cheatsheet.postgresql.md

PostgreSQL Cheat Sheet

• Common queries
• Init DB
• Replication
• Pgbench

Common queries

Tables that qualify for autovacuum:

  SELECT psut.relname,
       to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
       to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
       to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
       to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
       to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
           + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
              * pg_class.reltuples), '9G999G999G999') AS av_threshold,
       CASE
           WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
               + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                  * pg_class.reltuples) < psut.n_dead_tup
           THEN '*'
           ELSE ''
       END AS expect_av
   FROM pg_stat_user_tables psut
       JOIN pg_class on psut.relid = pg_class.oid
   ORDER BY 1;

Init DB

su - postgres
rm -rf /var/lib/postgresql/10/main/*
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main/
/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main/ -l logfile start

Replication

The primary server operates in continuous archiving mode,
while each standby server operates in continuous recovery mode, reading the WAL files from the primary.

• On both servers:

  # In postgresql.conf:
  wal_level = replica
  hot_standby = on

• On the master:

  # create repl user
  CREATE ROLE repl LOGIN REPLICATION ENCRYPTED PASSWORD 'verysecret';  
  
  # create a replication slot
  SELECT * FROM pg_create_physical_replication_slot('standby_replication_slot');
  
  # Add to pg_hba.conf:
  host    replication     repl            SLAVEIP/32        md5

• On the slave:

  # In /var/lib/postgresql/10/main/recovery.conf
  primary_conninfo = 'host=MASTERIP port=5432 user=repl password=verysecret'
  primary_slot_name = 'standby_replication_slot'
  standby_mode = on
  trigger_file = '/etc/postgresql/10/main/failover.trigger'  
  
  # Init replica
  systemctl stop postgresql
  rm -rf /var/lib/postgresql/10/main/*  
  su - postgres -c "pg_basebackup --wal-method=stream -D /var/lib/postgresql/10/main/ -U repl -h MASTERIP"

• Check replication status

  • On the master:

    Check the status of the replication:

    select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication;

  • On the slave:

    pg_is_in_recovery will return "t" in the replica, and "f" in the master.

    select pg_is_in_recovery();

    The following query will return 1 in the slave, 0 in the master:

    SELECT count(*) from pg_settings where name = 'hot_standby' AND setting = 'on';

Pgbench

https://wiki.postgresql.org/wiki/Pgbenchtesting

Basic insert test:

pgbench -i -s 70 bench2