PostgreSQL Quick Reference Guide

Een hulp en handvat of cheatsheet voor configuratie van PostgreSQL.

Connection Arguments

• -h : hostname or IP address or socket directory
• -p : port number
• -U : role name
• -d : database name
• connection string : host= port= user= dbname=
• service file : ~/.pg_service.conf
• gobal service file : pg_service.conf in directory :
  • redhat : /etc/sysconfig/pgsql/
  • Debian : /etc/postgresql-common

Arguments

• -c '' : run SQL or internal command
• -f : run a file
• -1 : run orders in a transaction
• -s : run orders step by step

Internal commands

• c [ dbname [ username ]  [ host ] [ port ] | conninfo ] : open a new connection
• conninfo : display current connection
• l : show databases
• dn : show namespaces (schemas)
• dt : show visible tables
• dx : show installed extensions
• df : show functions
• d

Creation with initdb

• -D : data directory ($PGDATA)
• -k : enable checksum in data page
• -X : WAL directory
• -A : authentication mode

Default paths

• Redhat : /var/lib/pgsql/<major version number>/data
• Debian : /var/lib/postgresql/<major version number>/main

contents

• base : database's directories and data files
• pg_wal : WAL directory
• pg_tblspc : links to tablespaces

Configuration

• pg_hba.conf : authentication file
• postgresql.conf : main configuration file
• postgresql.auto.conf : used by ALTER SYSTEM

default paths

• Redhat : var/lib/pgsql/<major version number>/data
• Debian : /etc/postgresql/<major version number>/main

main parameters

• shared_buffers : main memory, shared between backends
• work_mem : memory used by sort or hash
• maintenance_work_mem : memory used by VACUUM
• autovacuum_vacuum_scale_factor : table's ratio to trigger VACUUM
• min_wal_size / max_wal_size : trigger a CHECKPOINT

Commands

• SET work_mem=100MB ;
• ALTER SYSTEM SET work_mem = 100MB ;
• SET search_path to public, , ;
• ALTER DATABASE set search_path to CURRENT ;
• ALTER TABLE SET ( autovacuum_vacuum_scale_factor = 0.1) ;

Activity Log

• Redhat : /var/lib/pgsql/<major version number>data/pg_log
• Debian : /var/log/postgresql

Catalog

• pg_settings : configuration parameters
• pg_stat_activity :live sessions
• pg_locks : live locks
• pg_stat_user_tables : tables and statistics

Queries

• live queries :

select datname, pid, query_start, wait_event_type, wait_event, state, query

from pg_stat_activity where backend_type='client_backend' and pid <> pg_backend_pid () ;

• live locks :

select datname, a.pid, backend_start, wait_event, state, 1.relation : : regclass, 1.mode, 1.granted

from pg_locks 1 join pg_stat_activity a on 1.pid=a.pid ;

SQL functions

• pg_reload_conf () : reload configuration files
• pg_cancel_backend(pid) : cancel a session
• pg_terminate_backend(pid) : terminate a session
• pg_create_restore_point(text) : create a restore in WAL for PITR
• pg_is_in_recovery() : is cluster a standby?
• pg_xlog_replay_pause() : pause the streaming replication
• pg_xlog_replay_resume() : resume the streaming replicaton
• pg_create_physical_replication_slot(name) : create a replication slot
• pg_drop_replication_slot(name) : drop a replication slot 

Backups

pg_dump : logical backup

• -F p|c|t|d :
  • plain : plain SQL text
  • custom : binary compressed file
  • tar : TAR file
  • directory : compressed directory
• -j N : jobs number
• -f : backup file
• -c : add object's DROP commands
• -C : add database creation
• -n, -N : include or exclude namespace
• -t, -T : include or exclude table
• -s : backup schema, not data
• -a : backup data, not schema

pg_dump -Fc -j4 -f dbname.20171025.dmp
pg_dumpall -g -f globals.sql

pg_restore : logical backup restore

• -F c|t : backup format
• -l : catalog extraction
• -L : : use external catalog
• -j N : restore jobs number

pg_restore -l -f dbname.20171025.dmp  > catalog.txt
pg_restore -L catalog.txt -j4  -f dbname.20171025.dmp

pg_basebackup : physical backup

• -F t|p : backup format
• -r : maximum bandwidth
• -X fetch|stream|none : WAL fetch method
• -c fast|spread : CHECKPOINT's type
• -l text : backup label
• -R : make recovery.conf
• -P : progress bar

pg_basebackup -Fp -r 100 -c fast -l '2017.pgconf.eu' -R -P

Instance control from system

• with SystemD

systemctl restart|reload|start|stop|status postgresql-<major version number>

• on Debian

pg_ctlcluster <major version number> main restart|reload|start|stop|status

Debian scripts

• pg_lscluster : show clusters
• pg_createcluster : create a new cluster
• pg_dropcluster : drop a cluster