Snap PostgreSQL Reference - Commands
The snap PostgreSQL ships the list of useful tools which be grouped as:
Clients to connect PostgreSQL
- postgresql.psql - PostgreSQL interactive terminal.
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:
- postgresql.lsclusters - show information about all PostgreSQL clusters
- postgresql.ctlcluster - start/stop/restart/reload a PostgreSQL cluster
- postgresql.createcluster - create a new PostgreSQL cluster
- postgresql.renamecluster - rename a PostgreSQL cluster
- postgresql.dropcluster - completely delete a PostgreSQL cluster
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
- postgresql.createuser - define a new PostgreSQL user account
- postgresql.createdb - create a new PostgreSQL database
- postgresql.ctl - initialize, start, stop, or control a PostgreSQL server
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
- postgresql.isready - check the connection status of a PostgreSQL server
- postgresql.pgbench - run a benchmark test on 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
- postgresql.config - retrieve information about the installed version of PostgreSQL
- postgresql.conftool - read and edit PostgreSQL cluster configuration files
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
- postgresql.dump - extract a PostgreSQL database into a script file or other archive file
- postgresql.dumpall - extract a PostgreSQL database cluster into a script file
- postgresql.restore - restore a PostgreSQL database from an archive file created by pg_dump
- postgresql.archivecleanup - clean up PostgreSQL WAL archive files
- postgresql.basebackup - take a base backup of a PostgreSQL cluster
- postgresql.receivewal - stream write-ahead logs from a PostgreSQL server
- postgresql.recvlogical - control PostgreSQL logical decoding streams
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: