PostgreSQL 19 adds pg_stat_recovery and WAL reading in tar backups

Engineer Pavel Luzanov, of Postgres Professional, published on Planet PostgreSQL the third installment (5.3) of the series following the March 2026 CommitFest for PostgreSQL 19, this time covering the DBA3 and DBS modules of the company's courses. The focus is clearly operational: backup, recovery, replication, and access control, the daily routine of whoever is on call for production. None of these changes is cosmetic; all of them affect how the DBA verifies, diagnoses, and audits what the database is doing.

pg_verifybackup and pg_waldump now read tar directly

Until this batch of commits (b15c1513984, b3cf461b3cf), analyzing the WAL of a backup packaged as tar required extracting the file first. Now pg_verifybackup and pg_waldump read and parse the WAL directly from inside the .tar, with no intermediate step. This removes a real bottleneck in backup pipelines: in the source's own example, a pg_basebackup --format=tar produced a base.tar of about 6.2 GB and a pg_wal.tar of only about 16 MB, and verifying the integrity of that backup meant decompressing everything before running the check.

The flow becomes more direct:

$ pg_basebackup --pgdata=. --format=tar
$ pg_verifybackup .
backup successfully verified

To go along with this change, pg_verifybackup gains the --wal-path option, which replaces --wal-directory (still supported, but marked as deprecated). In practical terms, this means less temporary disk space and a shorter verification window in backup routines that already use tar as the default format, something common in object storage and in archiving scripts for S3 or equivalent.

pg_stat_recovery: a single view for recovery state

Commit 01d485b142e introduces the pg_stat_recovery view, queryable during the startup process of a replica in physical replication. Previously, the same information existed scattered across system functions; now it's gathered into a single row:

SELECT * FROM pg_stat_recovery\gx
-[ RECORD 1 ]------------+------------------------------
promote_triggered | f
last_replayed_read_lsn | F/2F000028
last_replayed_end_lsn | F/2F000060
last_replayed_tli | 1
replay_end_lsn | F/2F000060
replay_end_tli | 1
recovery_last_xact_time |
current_chunk_start_time | 2026-09-08 13:55:36.859622+03
pause_state | not paused

Outside of recovery (on the primary, for example), the view simply returns no rows, which already serves as a quick test of the server's role. For those writing monitoring scripts, this removes the need to combine pg_last_wal_replay_lsn(), pg_is_in_recovery(), and other standalone functions into a single custom query: now it's one query, one source of truth.

psql indicates whether you're on a replica

One of the most costly mistakes in database operations is running a write command thinking you're on the primary when, in fact, the session is on a replica (or vice versa, when testing failover). Commit dddbbc253b9 fixes this with the new %i escape in PROMPT1/PROMPT2, which shows standby or primary depending on the state of in_hot_standby:

\set PROMPT1 [%i]:PROMPT1
[primary]postgres@postgres(19.0)=#

When switching connections, the prompt changes on its own to [standby]. It's a small detail, but one that matters especially when the connection string lists multiple hosts and psql automatically picks which one serves the session: without this indicator, the only way to know where you were was to run SHOW in_hot_standby manually every time the context changed.

Fine-grained timeout control in replication

Two new parameters address a recurring problem in shutdown and in environments with multiple logical subscriptions:

• wal_sender_shutdown_timeout (commit a8f45dee917): limits how long the server waits, during shutdown, for all pending WAL to be replicated to receivers. By default there is no limit, which historically caused shutdowns to hang when the subscriber's apply worker got stuck waiting on a lock.
• wal_receiver_timeout (commits 8a6af3ad087, fb80f388f4a): can now be configured per subscription, via CREATE|ALTER SUBSCRIPTION, or per subscription owner, instead of only globally via sighup. Useful when the same server maintains subscriptions to publishers with very different network latency from one another.

Together, these two parameters spare the DBA from having to choose between a shutdown that never finishes and an abrupt cutoff that risks losing WAL that hasn't been replicated yet.

GRANT/REVOKE with GRANTED BY: real delegation

The SQL standard has always included the GRANTED BY clause in GRANT/REVOKE, to specify on behalf of which role the privilege is granted or revoked. PostgreSQL accepted the syntax but failed with ERROR: grantor must be current user whenever someone tried to use a role other than the current one. Commit dd1398f1378 fixes this, as long as the role named in GRANTED BY actually has the right to grant the privilege and whoever runs the command inherits that role via INHERIT.

In the source's example, charlie inherits alice and bob with INHERIT true, and can therefore grant SELECT on behalf of either of them:

charlie=> GRANT SELECT ON test TO dave GRANTED BY bob;
GRANT

This fixes a structural governance problem: until now, delegating the granting of privileges on behalf of another role required the owner itself to run the command, or for Postgres to pick the grantor on its own, with no choice involved. In environments with multiple teams administering different schemas under shared roles, this is the difference between reliable auditing and a pg_catalog log that doesn't reflect who actually authorized what.

Password: expiration warning and the announced end of MD5

Two security changes round out the DBA3/DBS package. The new password_expiration_warning_threshold (commit 1d92e0c2cc4, default of 7 days) makes the server warn the user, at connection time, when the password is close to expiring:

WARNING: role password will expire soon
DETAIL: The password for role "alice" will expire in 5 hours.

And since version 18, Postgres already warned, when setting a password with MD5, that support would be removed; now (commit bc60ee86066) the warning also appears when authenticating with an existing MD5 password, which makes it much harder to ignore legacy databases that haven't yet migrated to SCRAM-SHA-256. Anyone administering older clusters should treat this as a deadline, not noise: the md5_password_warnings parameter silences the warning but doesn't fix the underlying problem.

What else landed in this batch

Beyond the core described above, the same March 2026 CommitFest batch includes items that affect the logical replication and packaging side more than day-to-day monitoring: CREATE|ALTER PUBLICATION with EXCEPT (TABLE ...) to exclude specific tables from a FOR ALL TABLES; CREATE SUBSCRIPTION accepting SERVER (a postgres_fdw server) instead of a raw CONNECTION, allowing credentials to be managed via USER MAPPING without exposing the connection string to whoever creates the subscription; pg_createsubscriber --logdir for writing diagnostics to a file instead of only to the console; server-side SNI via ssl_sni and the new pg_hosts.conf, useful for providers serving multiple virtual clusters behind the same IP; and pg_read_all_data/pg_write_all_data now covering large objects, which closes a gap that kept a non-superuser role from making a full copy of a database with LOBs.

What remains open

None of these changes, by themselves, solves the work of designing a backup or replication strategy; they remove specific operational friction. pg_stat_recovery still doesn't replace external observability tools with history and alerting, it only consolidates the instantaneous state. And the loosening of GRANTED BY requires discipline in role modeling: without a well-thought-out INHERIT hierarchy, the new syntax doesn't solve anything on its own. As usual with a CommitFest, the real value only shows up when the DBA adjusts monitoring scripts and playbooks to actually use what was released, not just knows that it exists.