Synchronous replication - PostgreSQL 17

1

In synchronous replication, if Primary (pgnode1) goes down, then pgnode 2 (standby) becomes primary and write operations are hung in pgnode2 with below error DETAIL: The transaction has already committed locally, but might not have been replicated to the standby. We are using Patroni and etcd, PostgreSQL17.
synchronous_mode: true
synchronous_mode_strict: false
synchronous_commit: ‘on’
synchronous_standby_names: ‘pgnode1’

How to resolve this issue?

2

Hi,

There’s two ways to solve your problem i.e. the easy way and the hard way :slight_smile:

Hard way: Since the transaction to the REPLICA has not been received you will need to tell the service to ignore it by identifying the LSN and “skipping it”. Refer to the documentation for pg_replication_slot_advance

Easy way: update synchronous_commit i.e. use either remote_apply or remote_write

-- execute on PRIMARY 
alter system set  synchronous_commit=remote_write
select pg_reload_conf();