Request a Demo
Home   >   Blog
  •   PostgreSQL   •   By Pavel Luzanov

PostgreSQL 19: part 5.3 (DBA3 and DBS) of CommitFest 2026-03

PostgreSQL
Source:
habr.com Subscribe to blog
Source:
habr.com Subscribe to blog

We’re continuing our review of the March CommitFest for PostgreSQL 19. This third article in the series covers the changes related to our DBA3 and DBS courses.

As a reminder, here’s the plan for our coverage of the final PostgreSQL 19 CommitFest:

Part 5.1 (QPT)

Part 5.2 (DBA1, DBA2)

Part 5.3 (DBA3, DBS) -- you are here 🙂

Part 5.4 (SQL, DEV1, DEV2)

For the earlier CommitFests, see: 2025-07, 2025-09, 2025-11, 2026-01.

Here’s what’s covered:

pg_verifybackup, pg_waldump: reading WAL from tar archives

commit: b15c1513984, b3cf461b3cf

Previously, pg_verifybackup and pg_waldump could only analyze WAL files that weren’t packed into an archive. To work with a tar-format backup, you had to extract it first. Now both utilities can read and parse WAL directly from a tar file.

$ pg_basebackup --pgdata=. --format=tar
$ ls -l
total 6088948
-rw------- 1 pal pal     192411 Oct  5 12:06 backup_manifest
-rw------- 1 pal pal 6218102272 Oct  5 12:06 base.tar
-rw------- 1 pal pal   16778752 Oct  5 12:06 pg_wal.tar
$ pg_verifybackup .
backup successfully verified

Since WAL files can now live in a tar archive as well as in a directory, pg_verifybackup gets a new --wal-path option that replaces the old --wal-directory. The --wal-directory option is deprecated but still supported for backward compatibility.

pg_stat_recovery: monitoring replication and recovery

commit: 01d485b142e

The new pg_stat_recovery system view shows the current state of recovery. In physical replication, recovery is performed by the startup process on the replica, so that’s where you query the view. It returns 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, for example on the primary, the view returns no rows.

Some of this information was already available through various system functions, but now there’s a single view for monitoring recovery on the replica.

psql: replica indicator in the prompt

commit: dddbbc253b9

Until now, there was no way to tell from the psql prompt whether you were connected to a replica or to the primary. The new %i escape in the PROMPT1/PROMPT2 variables shows whether the server is running in hot standby mode (standby) or not (primary):

postgres@postgres(19.0)=# \dconfig in_hot_standby
List of configuration parameters
   Parameter    | Value 
----------------+-------
 in_hot_standby | off
(1 row)
postgres@postgres(19.0)=# \set PROMPT1 [%i]:PROMPT1
[primary]postgres@postgres(19.0)=#

When you switch to a replica, the prompt changes automatically:

[primary]postgres@postgres(19.0)=# \c - - - 5402
You are now connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5402".
[standby]postgres@postgres(19.0)=# \dconfig in_hot_standby
List of configuration parameters
   Parameter    | Value 
----------------+-------
 in_hot_standby | on
(1 row)
[standby]postgres@postgres(19.0)=#

This is especially handy when the connection string lists multiple hosts.

pg_createsubscriber --logdir: writing diagnostics to files

commit: d6628a5ea0a, 6b5b7eae3ae, 847336ba53a

The pg_createsubscriber utility (which creates a logical replica from a physical standby) used to print all diagnostic messages to the console only, which isn’t always convenient for automation. The new --logdir option redirects the output to files:

$ pg_createsubscriber -D . -P "host=localhost port=5401" -d demo --logdir=log

Inside the log directory, each run gets its own timestamped subdirectory containing the log files.

$ ls -lR log
log:
total 8
drwx------ 2 pal pal 4096 Oct  5 13:10 20261005T121108.763
drwx------ 2 pal pal 4096 Oct  5 13:11 20261005T131143.866

log/20261005T121108.763:
total 4
-rw------- 1 pal pal 403 Oct  5 13:10 pg_createsubscriber_internal.log

log/20261005T131143.866:
total 8
-rw------- 1 pal pal    0 Oct  5 13:11 pg_createsubscriber_internal.log
-rw------- 1 pal pal 4375 Oct  5 13:11 pg_createsubscriber_server.log

Publications: excluding specific tables

commit: fd366065e06, 493f8c6439c, 5984ea868ee

When you create a publication, you can specify FOR ALL TABLES to include every table in the database. But what if you need all tables except a few?

In version 19, CREATE|ALTER PUBLICATION let you exclude specific tables from a publication:

CREATE PUBLICATION pub FOR ALL TABLES EXCEPT (TABLE seats, boarding_passes);

\dRp+
                                                       Publication pub
  Owner   | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root | Description 
----------+------------+---------------+---------+---------+---------+-----------+-------------------+----------+-------------
 postgres | t          | f             | t       | t       | t       | t         | none              | f        | 
