Skip to content

Instantly share code, notes, and snippets.

Matheus de Oliveira matheusoliveira

  • iFood
  • Brasil
Block or report user

Report or block matheusoliveira

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@matheusoliveira
matheusoliveira / README.md
Last active Mar 19, 2019
Get AWS RDS Enhanced Monitoring statistics from CloudWatch and show something similar to Linux top command
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) (
@matheusoliveira
matheusoliveira / temp_files.sql
Last active Nov 27, 2018
Query to get current temp files in PostgreSQL (needs SUPERUSER)
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):

@matheusoliveira
matheusoliveira / vacuum_freeze_demand.sh
Last active May 21, 2017
Script to perform VACUUM (to prevent wrap around) on a scheduled window
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} ))
@matheusoliveira
matheusoliveira / random.sql
Created Feb 21, 2015
A very optimized way of getting random rows of a table on PostgreSQL (gets by "id" in a fast and non-biased way, if with gaps)
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
)
@matheusoliveira
matheusoliveira / check_fk_reference.sql
Created Nov 7, 2014
Check if a given table has any reference to it
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,
@matheusoliveira
matheusoliveira / gist:05b5edc579124116cde0
Created Oct 22, 2014
Trigger para atualização de número em tabela filha
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
@matheusoliveira
matheusoliveira / parallel_vacuum.sh
Created Aug 19, 2014
Calls xargs with vacuumdb to do parallel (by table or by database) VACUUM
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
You can’t perform that action at this time.