There have been a lot of articles about new features in the recent PostgreSQL 13 release, but one set of improvements which seems to have escaped much attention is in pg_rewind, which has gained some additional useful functionality deserving of closer examination.

New option "-R /--write-recovery-conf"

As the name implies, this option causes pg_rewind to write recovery (replication) configuration (and additionally the "standby.signal" file) after rewinding a server, in exactly the same way that pg_basebackup does. This is useful for example when restoring a former primary to become a standby. Note that it can only be used in conjunction with the option --source-server, and (unless the --dry-run option is supplied) that the configuration will be written regardless of whether a rewind is needed.

As replication configuration via the recovery.conf file was removed in PostgreSQL 12, the generated replication configuration is actually appended to postgresql.auto.conf, which is the one configuration file which is guaranteed to be in a known location (the data directory) and which will be read last when PostgreSQL starts up, ensuring any prior replication configuration is overridden by whatever pg_rewind has written.

Currently the only replication configuration item actually written is primary_conninfo - be aware that this is generated from a blend of the connection information passed in --source-server and the default parameters generated by libpq, which may be different to the primary_conninfo string you would normally generate and will look something like this:

primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' channel_binding=prefer host=node1 port=5432
sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

Note that currently replication slot configuration (primary_slot_name) cannot be generated by pg_rewind.

Also be aware that if attempting to rewind a standby which was not shut down properly, the standby.signal file must be removed otherwise pg_rewind will attempt to start the server in single user mode to run recovery, which cannot be done with replication configuration in place. See also the section "Automatic crash recovery" below.

This option was added in commit 927474ce

New option "-c/--restore-target-wal"

pg_rewind requires that the server to be rewound (the "target server") contains WAL files reaching back to the point of divergence, which can result in situations like this:

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432'
pg_rewind: servers diverged at WAL location 0/30005C8 on timeline 1
(... snip log output from postgres starting up in single user mode ...)
pg_rewind: error: could not open file "/var/lib/pgsql/data/pg_wal/000000010000000000000002": No such file or directory
pg_rewind: fatal: could not find previous WAL record at 0/2000100

Previously, it would have been necessary to manually retrieve any missing files, however from PostgreSQL 13 pg_rewind provides the -c/--restore-target-wal option, which uses the local server's restore_command to fetch required files.

Assuming restore_command is correctly defined and the required WAL files are available in the archive, with c/--restore-target-wal the preceding scenario should now succeed:

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432' --restore-target-wal
pg_rewind: servers diverged at WAL location 0/30005C8 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/2000060 on timeline 1
pg_rewind: Done!

Note that there is no mention of whether any missing files were retrieved with via the restore_command; this is only shown in the (extremely verbose) --debug output mode. Any errors will however be reported, e.g.:

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432' --restore-target-wal
pg_rewind: servers diverged at WAL location 0/30005C8 on timeline 1
cp: cannot stat "/var/lib/wal-archive/000000010000000000000002": No such file or directory
pg_rewind: error: could not restore file "000000010000000000000002" from archive
pg_rewind: fatal: could not find previous WAL record at 0/2000100

(Disclaimer: the use of cp in restore_command is for demonstration purposes only and is not recommended for real-world scenarios.)

This option was added in commit a7e8ece4.

Automatic crash recovery

pg_rewind can only operate on a cleanly shut down PostgreSQL instance, otherwise it won't be able to correctly determine which (if any) changes need to be applied. in PostgreSQL 12 and earlier, this meant starting the server (preferably in single user mode) so it can perform crash recovery, before stopping it again and executing pg_rewind:

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432'
pg_rewind: fatal: target server must be shut down cleanly

With PostgreSQL 13, when pg_rewind detects the server was not shut down cleanly, it will automatically start the server (in single user mode) to ensure crash recovery is performed. This simplifies the process of recovering a crashed server.

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432'
pg_rewind: executing "/usr/bin/pgsql/postgres" for target server to complete crash recovery
(... snip log output from postgres starting up in single user mode ...)
pg_rewind: servers diverged at WAL location 0/30005D0 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/2000060 on timeline 1
pg_rewind: Done!

Be aware however that unless one of the options --no-ensure-shutdown or --dry-run is provided, pg_rewind will perform automatic crash recovery before performing any other checks.

If --dry-run is provided, pg_rewind will indicate it is going to perform crash recovery, but not actually do so, and will exit with:

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432' --dry-run
pg_rewind: executing "/usr/bin/pgsql/postgres" for target server to complete crash recovery
pg_rewind: fatal: target server must be shut down cleanly

Also, as PostgreSQL's single user mode cannot be used when the server is in recovery, the presence of a standby.signal file (in the case of a former standby) will cause the operation to fail and pg_rewind to abort:

$ pg_rewind -D /var/lib/pgsql/data --source-server='host=node1 dbname=postgres user=postgres port=5432'
pg_rewind: executing "/usr/bin/pgsql/postgres" for target server to complete crash recovery
....
[2020-10-20 23:47:21 JST]    FATAL:  0A000: standby mode is not supported by single-user servers
....
pg_rewind: error: postgres single-user mode in target cluster failed
pg_rewind: fatal: Command was: "/usr/bin/pgsql/postgres" --single -F -D "/var/lib/pgsql/data" template1 < "/dev/null"

In this case standby.signal would need to be manually removed before, and replaced after the rewind operation.

This functionality was added in commit 5adafaf1.