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 [, ...])).

Example:

    wal_level = replica
    max_wal_senders = 5
    synchronous_commit = on
    synchronous_standby_names = 'FIRST 1 (sync_standby_1)'

3. 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
     

4. 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 postgresql.auto.conf file with replication settings.

2. Adjust Standby Settings (Optional):

   • You might want to adjust settings specifically for the standby server in postgresql.auto.conf 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.