Direct technisch advies?
Direct een ervaren DBA aan de lijn voor database hulp, support en ondersteuning. We kunnen eventueel direct met u meekijken!035 369 0304
dba@optimadata.nl
Home > PostgreSQL Quick Reference Guide

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

Client psql

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 : describe object
  • ! : run a shell command
  • i : run a file
  • x on|auto|off : toggle expanded display mode
  • timing on|off : toggle display running time
  • ? : help about internal command
  • h : help about SQL orders
  • q : quit

Directory and files

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/10/data
  • Debian : /var/lib/postgresql/10/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 : mail configuration file
  • postgresql.auto.conf : used by ALTER SYSTEM

default paths

  • Redhat : var/lib/pgsql/10/data
  • Debian : /etc/postgresql/10/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/10data/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-10

    • on Debian

    pg_ctlcluster 10 main restart|reload|start|stop|status

    Debian scripts

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

    Op deze pagina vind je de meest gebruikte en handige commandline's voor het configureren en beheren van PostgreSQL databases. Uiteraard is deze lijst niet uitputtend en continue in ontwikkeling. Heb je suggesties, mis je iets of wil je graag meer weten? Neem dan gerust vrijblijvend contact met ons op.

    Contact over PostgreSQL

    Neem contact met ons op

    Vul hier het telefoonnummer in en wij zullen zo snel mogelijk terugbellen. Of neem zelf contact op.

    Zelf contact opnemen

    Op de hoogte blijven?

    Schrijf je in voor het laatste (open source) database nieuws, events en blogs.
    LAAT HIER UW VRAAG ACHTERSTEL UW VRAAG
    Consultant

    Sorry

    Op dit moment is er niemand online. Laat uw gegevens hier achter, dan nemen wij contact op.

    Bedankt

    Bedankt voor de informatie, we nemen snel contact met u op.