PostgreSQL comes with a rich collection of built-in command-line tools that make database administration, backup, debugging, and monitoring extremely efficient. If you’ve installed PostgreSQL from source or from binary packages, you’ve probably seen several executable files inside the bin/ directory.
These commands may look confusing at first, but each one has a special purpose—some are used for backup, some for verification, some for debugging the WAL, and others for monitoring the health of your cluster.
In this blog, we will explore 10 important PostgreSQL binaries, understand what they do, and learn how to use each one with simple, beginner-friendly examples.
1. pg_basebackup
Purpose:
Creates a physical backup of the entire PostgreSQL cluster.Used for streaming replication and disaster recovery.
Why it is important:
- Takes full base backup
- Supports streaming WAL
- Very simple to use
- Perfect for replica/standby creation
Example:
pg_basebackup -h localhost -U postgres -D /tmp/pg18_backup -Fp -Xs -P
You get result like this
postgres@cybrosys:/home/cybrosys$ pg_basebackup -h localhost -U postgres -D /tmp/pg18_demo -Fp -Xs -P
Password:
3429569/3429569 kB (100%), 1/1 tablespace
You can verify the backup is exist in the tmp directory
ls /tmp/pg18_demo
You can see the backup files here
cybrosys@cybrosys:~$ ls /tmp/pg18_demo/
backup_label base pg_bulkload pg_dynshmem pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc PG_VERSION pg_xact
backup_manifest global pg_commit_ts pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_wal postgresql.auto.conf
Meaning:
- -h localhost - connect to local server
- -U postgres - user
- -D /tmp/pg18_demo... - backup directory
- -Fp - plain format
- -Xs - include WAL
- -P - show progress
You can also use the below command to explore more flags about this cli named pg_basebackup
pg_basebackup --help
2. pg_amcheck
Purpose:
Checks the internal structure of tables and indexes.Useful for detecting corruption early.
Why it is important:
- Helps identify corrupted pages
- Useful during debugging or after crash recovery
- Can check entire databases or specific tables
Example:
Before using this cli, you need to install the extension named am_check in the required database
create extension amcheck;
You can check that the extension is installed properly
select * from pg_available_extensions where name = 'amcheck';
Check an entire database:
pg_amcheck -d mydb -v
You can see the result like this
pg_amcheck: including database "postgres"
pg_amcheck: in database "postgres": using amcheck version "1.5" in schema "public"
pg_amcheck: checking heap table "postgres.public.big_table"
pg_amcheck: checking heap table "postgres.pg_catalog.pg_attribute"
pg_amcheck: checking heap table "postgres.pg_catalog.pg_shdepend"
Check a specific table:
pg_amcheck -d mydb public.customers
Explore more about this pg_amcheck command line utility
pg_amcheck - -help
3. pg_waldump
Purpose:
Reads and displays WAL (Write-Ahead Log) records.
What is wal in postgresql?
WAL = Write-Ahead Log
It is a log file where PostgreSQL writes every change before writing it to the actual data files.
Think of WAL as a safety notebook where PostgreSQL writes what it’s going to do — so it can recover later if something goes wrong.Great for learning internals or debugging replication, recovery, and crash events.
Example:
Dump the latest WAL file:
pg_waldump /var/lib/postgresql/18/main/pg_wal/00000001000000000000000A -10
You get result like this
rmgr: Heap len (rec/tot): 59/ 8227, tx: 1006708, lsn: B11/03000A30, prev B11/02FFEBA8, desc: LOCK xmax: 1006708, off: 10, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/5335543/5467759 blk 544 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 1006708, lsn: B11/03002A70, prev B11/03000A30, desc: LOCK xmax: 1006708, off: 11, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/5335543/5467759 blk 544
rmgr: Heap len (rec/tot): 59/ 7215, tx: 1006708, lsn: B11/03002AA8, prev B11/03002A70, desc: LOCK xmax: 1006708, off: 51, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/5335543/5467759 blk 493 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 1006708, lsn: B11/030046F0, prev B11/03002AA8, desc: LOCK xmax: 1006708, off: 27, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/5335543/5467759 blk 452
rmgr: Heap len (rec/tot): 59/ 8111, tx: 1006708, lsn: B11/03004728, prev B11/030046F0, desc: LOCK xmax: 1006708, off: 10, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/5335543/5467759 blk 383 FPW
rmgr: Heap len (rec/tot): 54/ 54, tx: 1006708, lsn: B11/0300A680, prev B11/03008630, desc: LOCK xmax: 1006708, off: 45, infobits: [LOCK_ONLY, EXCL_LOCK], flags: 0x00, blkref #0: rel 1663/5335543/5467759 blk 539
Show only specific record types:
pg_waldump --relation=16384
You can also explore more about this cli by using this help flag
pg_waldump --help
4. pg_controldata
Purpose:
Shows internal metadata stored in the cluster’s pg_control file.
You can view:
- Cluster state
- Checkpoint locations
- Database system identifier
- WAL timeline
- PostgreSQL version info
Example:
pg_controldata /var/lib/postgresql/18/main
You will get details like:
postgres@cybrosys:/home/cybrosys$ pg_controldata /var/lib/postgresql/18/main
pg_control version number: 1800
Catalog version number: 202506291
Database system identifier: 7556910269173853312
Database cluster state: in production
pg_control last modified: Sunday 23 November 2025 07:46:47 PM
Latest checkpoint location: B11/3652C30
Latest checkpoint's REDO location: B11/3652BA0
Latest checkpoint's REDO WAL file: 0000000100000B1100000003
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:1006808
Latest checkpoint's NextOID: 5497853
Latest checkpoint's NextMultiXactId: 4299
Latest checkpoint's NextMultiOffset: 8967
Latest checkpoint's oldestXID: 744
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 1006808
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Sunday 23 November 2025 07:46:43 PM
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: off
max_connections setting: 100
max_worker_processes setting: 16
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 256
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Default char data signedness: signed
Mock authentication nonce: 71129dc58765181be3a7ee5b6bc07a6ef966860bc22412ce3bf308fe1620ff38
You can also explore more about this cli named pg_controldata
Pg_controldata --help
5. pg_archivecleanup
Purpose:
Removes old archived WAL files that are no longer needed.
Why it is useful:
- Prevents storage from filling up
- Used with archive_command
- Helps in PITR (Point-in-Time Recovery)
Example:
postgres@cybrosys:/home/cybrosys$ ls /var/lib/postgresql/18/main/pg_wal
0000000100000B1100000003 0000000100000B1100000007 0000000100000B110000000B 0000000100000B110000000F 0000000100000B1100000013 0000000100000B1100000017 0000000100000B110000001B summaries
0000000100000B1100000004 0000000100000B1100000008 0000000100000B110000000C 0000000100000B1100000010 0000000100000B1100000014 0000000100000B1100000018 0000000100000B110000001C
0000000100000B1100000005 0000000100000B1100000009 0000000100000B110000000D 0000000100000B1100000011 0000000100000B1100000015 0000000100000B1100000019 0000000100000B110000001D
0000000100000B1100000006 0000000100000B110000000A 0000000100000B110000000E 0000000100000B1100000012 0000000100000B1100000016 0000000100000B110000001A archive_status
postgres@cybrosys:/home/cybrosys$ /usr/lib/postgresql/18/bin/pg_archivecleanup /var/lib/postgresql/18/main/pg_wal 0000000100000B110000000A
postgres@cybrosys:/home/cybrosys$ ls /var/lib/postgresql/18/main/pg_wal
0000000100000B110000000A 0000000100000B110000000D 0000000100000B1100000010 0000000100000B1100000013 0000000100000B1100000016 0000000100000B1100000019 0000000100000B110000001C summaries
0000000100000B110000000B 0000000100000B110000000E 0000000100000B1100000011 0000000100000B1100000014 0000000100000B1100000017 0000000100000B110000001A 0000000100000B110000001D
0000000100000B110000000C 0000000100000B110000000F 0000000100000B1100000012 0000000100000B1100000015 0000000100000B1100000018 0000000100000B110000001B archive_status
postgres@cybrosys:/home/cybrosys$
You can see the older files are removed
This removes all WAL files older than the specified one.
Explore more about this pg_archivecleanup cli
Pg_archivecleanup --help
You get result like this
pg_archivecleanup removes older WAL files from PostgreSQL archives.
Usage:
pg_archivecleanup [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE
Options:
-b, --clean-backup-history clean up files including backup history files
-d, --debug generate debug output (verbose mode)
-n, --dry-run dry run, show the names of the files that would be
removed
-V, --version output version information, then exit
-x, --strip-extension=EXT strip this extension before identifying files for
clean up
-?, --help show this help, then exit
For use as "archive_cleanup_command" in postgresql.conf:
archive_cleanup_command = 'pg_archivecleanup [OPTION]... ARCHIVELOCATION %r'
e.g.
archive_cleanup_command = 'pg_archivecleanup /mnt/server/archiverdir %r'
Or for use as a standalone archive cleaner:
e.g.
pg_archivecleanup /mnt/server/archiverdir 000000010000000000000010.00000020.backup
Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
6. pg_config
Purpose:
Displays details about your PostgreSQL installation.
Useful for:
- Compiling extensions
- Checking include, library, and binary paths
- Debugging source installations
Example:
postgres@cybrosys:/home/cybrosys$ pg_config
Example output:
BINDIR = /usr/lib/postgresql/18/bin
DOCDIR = /usr/share/doc/postgresql-doc-18
HTMLDIR = /usr/share/doc/postgresql-doc-18
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/18/server
LIBDIR = /usr/lib/x86_64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/18/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/18/man
SHAREDIR = /usr/share/postgresql/18
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/18/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--build=x86_64-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/x86_64-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/18/man' '--docdir=/usr/share/doc/postgresql-doc-18'
CC = gcc
CPPFLAGS = -Wdate-time -D_FORTIFY_SOURCE=2 -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 -flto=auto -ffat-lto-objects -flto=auto -ffat-lto-objects -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects -flto=auto -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 -lnuma
VERSION = PostgreSQL 18.1 (Ubuntu 18.1-1.pgdg22.04+2)
Get only include directory:
pg_config --includedir
7. pg_isready
Purpose:
Checks whether PostgreSQL server is accepting connections.
Useful for:
- Monitoring
- Automation
- Load balancer health checks
- Debugging connection issues
Example:
postgres@cybrosys:pg_isready
Output:
/var/run/postgresql:5432 - accepting connections
If down:
/var/run/postgresql:5432 - no response
Explore more about this cli named pg_isready
pg_isready -help
8. pg_verifybackup
Purpose:
Verifies that a physical backup is valid.
Why it matters:
- Ensures that base backups contain required files
- Confirms WAL integrity
- Helpful before starting recovery or replica setup
Example:
postgres@cybrosys:/home/cybrosys$ pg_verifybackup /tmp/pg18_backup
Output shows:
- Missing files
- Files checksum
- WAL missing or corrupted
- Result: Success or Failure
If it is successful, you get result like this
postgres@cybrosys:pg_verifybackup /tmp/pg18_backup
backup successfully verified
9. pg_walsummary
Purpose:
Summarizes WAL data (introduced in PostgreSQL 18).Used to inspect WAL contents in a structured format.
Example:
postgres@cybrosys:/home/cybrosys$ pg_walsummary \
/var/lib/postgresql/18/main/pg_wal/summaries/0000000100000B1125000BA000000B11460D31B0.summary
You will get result like this
TS 1663, DB 5, REL 5489681, FORK main: blocks 0..42555
TS 1663, DB 5, REL 5489681, FORK vm: block 0
PostgreSQL is summarizing WAL modifications for one table, and showing:
which tablespace
which database
which relation
which fork (data file type)
which blocks were modified
10. pg_test_timing
Purpose:
Measures the timer accuracy of your system.
Why it is useful:
- Helps detect OS/hypervisor timing issues
- Important for tuning autovacuum and background workers
- Useful in VM or cloud environments
Example:
postgres@cybrosys:pg_test_timing
You will get result like this
Testing timing overhead for 3 seconds.
Per loop time including overhead: 1209.82 ns
Histogram of timing durations:
< us % of total count
1 2.37875 58986
2 75.26621 1866383
4 22.21563 550883
8 0.01948 483
16 0.11901 2951
32 0.00081 20
64 0.00008 2
128 0.00004 1
Conclusion
PostgreSQL provides a powerful set of command-line tools that help you manage, monitor, verify, and debug your database systems. Whether you want to take backups, validate data, inspect WAL files, or verify system performance, these 10 PostgreSQL binaries give you everything you need.
By understanding and practicing these commands, you become more confident as a PostgreSQL developer or DBA. The more you use these tools, the deeper your understanding of PostgreSQL internals becomes.