Snap PostgreSQL Reference - Commands

Snap PostgreSQL Reference - Commands

The snap PostgreSQL ships the list of useful tools which be grouped as:

Clients to connect PostgreSQL

Examples:

Example for 'postgresql.psql'
> postgresql.psql -U postgres -h /tmp
psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# \du+
                                    List of roles
 Role name |                         Attributes                         | Description 
-----------+------------------------------------------------------------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | 

Tools to manage PostgreSQL cluster

Those tools are Ubuntu/Debian-specific wrappers around ‘initdb’ and ‘pg_ctl’ precisely for the purpose of providing a simpler interface for managing multiple postgresql instances on the same host:

Examples:

Example for 'postgresql.lsclusters'
> postgresql.lsclusters
Ver Cluster Port Status Owner    Data directory                 Log file
16  main    5432 online _daemon_ /var/lib/postgresql/16/main2   /var/log/postgresql/postgresql-16-main2.log
16  test    5434 online _daemon_ /var/lib/postgresql/16/test    /var/log/postgresql/postgresql-16-test.log
16  test1   5433 online _daemon_ /var/lib/postgresql/16/test1   /var/log/postgresql/postgresql-16-test1.log
16  test123 5435 online _daemon_ /var/lib/postgresql/16/test123 /var/log/postgresql/postgresql-16-test123.log
Example for 'postgresql.ctlcluster'
> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 main status
pg_ctl: server is running (PID: 41805)
...

> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 main stop

> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 main status
pg_ctl: no server running

> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 main start

> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 main status
pg_ctl: server is running (PID: 42101)
...

Usage:

Usage: postgresql.ctlcluster <version> <cluster> <action> [-- <pg_ctl options>]
Example for 'postgresql.createcluster'
> sudo postgresql.createcluster 16 test1
Creating new PostgreSQL cluster 16/test1
...
The cluster can be started with: 'postgresql.ctlcluster --skip-systemctl-redirect 16 test1 start'

> postgresql.lsclusters 16 test1
Ver Cluster Port Status Owner    Data directory               Log file
16  test1   5435 down   _daemon_ /var/lib/postgresql/16/test1 /var/log/postgresql/postgresql-16-test1.log

> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 test1 start

> postgresql.lsclusters 16 test1
Ver Cluster Port Status Owner    Data directory               Log file
16  test1   5435 online _daemon_ /var/lib/postgresql/16/test1 /var/log/postgresql/postgresql-16-test1.log

> postgresql.psql -U postgres -h /tmp -p 5435 -d postgres
psql (16.8 (Ubuntu 16.8-0ubuntu0.24.04.1))
Type "help" for help.

postgres=# 
Example for 'postgresql.renamecluster'
> postgresql.lsclusters 16
Ver Cluster Port Status Owner    Data directory               Log file
16  main    5432 online _daemon_ /var/lib/postgresql/16/main  /var/log/postgresql/postgresql-16-main.log
16  test1   5433 online _daemon_ /var/lib/postgresql/16/test1 /var/log/postgresql/postgresql-16-test1.log

> sudo postgresql.renamecluster 16 test1 test123
Stopping cluster 16 test1 ...
Warning: systemd does not know about the new cluster yet. Operations like "service postgresql start" will not handle it. To fix, run:
  sudo systemctl daemon-reload
Starting cluster 16 test123 ...
Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  sudo systemctl start postgresql@16-test123

> postgresql.lsclusters 16
Ver Cluster Port Status Owner    Data directory                 Log file
16  main    5432 online _daemon_ /var/lib/postgresql/16/main    /var/log/postgresql/postgresql-16-main.log
16  test123 5433 online _daemon_ /var/lib/postgresql/16/test123 /var/log/postgresql/postgresql-16-test123.log
Example for 'postgresql.dropcluster'
> postgresql.lsclusters 16 test123
Ver Cluster   Port Status Owner    Data directory               Log file
16  test123   5433 online _daemon_ /var/lib/postgresql/16/test1 /var/log/postgresql/postgresql-16-test1.log

> sudo postgresql.dropcluster --stop 16 test123

> postgresql.lsclusters 16 test123
Error: Cluster 16 test123 does not exist

Tools to manage PostgreSQL instance

Examples:

Example for 'postgresql.createuser'
> postgresql.createuser -U postgres -h /tmp mynewuser

> postgresql.psql -U postgres -h /tmp -p 5432 -d postgres -c "\du"
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 mynewuser | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
Example for 'postgresql.createdb'
> postgresql.createdb -U postgres -h /tmp -p 5432 mybench

> postgresql.psql -U postgres -h /tmp -p 5432 -d postgres -c "\l"
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 mybench   | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | 
...
Example for 'postgresql.ctl'
> 

Tools to healthcheck PostgreSQL

Examples:

Example for 'postgresql.isready'
> postgresql.isready
/tmp:5432 - accepting connections

> postgresql.isready -h /tmp -p 5435
/tmp:5435 - accepting connections

> postgresql.isready -h /tmp -p 5442
/tmp:5442 - no response
Example for 'postgresql.pgbench'
> postgresql.createdb -U postgres -h /tmp -p 5432 mybench

> postgresql.pgbench -U postgres -h /tmp -p 5432 -d mybench --initialize
...
done in 4.98 s (drop tables 0.00 s, create tables 0.04 s, client-side generate 4.18 s, vacuum 0.23 s, primary keys 0.54 s).

> postgresql.pgbench -U postgres -h /tmp -p 5432 -d mybench -T 60
...
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 6634
number of failed transactions: 0 (0.000%)
latency average = 9.043 ms
initial connection time = 14.192 ms
tps = 110.587085 (without initial connection time)

Tools to configure PostgreSQL

Examples:

Example for 'postgresql.config'
> postgresql.config
BINDIR = /usr/lib/postgresql/16/bin
DOCDIR = /usr/share/doc/postgresql-doc-16
HTMLDIR = /usr/share/doc/postgresql-doc-16
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/16/server
LIBDIR = /usr/lib/riscv64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/16/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/16/man
SHAREDIR = /usr/share/postgresql/16
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/16/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--build=riscv64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/riscv64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/16/man' '--docdir=/usr/share/doc/postgresql-doc-16' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/16' '--bindir=/usr/lib/postgresql/16/bin' '--libdir=/usr/lib/riscv64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu 16.8-0ubuntu0.24.04.1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fno-omit-frame-pointer -fstack-protector-strong -Wformat -Werror=format-security -fno-stack-clash-protection' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-lz4' '--with-zstd' '--with-systemd' '--with-selinux' 'build_alias=riscv64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=3' 'CXXFLAGS=-g -O2 -fno-omit-frame-pointer -fstack-protector-strong -Wformat -Werror=format-security -fno-stack-clash-protection'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=3 -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fno-omit-frame-pointer -fstack-protector-strong -Wformat -Werror=format-security -fno-stack-clash-protection
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lselinux -lzstd -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lm 
VERSION = PostgreSQL 16.8 (Ubuntu 16.8-0ubuntu0.24.04.1)
Example for 'postgresql.conftool'
> postgresql.conftool 16 main show max_connections
max_connections = 100

> postgresql.conftool 16 test1 show all
data_directory = '/var/lib/postgresql/16/test1'
datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
dynamic_shared_memory_type = posix
external_pid_file = '/tmp/16-test1.pid'
hba_file = '/etc/postgresql/16/test1/pg_hba.conf'
ident_file = '/etc/postgresql/16/test1/pg_ident.conf'
lc_messages = 'C.UTF-8'
lc_monetary = 'C.UTF-8'
lc_numeric = 'C.UTF-8'
lc_time = 'C.UTF-8'
log_timezone = UTC
max_connections = 100
max_wal_size = 1GB
min_wal_size = 80MB
port = 5435
shared_buffers = 128MB
timezone = UTC
unix_socket_directories = '/tmp'

> postgresql.conftool 16 main edit
...

Tools to backup/restore PostgreSQL

Examples:

Example for 'postgresql.dump'
Dump:
> postgresql.dump -c -C -U postgres -h /tmp -d mydb > mydb.sql
Restore:
> psql -U postgres -h /tmp < mydb.sql
Example for 'postgresql.dumpall'
> postgresql.dumpall -c -U postgres -h /tmp > all.sql
Example for 'postgresql.restore'
> postgresql.restore -c -U postgres -h /tmp -v -f mydb.sql
Example for 'postgresql.archivecleanup'
Command-line interface:
> postgresql.archivecleanup /mnt/server/archiverdir 000000010000000000000010.00000020.backup
Inside postgresql.conf:
> archive_cleanup_command = 'pg_archivecleanup /mnt/server/archiverdir %r'
Example for 'postgresql.basebackup'
Create slot:
> mkdir -p /tmp/test1 && postgresql.basebackup -D /tmp/test1 -C --slot myslot -P -U postgres -h /tmp

Reuse slot:
> mkdir -p /tmp/test2 && postgresql.basebackup -D /tmp/test2 --slot myslot -P -U postgres -h /tmp
31295/31295 kB (100%), 1/1 tablespace
Example for 'postgresql.receivewal'
> mkdir -p ~/wal_archive
> sudo postgresql.receivewal -D ~/wal_archive -U postgres -h /tmp -p 5432
...

> ls -la ~/wal_archive/
...
-rw-------  1 root   root   16777216 Apr 10 12:16 000000010000000000000001.partial
-rw-------  1 root   root   16777216 Apr 10 23:59 000000010000000000000002.partial
-rw-------  1 root   root   16777216 Apr 11 22:23 000000010000000000000003.partial
...
Example for 'postgresql.recvlogical'
> postgresql.conftool 16 main edit # set wal_level=logical !

> sudo postgresql.ctlcluster --skip-systemctl-redirect 16 main restart

> postgresql.conftool 16 main show wal_level
wal_level = logical

> postgresql.recvlogical -U postgres -d mybench --create-slot -S myslot1

> postgresql.recvlogical -U postgres -d mybench --start -S myslot1 -f logical_repl.sql &
[1] 44782

>  postgresql.pgbench -U postgres -h /tmp -p 5432 -d mybench
...

> head -5 logical_repl.sql 
BEGIN 7406
table public.pgbench_history: TRUNCATE: (no-flags)
COMMIT 7406
BEGIN 7407
table public.pgbench_accounts: UPDATE: aid[integer]:79774 bid[integer]:1 abalance[integer]:2767 filler[character]:'     

> kill 44782
pg_recvlogical: error: unexpected termination of replication stream: