PG19 Hacktober: 31 Days of New Features: Command-Line Corner: psql Upgrades and vacuumdb, pg_upgrade, pg_waldump, and pgbench Improvements

PostgreSQL 19 introduces several improvements to database administration, backup verification, WAL analysis, benchmarking, and major-version upgrades. This blog consolidates eight practical topics into one guide, comparing PostgreSQL 18 with PostgreSQL 19 wherever the behavior differs.

Features covered

Tool	PG18	PG19
vacuumdb --dry-run	Not available	Preview commands without running them
vacuumdb --analyze-only	Skips partitioned parents	Includes partitioned tables
pg_verifybackup	Needs --no-parse-wal for tar backups	Parses WAL inside tar archives
pg_waldump	Directory only	Reads tar archives directly
pg_upgrade	Many per-object SQL commands for large objects	Faster metadata transfer
pgbench --continue-on-error	Not available	Continue after SQL errors

vacuumdb –dry-run

vacuumdb is a command-line utility used to run VACUUM and ANALYZE operations on a PostgreSQL database.In PostgreSQL 18, vacuumdb does not provide the –dry-run option. PostgreSQL 19 introduces this option, which allows you to preview the maintenance commands without executing them.
This is useful for database administrators who want to review a maintenance plan before running it on production systems.

Example:
Create a test database and table:

CREATE DATABASE maintenance_demo;
Connect to the database and create a table:
CREATE TABLE public.employee (id integer,name text);
INSERT INTO public.employee
SELECT i, 'Employee ' || i
FROM generate_series(1, 1000) AS i;

PostgreSQL 18
Try the following command:

/usr/lib/postgresql/18/bin/vacuumdb \
  --dry-run \
  --verbose \
  maintenance_demo

PostgreSQL 18 reports an unrecognized or invalid option because –dry-run is not supported.

PostgreSQL 19
Run:

/usr/lib/postgresql/19/bin/vacuumdb \
  --dry-run \
  --verbose \
  maintenance_demo

#Output:
vacuumdb: vacuuming database "maintenance_demo"
VACUUM public.employee;

The exact output depends on the database objects and options supplied.

vacuumdb: Analyzing partitioned tables

Example:

Create a partitioned table:

CREATE TABLE public.sales (sale_id integer,sale_date date,amount numeric) PARTITION BY RANGE (sale_date);
CREATE TABLE public.sales_2025
PARTITION OF public.sales
FOR VALUES FROM ('2025-01-01')
TO ('2026-01-01');
CREATE TABLE public.sales_2026
PARTITION OF public.sales
FOR VALUES FROM ('2026-01-01')
TO ('2027-01-01');

Insert sample data:

INSERT INTO public.sales
SELECT i, DATE '2025-01-01' + (i % 365),(i % 1000) + 10
FROM generate_series(1, 10000) AS i;

PostgreSQL 18

/usr/lib/postgresql/18/bin/vacuumdb \
  --analyze-only \
  --echo \
  maintenance_demo

With no explicit table targets, PostgreSQL 18’s vacuumdb does not analyze the partitioned parent table as part of this operation. It only analyzes the partitions.

PostgreSQL 19

/usr/lib/postgresql/19/bin/vacuumdb \
  --analyze-only \
  --echo \
  maintenance_demo

PostgreSQL 19 includes all partitioned table as well as the partitions in the analysis when no targets are specified.

#Output:
ANALYZE public.sales;
ANALYZE public.sales_2025;
ANALYZE public.sales_2026;

pg_verifybackup: Verify WAL stored in tar archives

What does pg_verifybackup do?

pg_verifybackup checks a physical backup against its backup manifest. It can verify that the expected files are present and that their contents match the manifest, and it can validate WAL when WAL parsing is enabled and supported.

PostgreSQL 19 adds the ability to read and verify WAL files stored inside tar archives directly. PostgreSQL 18 cannot perform that WAL verification directly from the tar archive.

This is particularly relevant when backups are produced using pg_basebackup with tar format.
Create a tar-format backup
For example, run:

mkdir -p /backups/pg19_backup
pg_basebackup \
  -h localhost \
  -p 5432 \
  -U postgres \
  -D /backups/pg19_backup \
  -F tar \
  -X stream \
  -P
A typical backup directory may contain:
/backups/pg19_backup/
├── backup_manifest
├── base.tar
└── pg_wal.tar

PostgreSQL 18
For a tar-format backup, you can verify the backup files while skipping WAL parsing:

/usr/lib/postgresql/18/bin/pg_verifybackup \
  --no-parse-wal \
  /backups/pg18_backup

#Output:
backup successfully verified

The important limitation is that this invocation does not verify the WAL by parsing it. A successful result should not be interpreted as confirmation that the WAL contents have passed WAL parsing.

PostgreSQL 19
Run the verification without skipping WAL parsing:

/usr/lib/postgresql/19/bin/pg_verifybackup \
  /backups/pg19_backup

PostgreSQL 19 can read WAL stored in the tar archive during verification.

#output:
backup successfully verified

PostgreSQL 19 also supports –wal-path as an alias for the existing –wal-directory option. For example:

/usr/lib/postgresql/19/bin/pg_verifybackup \
  --wal-path=/backups/pg19_backup/pg_wal.tar \
  /backups/pg19_backup