Except tables:
    "bookings.boarding_passes"
    "bookings.seats"

Logical replication subscriptions: connecting via postgres_fdw

commit: 8185bb53476

Try to spot what’s changed in the CREATE SUBSCRIPTION syntax:

\h create subscription
Command:     CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_name
    { SERVER server_name | CONNECTION 'conninfo' }
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

But they’re there. To connect to the publisher, you can now specify the name of a foreign server (SERVER) instead of a connection string (CONNECTION). The foreign server here is a postgres_fdw server.

This way, connection settings and subscriptions can be managed independently. Multiple subscriptions to the same publisher no longer need their own copy of the connection string, and you can change it in one place without touching the subscriptions. You can also use user mappings (USER MAPPING) to control access on a per-user basis.

So, we have a publication pub in the demo database:

publisher=# \dRpx pub
List of publications
-[ RECORD 1 ]-----+---------
Name              | pub
Owner             | postgres
All tables        | t
All sequences     | f
Inserts           | t
Updates           | t
Deletes           | t
Truncates         | t
Generated columns | none
Via root          | f

Let’s set up a foreign server in another cluster that already has the tables needed for logical replication:

postgres@subscriber=# CREATE EXTENSION postgres_fdw;

postgres@subscriber=# CREATE SERVER demo_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'localhost', port '5401', dbname 'demo');

Next, create a role that will set up the logical replication subscription. It has the privileges it needs but isn’t a superuser:

postgres@subscriber=# CREATE ROLE alice LOGIN;
postgres@subscriber=# GRANT pg_create_subscription TO alice;
postgres@subscriber=# GRANT CREATE ON DATABASE demo TO alice;

postgres@subscriber=# GRANT USAGE ON SCHEMA bookings TO alice;
postgres@subscriber=# GRANT ALL ON ALL TABLES IN SCHEMA bookings TO alice;
postgres@subscriber=# GRANT USAGE ON FOREIGN SERVER demo_server TO alice;

Set up a user mapping for alice. Note that alice doesn’t need to know the connection details, including the password:

postgres@subscriber=# CREATE USER MAPPING FOR alice
  SERVER demo_server
  OPTIONS (user 'postgres', password 'postgres');

Now alice can create a subscription by specifying the foreign server instead of a connection string:

alice@subscriber=> CREATE SUBSCRIPTION sub
  SERVER demo_server
  PUBLICATION pub
  WITH (run_as_owner = true);

wal_sender_shutdown_timeout: limiting the wait for WAL replication at shutdown

commit: a8f45dee917

When the server shuts down, walsender processes wait until all pending WAL has been sent to their receivers on the replicas. Sometimes, though, they get stuck for a long time waiting for a response from the receiver. This is especially true for logical replication, when the apply worker on the subscriber is blocked waiting for locks.

The new wal_sender_shutdown_timeout parameter sets the maximum time the server waits during shutdown for all WAL data to be replicated to the receiver. By default, there’s no time limit:

\dconfig+ wal_sender_shutdown_timeout
                      List of configuration parameters
          Parameter          | Value |  Type   | Context | Access privileges 
-----------------------------+-------+---------+---------+-------------------
 wal_sender_shutdown_timeout | -1    | integer | user    | 
(1 row)

wal_receiver_timeout: per-user and per-subscription settings

commit: 8a6af3ad087, fb80f388f4a

If a server has several subscriptions to different publishers, you can now give each one its own timeout for terminating inactive replication connections.

First, CREATE|ALTER SUBSCRIPTION now accept a new wal_receiver_timeout subscription parameter. Second, the wal_receiver_timeout configuration parameter can now be set for the user who owns the subscription (context=user), rather than only server-wide (sighup), as before.

SSL: server-side SNI

commit: 4f433025f66, 4abcdc1bbeb

Client-side SNI support was added back in version 14 with the libpq sslsni connection parameter. However, only external TLS proxies sitting in front of the PostgreSQL server could take advantage of it. The PostgreSQL server itself ignored this information and always presented the client with the same certificate configured in postgresql.conf.

In version 19, the PostgreSQL server can present different SSL certificates depending on the hostname the client specifies when connecting. This can be useful for cloud providers that serve several virtual PostgreSQL servers from a single IP address and port.

To configure SNI on the server, enable the new ssl_sni parameter:

\dconfig+ ssl_sni
            List of configuration parameters
 Parameter | Value | Type | Context | Access privileges 
-----------+-------+------+---------+-------------------
 ssl_sni   | off   | bool | sighup  | 
(1 row)

You also need to map hostnames to certificates and keys in the new pg_hosts.conf configuration file. In a freshly initialized cluster, the file is empty:

$ cat $PGDATA/pg_hosts.conf
# PostgreSQL SNI Hostname mappings
# ================================

