sidymar

{"version":1,"resource":"file:///opt/projects/aegro/data-flow-aegro/airflow/dags/include/plugins/slack_plugin.py","entries":[{"id":"ytKG.py","timestamp":1650986196952},{"id":"51Bw.py","timestamp":1650986223179}]}

sidymar

pg_dump database -Fc backup.dump
pg_restore -l backup.dump | sed '/MATERIALIZED VIEW DATA/d' > restore.lst
pg_restore -L restore.lst -d database backup.dump

pg_restore -l backup.dump | grep 'MATERIALIZED VIEW DATA' > refresh.lst
pg_restore -L refresh.lst -d database backup.dump

sidymar

create temporary table temp (symbol varchar(255), open decimal, high decimal, low decimal, close decimal, volume varchar(255), date date );

create table if not exists stocks (id serial primary key, symbol varchar(255), open decimal, high decimal, low decimal, close decimal, volume varchar(255), date date, created_at timestamp, updated_at timestamp);

copy temp (symbol, date, open, high, low, close, volume) from '/path/to/file.csv' with delimiter ',' csv header;

delete from stocks using temp where stocks.date = temp.date and stocks.symbol = temp.symbol;

insert into stocks (symbol, open, high, low, close, volume, date) select symbol, open, high, low, close, volume, date from temp;

sidymar

/**
 *  Parte do Projeto LUX, em desenvolvimento desde 12 de novembro de 2016.
 *
 *  Contém implementação do "Cálculo da Páscoa" pelo método tabular para
 *  datas no calendário Gregoriano, descrito em "Computus":
 *
 *      https://en.wikipedia.org/wiki/Computus#Gregorian_calendar
 *
 *  com adequações para o SQLite 3.7.13 ou mais recente, privilegiando o
 *  uso de datas no padrão ISO-8601.

sidymar

Rabbitmq conf locations for homebrew

/usr/local/etc/rabbitmq/rabbitmq-env.conf
/usr/local/etc/rabbitmq/rabbitmq

sidymar

SELECT
c.relname,
count(*) AS buffers
FROM pg_class c
JOIN pg_buffercache b
ON b.relfilenode=c.relfilenode
JOIN pg_database d
ON (b.reldatabase=d.oid AND d.datname=current_database())
WHERE c.relname NOT LIKE 'pg%' GROUP BY c.relname
ORDER BY 2 DESC;

sidymar

pg_config --version | awk '{print $2}'

sidymar

SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
 (tblpages-est_tblpages)*bs AS extra_size,
 CASE WHEN tblpages - est_tblpages > 0
 THEN 100 * (tblpages - est_tblpages)/tblpages::float
 ELSE 0
 END AS extra_ratio, fillfactor, (tblpages-est_tblpages_ff)*bs AS bloat_size,
 CASE WHEN tblpages - est_tblpages_ff > 0
 THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
 ELSE 0
 END AS bloat_ratio, is_na

sidymar

#This Assumes that we are moving the whole /var/lib/pgsql directory to /data/pgsql, /data being a different physical drive, but it could be anywhere.

service postgresql stop

#Edit /etc/init.d/postgresql and change the following three lines from this:

PGDATA=/var/lib/pgsql/data
if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]
then
echo "Using old-style directory structure"

sidymar

Get replication lag:

 #!/bin/bash

 ### PURPOSE:
 #
 # When things go as expected:
 # ---------------------------
 # 1. Gets the replication lag and prints that time to STDOUT.  Time is in seconds.
 # 2. $?  will be 0