How to Set Up Synchronous Replication in PostgreSQL 16: An Easy Guide

Improving Data Stability and Coherence through Synchronous Standby Servers in PostgreSQL


3 min read

Implementing synchronous replication in PostgreSQL ensures that each write transaction is confirmed on both the primary and a designated synchronous standby server before it is considered complete. This setup enhances data durability and consistency at the cost of increased commit latency. Below is a step-by-step guide on setting up synchronous replication in PostgreSQL 16.

Step 1: Configure the Primary Server

  1. Identify or Modify the postgresql.conf File: Locate postgresql.conf on your primary server. This file is usually in the PostgreSQL data directory.

  2. Edit Replication Settings:

    • Enable WAL archiving by setting wal_level to replica or logical.

    • Specify the maximum number of WAL sender processes allowed to connect simultaneously with max_wal_senders.

    • Configure synchronous_commit to on to ensure transactions wait for confirmation that changes are on disk in the synchronous standby.

    • Define synchronous_standby_names to list one or more standby servers that can support synchronous replication. Use a comma-separated list or a priority-based list (FIRST num (name [, ...])).


    wal_level = replica
    max_wal_senders = 5
    synchronous_commit = on
    synchronous_standby_names = 'FIRST 1 (sync_standby_1)'
  1. Configure Client Authentication:

    • Edit pg_hba.conf to allow the standby server to connect to the primary server. Add a line for the replication connection:

        host  replication  all  standby_ip/32  trust
  2. Reload PostgreSQL Configuration:

    • Apply the changes without restarting PostgreSQL:

        SELECT pg_reload_conf();

Step 2: Prepare the Standby Server

  1. Create a Base Backup:

    • On the primary server, create a base backup using pg_basebackup or similar tools to initialize the standby server's data directory. For example:

        pg_basebackup -h primary_ip -D /path/to/standby/data/dir -U replicator -Fp -Xs -P -R
    • The R option generates a standby.signal file and partially fills the file with replication settings.

  2. Adjust Standby Settings (Optional):

    • You might want to adjust settings specifically for the standby server in or postgresql.conf, such as hot_standby = on.

Step 3: Start the Standby Server

  • Start PostgreSQL on the standby server. It should connect to the primary server as a standby and begin replicating data.

Step 4: Test Synchronous Replication

  • To test if synchronous replication is working:

    1. Create a test table and insert data on the primary server.

    2. Query this table on the standby server to confirm the data is replicated synchronously.

Additional Configuration Notes:

  • Multiple Standby Servers: If using multiple standbys, you can configure them for potential synchronous replication by listing them in synchronous_standby_names. PostgreSQL will wait for at least one of the listed standbys to confirm writes if set up with priorities.

  • Failover and Switchover: Plan for failover and switchover scenarios. In a failover, the synchronous standby becomes the new primary. Tools like repmgr can help automate failover procedures.

  • Monitoring: Monitor replication delay and performance impacts. Synchronous replication might introduce latency for write operations, as they must be confirmed by the standby server.

By following these steps, you set up a basic synchronous replication in PostgreSQL, enhancing your database's fault tolerance and data integrity.