Use the option according to the layout of your backup and the utility’s documented path expectations.

pg_waldump: Read WAL directly from tar archives

What is pg_waldump?

pg_waldump is a command-line utility for displaying and inspecting PostgreSQL Write-Ahead Log (WAL) records.

WAL records help DBAs investigate database activity, diagnose issues, and understand changes recorded for crash recovery and replication.

PostgreSQL 19 allows pg_waldump to read WAL files directly from a tar archive. PostgreSQL 18 expects a directory-based WAL location.

PostgreSQL 18: Extract the archive first

If the WAL is in a tar archive, extract it before invoking pg_waldump.

mkdir -p /backups/pg18_wal
tar -xf /backups/pg18_backup/pg_wal.tar \
 -C /backups/pg18_wal

Then inspect a few records:

/usr/lib/postgresql/18/bin/pg_waldump \
-p /backups/pg18_wal \
-n 5
output:
rmgr: XLOG        len (rec/tot): ...
rmgr: Transaction len (rec/tot): ...
rmgr: Heap        len (rec/tot): ...

The actual record types and details depend on the WAL generated by your workload.

PostgreSQL 19: Read the archive directly

/usr/lib/postgresql/19/bin/pg_waldump \
-p /backups/pg19_backup/pg_wal.tar \
-n 5

PostgreSQL 19 can read the WAL records directly from the tar archive, avoiding the preliminary extraction step.

Filtering WAL records
For example, you can filter by transaction ID:

/usr/lib/postgresql/19/bin/pg_waldump \
-p /backups/pg19_backup/pg_wal.tar \
--xid=12345 \
-n 10

pg_upgrade: Faster large-object metadata copying

What are PostgreSQL large objects?

PostgreSQL large objects let applications store data that is accessed through the large-object API. Their metadata is stored in pg_largeobject_metadata, while the large-object contents are stored separately.

During a major-version upgrade, PostgreSQL must preserve large objects and their associated metadata, including ownership and permissions.

PostgreSQL 19 improves pg_upgrade performance by using more efficient methods to transfer large-object metadata. For binary upgrades from PostgreSQL 16 or newer, the metadata transfer can use direct file transfer instead of generating many individual SQL commands. For older source versions, a bulk COPY method is used where appropriate.

The exact method depends on the source cluster version and metadata compatibility.

Check the number of large objects

Run on the source database:

SELECT COUNT(*) AS large_objects
FROM pg_catalog.pg_largeobject_metadata;
output:
large_objects
--------------
 100000

A large number of large objects can increase the work required during a major-version upgrade.

PostgreSQL 18: More individual metadata operations
For the test case described below, a PostgreSQL 16 source upgraded to PostgreSQL 18 required many individual metadata-related commands.

SELECT lo_create(0);
ALTER LARGE OBJECT 100001 OWNER TO alice;
GRANT SELECT ON LARGE OBJECT 100001 TO bob;

PostgreSQL 19: More efficient metadata transfer
For a PostgreSQL 16 or newer source cluster, PostgreSQL 19 can transfer the relevant large-object metadata files directly during a binary upgrade, rather than relying on a large number of individual SQL commands.

A pre-upgrade check can be run with:

/usr/lib/postgresql/19/bin/pg_upgrade \
--old-bindir=/usr/lib/postgresql/16/bin \
--new-bindir=/usr/lib/postgresql/19/bin \
--old-datadir=/var/lib/postgresql/16/main \
--new-datadir=/var/lib/postgresql/19/main \
--check

Adjust the binary and data directories to match your environment. The –check option checks upgrade compatibility; it does not perform the actual upgrade.

pgbench –continue-on-error: Continue after SQL errors

What is pgbench?

pgbench is PostgreSQL’s benchmarking utility. It can run predefined or custom SQL workloads to measure throughput, latency, and behavior under concurrent load.

In PostgreSQL 19, pgbench adds –continue-on-error, allowing a benchmark run to continue when SQL statements encounter errors instead of stopping immediately due to those errors.

This can be useful when testing error-handling scenarios or workloads where some SQL errors are expected.

Example setup

Initialize a test database:

create database bench_demo;
pgbench -i -s 1 bench_demo

The initialization command creates the standard pgbench benchmark tables.

PostgreSQL 18
The PostgreSQL 18 pgbench does not support –continue-on-error. For example:

/usr/lib/postgresql/18/bin/pgbench \
 --continue-on-error \
-c 2 \
-T 10 \
bench_demo

Expected behavior: the utility reports an unrecognized option.

PostgreSQL 19

/usr/lib/postgresql/19/bin/pgbench \
--continue-on-error \
-c 2 \
 -T 10 \
bench_demo

PostgreSQL 19 accepts the option and continues the benchmark after SQL errors according to the option’s documented behavior

Conclusion

PostgreSQL19 brings practical improvements to maintenance planning, partitioned-table statistics, tar-based backup verification, WAL inspection, major-version upgrades, and benchmark error handling.

The key benefit is not a single new command, but less manual work and smoother operational workflows. DBAs can preview maintenance, inspect archived WAL more easily, verify tar-based backups more completely, and reduce upgrade overhead for some large-object-heavy clusters.

As with any major-version upgrade, validate these improvements against your own workload, backup layout, tablespace configuration, and recovery requirements before adopting them in production.