Replication configuration changes in PostgreSQL 12

Historically, PostgreSQL’s replication configuration has been managed via a configuration parameters stored in a dedicated configuration file, recovery.conf, which has been present in PostgreSQL since the introduction of archive recovery in PostgreSQL 8.0.

One of the major changes in PostgreSQL 12, co-authored by 2ndQuadrant is the replacement of recovery.conf and the conversion of recovery.conf parameters to normal PostgreSQL configuration parameters. This removes the need for a separate configuration file and enables replication to be configured in the same way as other configuration parameters, including via the ALTER SYSTEM command.

This article provides an overview of the changes and potential issues which may be encountered when migrating replication configuration to PostgreSQL 12.

File changes

“recovery.conf” is no longer valid, and its presence in a PostgreSQL 12 data directory will cause the PostgreSQL instance to refuse to start with the following error:

FATAL: XX000: using recovery command file "recovery.conf" is not supported

In its place, one of two “signal” files may be placed in the data directory:

• “standby.signal” – indicates the server should start up as a hot standby
• “recovery.signal” – indicates the server should start up in targeted recovery mode

If both files are present, “standby.signal” takes precedence. The files do not need to contain any data; any data present will be ignored.

If a standby is promoted, “standby.signal” is removed entirely (and not renamed as was the case with “recovery.conf”, which became “recovery.done”). Similarly, if point-in-time recovery is taking place, “recovery.signal” will be removed once the recovery target is reached (unless “recovery_target_action” is set to “shutdown”).

Note that as with “recovery.conf”, the absence of these files from the directory will cause the PostgreSQL instance to start up straight away as a primary. This will happen regardless of whether “postgresql.conf” contains replication configuration.

Parameter changes

The replication configuration parameters previously stored in “recovery.conf” are largely unchanged, with the following exceptions.

• “standby_mode”: The former parameter “standby_mode” has been removed and has been replaced by the “standby.signal” and “recovery.signal” files described above.
• “trigger_file”: The parameter “trigger_file” has been renamed to “promote_trigger_file“.

In PostgreSQL 11 and earlier, to apply changes to “recovery.conf”, a full server restart was required . While this is still largely true in PostgreSQL 12 and later, the following replication configuration items can now be changed via SIGHUP:

• archive_cleanup_command
• promote_trigger_file
• recovery_end_command
• recovery_min_apply_delay

The following query lists all the former “recovery.conf” parameters:

SELECT name, setting, category, short_desc, context, pending_restart
FROM pg_catalog.pg_settings
WHERE category IN('Write-Ahead Log / Archive Recovery','Write-Ahead Log / Recovery Target')
OR name IN ('primary_conninfo','primary_slot_name','promote_trigger_file','recovery_min_apply_delay')
ORDER BY category, name;

Gotchas

While on the face of it, administering replication configuration in the same way as other PostgreSQL configuration parameters seems more convenient, there are a couple of potential gotchas where behaviour has changed and

• “ALTER SYSTEM” settings always take priority
  If replication settings are stored in postgresql.conf, and someone changes a setting by executing “ALTER SYSTEM”, the parameter set by “ALTER SYSTEM” (and written to postgresql.auto.conf) will always take priority. This may cause confusion if subsequent attempts are made to update replication settings in postgresql.conf and no consideration is given to the possibility that settings may also be present in postgresql.auto.conf.
• Replication configuration settings may be present even on primary servers
  Any replication configuration settings (e.g. “primary_conninfo”) configured will be read by PostgreSQL and will be visible as normal, but their presence does not indicate whether the node is a standby or not.
• No canonical location to write configuration settings
  With “recovery.conf” there was a single well-known location for writing replication settings, which would guarantee they would be read at server startup. From PostgreSQL 12, replication settings can be located anywhere with the normal PostgreSQL configuration file(s). As the last configuration parameter read takes priority, there’s a risk that later parameters are overlooked and PostgreSQL is mistakenly started with the incorrect settings. Utilities which write replication configuration settings (such as pg_basebackup or repmgr) and which need to ensure that those settings are read last, therefore need to append them to the “postgresql.auto.conf” file.
• Risk of signal file confusion
  As “standby_mode” has been replaced by the option to write one of two signal files, and as “standby.signal” takes priority over “recovery.signal”, there’s a risk that the presence of “standby.signal” is overlooked when setting up “recovery.signal”
• Only one parameter from the “recovery_target” family may be specified
  If more than one of “recovery_target”, “recovery_target_lsn”, “recovery_target_name”, “recovery_target_time” or “recovery_target_xid” is present in the PostgreSQL configuration, the instance will emit a FATAL error at startup:

  2019-10-07 15:29:41.265 JST [23382] FATAL: multiple recovery targets specified
  2019-10-07 15:29:41.265 JST [23382] DETAIL: At most one of recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, recovery_target_xid may be set.

  In PostgreSQL 11 and earlier, the last instance of these parameters was used and preceding parameters ignored.

Links

• PostgreSQL documentation: Replication configuration (standby servers)
• PostgreSQL documentation: Recovery target configuration