matheusoliveira

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)

matheusoliveira

/**
 * 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';

matheusoliveira

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:

matheusoliveira

// 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"
)

matheusoliveira

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

matheusoliveira

# 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

matheusoliveira

#!/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

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

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

/*-------------------------------------------------------------------------
 *
 * partition_insert_trigger.c
 *    A function to redirect insertions from parent table to child
 *	  partitions.
 *	  
 *	  IMPORTANT: This is just an experimental code, and is based on
 *	             PostgreSQL  version 9.2.1, and will probably not work
 *	             on different versions (although not tested)
 *