Instantly share code, notes, and snippets.

View README.md

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:

View wm_concat.sql
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) (
View temp_files.sql
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
View config-pg-standby.md
  1. No postgresql.conf do primário:

    wal_level = 'hot_standby' max_wal_senders = 5

  2. Adicionar no pg_hba.conf do primário e reiniciar:

    local replication postgres trust

  3. Para secundário e limpar tudo (cuidado com o rm):

View vacuum_freeze_demand.sh
#!/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} ))
View random.sql
/* One random row from table "tbl" */
WITH RECURSIVE r AS (
SELECT NULL::int AS id, min(id) AS min_id, max(id) AS max_id, 0 AS cnt
FROM tbl
UNION ALL
SELECT tbl.id, r.min_id, r.max_id, r.cnt + 1
FROM r LEFT JOIN tbl
ON tbl.id = (SELECT floor(random() * (r.max_id - r.min_id + 1))::int)
WHERE r.id IS NULL
)
View check_fk_reference.sql
DO $$
DECLARE
r RECORD;
ret BOOLEAN;
cmd TEXT;
check_table regclass = '<table name here>'::regclass;
BEGIN
FOR r IN
SELECT
c.conname,
View gist:05b5edc579124116cde0
CREATE TABLE venda(venda_id serial primary key, ...);
CREATE TABLE venda_item(venda_id integer references venda(venda_id), num_item integer, ...);
CREATE OR REPLACE FUNCTION tg_item_venda_sequencia()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
-- Bloqueia acesso concorrente!
View pg-show-tps.sh
while true; do ( psql -AXtqc "SELECT 'atstart='||extract(epoch from now())||E'\n'||'before='||sum(xact_commit + xact_rollback) FROM pg_stat_database;"; sleep 1; psql -AXtqc "SELECT 'atend='||extract(epoch from now())||E'\n'||'after='||sum(xact_commit + xact_rollback) FROM pg_stat_database;"; echo -e 'after-before\natend-atstart\nafter\nbefore\n(after-before) / (atend-atstart)' ) | bc -l; echo; done
View parallel_vacuum.sh
# 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