# HOSTNAME       SSL CERTIFICATE             SSL KEY     SSL CA       PASSPHRASE COMMAND         PASSPHRASE COMMAND RELOAD

If ssl_sni is enabled and pg_hosts.conf has entries, they take precedence over the SSL parameters in postgresql.conf.

GRANT/REVOKE … GRANTED BY

commit: dd1398f1378

The GRANT and REVOKE commands support the GRANTED BY clause, which specifies the role that should grant or revoke the privilege. The SQL standard requires this clause. In practice, though, you could only specify the current role. Specifying any other role resulted in an error:

ERROR:  grantor must be current user

In version 19, you can grant or revoke privileges on behalf of a specified role, provided that:

  • The role in GRANTED BY actually has the right to grant or revoke the privilege and could run the command itself.
  • The role running the command inherits the privileges of the role in GRANTED BY (directly or through a chain of memberships with INHERIT).

Here’s an example with four roles: alice, bob, charlie, and dave.

A superuser has created a table and granted the SELECT privilege on it to alice and bob with the grant option:

postgres=# CREATE TABLE test (id int);
postgres=# GRANT SELECT ON test TO alice WITH GRANT OPTION;
postgres=# GRANT SELECT ON test TO bob WITH GRANT OPTION;

The superuser has also made charlie a member of alice and bob with the INHERIT membership option:

postgres=# GRANT alice TO charlie WITH INHERIT true, SET false;
postgres=# GRANT bob TO charlie WITH INHERIT true, SET false;

Now charlie can grant SELECT on the table to other roles on behalf of either alice or bob.

charlie=> GRANT SELECT ON test TO dave GRANTED BY bob;
GRANT
charlie=> \dp test
                                 Access privileges
 Schema | Name | Type  |     Access privileges      | Column privileges | Policies 
--------+------+-------+----------------------------+-------------------+----------
 public | test | table | postgres=arwdDxtm/postgres+|                   | 
        |      |       | alice=r*/postgres         +|                   | 
        |      |       | bob=r*/postgres           +|                   | 
        |      |       | dave=r/bob                 |                   | 
(1 row)

Revoking works too:

charlie=> REVOKE SELECT ON test FROM dave GRANTED BY bob;
REVOKE

Previously, PostgreSQL picked the grantor on its own in this situation, and you had no way to choose it.

Password expiration warning

commit: 1d92e0c2cc4

The new password_expiration_warning_threshold parameter controls how far in advance users are warned that their password is about to expire. The default is 7 days:

\dconfig+ password_expiration_warning_threshold
                           List of configuration parameters
               Parameter               | Value |  Type   | Context | Access privileges 
---------------------------------------+-------+---------+---------+-------------------
 password_expiration_warning_threshold | 7d    | integer | sighup  | 
(1 row)

Here’s what the warning looks like:

postgres=# \set tomorrow `date -d "tomorrow" "+%Y%m%d"`
postgres=# ALTER ROLE alice VALID UNTIL :'tomorrow';
ALTER ROLE
postgres=# \c - alice
Password for user alice: 
WARNING:  role password will expire soon
DETAIL:  The password for role "alice" will expire in 5 hours.
You are now connected to database "postgres" as user "alice".

MD5 passwords: more end-of-support preparations

commit: bc60ee86066

Since version 18, setting an MD5-encrypted password has produced a warning that MD5 password support will be removed in a future release. But the password was still set, and you could keep using it.

Now you also get a warning when you authenticate with such a password.

\connect - alice
Password for user alice: 
WARNING:  authenticated with an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
You are now connected to database "postgres" as user "alice".

You can turn these warnings off with the md5_password_warnings parameter. But it’s definitely time to think about moving to SCRAM-SHA-256.

pg_[read|write]_all_data: now for large objects too

commit: d9819760279

The pg_read_all_data and pg_write_all_data predefined roles previously didn’t cover large objects. As a result, a non-superuser role that was a member of pg_read_all_data couldn’t make a full copy of a database containing large objects.

Now pg_read_all_data and pg_write_all_data grant read and write access to large objects, respectively.

psql: comments for publications, subscriptions, and extended statistics

commit: aecc558666a

Previously, psql commands didn’t show comments on publications, subscriptions, and extended statistics objects. Now the corresponding commands display them: \dRp+ (publications), \dRs+ (subscriptions), and \dX+ (extended statistics):

CREATE PUBLICATION pub FOR ALL SEQUENCES;
COMMENT ON PUBLICATION pub IS 'Sequences';

\dRp+x
Publication pub
-[ RECORD 1 ]-----+----------
Owner             | postgres
All tables        | f
All sequences     | t
Inserts           | t
Updates           | t
Deletes           | t
Truncates         | t
Generated columns | none
Via root          | f
Description       | Sequences

 

That’s all for now. In the next and final review of the series, we’ll look at the changes related to SQL and to the DEV1 and DEV2 courses.

← Back to all articles

Pavel Luzanov