Postgres replica conflicts: Part 1

Photo by Bundo Kim on Unsplash

A replication conflict occurs whenever the recovery process cannot apply WAL information from the primary server to the standby, because the change would disrupt query processing there. These conflicts cannot happen with queries on the primary server, but they happen on the streaming replication standby server because the primary server has limited knowledge about what is going on on the standby.

select *from pg_stat_database_conflicts;datid | datname | confl_tablespace | confl_lock | confl_snapshot | confl_bufferpin | confl_deadlock— — — -+ — — — — — -+ — — — — — — — — — + — — — — — — + — — — — — — — — + — — — — — — — — -+ — — — — — — — —16404 | myservice | 0 | 0 | 3 | 0 | 016403 | rdsadmin | 0 | 0 | 0 | 0 | 016401 | postgres | 0 | 0 | 0 | 0 | 014372 | template0 | 0 | 0 | 0 | 0 | 016402 | template1 | 0 | 0 | 0 | 0 | 0

Number of queries in this database that have been canceled due to old snapshots

Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still “see” any of the rows to be removed.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store