2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Ian's PlanetPostgreSQL3 / Replication configuration changes in PostgreSQL 12
Replication configuration changes in PostgreSQL 12
Ian Barwick

Replication configuration changes in PostgreSQL 12

October 7, 2019/0 Comments/in Ian's PlanetPostgreSQL, repmgr /by Ian Barwick

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
Tags: high availability, PG12, Postgres12, PostgreSQL 12, replication, Replication configuration
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up]
  • Full-text search since PostgreSQL 8.3
  • Random numbers
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up]

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Webinar: New Features in Postgres 12 [Follow up] A convenient way to launch psql against postgres while running pg_regress A convenient way to launch psql against postgres while running pg_regress
Scroll to top
×