-
No postgresql.conf do primário:
wal_level = 'hot_standby' max_wal_senders = 5
-
Adicionar no
pg_hba.conf
do primário e reiniciar:local replication postgres trust
-
Para secundário e limpar tudo (cuidado com o
rm
):
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION wm_concat_finalfn(anyarray) | |
RETURNS text | |
LANGUAGE SQL | |
IMMUTABLE AS $$ | |
SELECT array_to_string($1, ','); | |
$$; | |
DROP AGGREGATE IF EXISTS wm_concat(anyelement); | |
CREATE AGGREGATE wm_concat(anyelement) ( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/bash | |
set -o pipefail | |
PROCESS_TIMEOUT_SEC=7200 | |
VACUUM_FREEZE_TABLE_AGE=150000000 # 150M | |
VACUUM_FREEZE_MIN_AGE=50000000 # 50M | |
START_TIME_EPOCH=$( date +%s ) | |
END_TIMEOUT_EPOCH=$(( ${START_TIME_EPOCH} + ${PROCESS_TIMEOUT_SEC} )) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# Per database | |
psql -A0Xtc "SELECT datname FROM pg_database WHERE datname <> 'template0'" | xargs -P 12 -0 -I ? bash -c 'echo "`date`: started ?"; echo -e "SET vacuum_freeze_table_age TO 0;\\nVACUUM;" | psql -qX "?"; echo "`date`: finish ?"' | |
# Per table | |
psql -A0Xtc "SELECT oid::regclass::text FROM pg_class WHERE relkind IN ('r','m')" | xargs -P 12 -0 -I {} bash -c 'echo "`date`: started {}"; vacuumdb --analyze --table={} && echo "`date`: {}: OK" || echo "`date`: {}: FAILED!"' | |
# Vaccum (kind-of) freeze per table - prevents bug fixed on 9.3.2 (and some 9.1.x, 9.2.x) | |
psql -A0Xtc "SELECT replace(replace(oid::regclass::text, '\\', '\\\\'), '\"', '\\\"') FROM pg_class WHERE relkind IN ('r','m')" | xargs -P 12 -0 -I {} bash -o pipefail -c 'echo "`date`: started {}"; echo -e "SET vacuum_freeze_table_age TO 0;\\nVACUUM {};" | psql -qX && echo "`date`: {}: OK" || echo "`date`: {}: FAILED!"' | tee -a /tmp/vacuum.log |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH temp_dirs AS ( | |
SELECT s1.dir || s1.version || '/pgsql_tmp' AS dir_name | |
FROM | |
( | |
SELECT | |
'pg_tblspc/' || spc.oid::text || '/' AS dir, | |
l.version | |
FROM | |
( | |
SELECT spc1.oid |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Simple example to read CSV format from nomes.csv at https://brasil.io/dataset/genero-nomes/files/ | |
package main | |
import ( | |
"encoding/csv" | |
"fmt" | |
"os" | |
"strconv" | |
"strings" | |
) |
This is a very simple script, so don't expect something great.
You can change it, distribute, do whatever you like, I don't mind. But if you improve it, you could share it back ;)
To use, you have to export variables to use aws-cli, like AWS_PROFILE
, AWS_DEFAULT_REGION
, etc. Then just call the script using watch
(or however you like) and giving the RDS instance name:
watch ./rds-top.sh <your instance name>
Requirements:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Este arquivo contém o histórico de TODOS comandos que executei durante o treinamento. | |
* Pode conter alguns erros e falhas, mas fica aqui caso eu tenha esquecido de deixar algum exemplo | |
* mais claro nos demais arquivos. | |
*/ | |
select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb; | |
\e | |
select '{"a":1, "b":2}'::jsonb; | |
select '{"a":1, "b":2, "b": 3}'::json; | |
select '{"a":1, "b":2, "b": 3}'::json -> 'b'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION public.json_append(data json, insert_data json) | |
RETURNS json | |
IMMUTABLE | |
LANGUAGE sql | |
AS $$ | |
SELECT ('{'||string_agg(to_json(key)||':'||value, ',')||'}')::json | |
FROM ( | |
SELECT * FROM json_each(data) | |
UNION ALL | |
SELECT * FROM json_each(insert_data) |
OlderNewer