PostgreSQL Replication Conflicts: Avoiding Pitfalls
summary
Shows how PostgreSQL counts and reacts to standby-only replication failures with concrete internals: the pg_stat_database_conflicts view has five counters and increments on explicit backend termination; conflicts occur only on standbys and commonly are lock and snapshot related; a reproduction on Postgres 14.1 (CentOS 7) demonstrates dropping a table or vacuuming while a long transaction holds a relation—default max_standby_streaming_delay 30s triggers termination; using hot_standby_feedback and inspecting pg_stat_replication/pg_stat_wal_receiver informs mitigation.
AI-written summary · about
chapters (15)
- 0:00Start
- 3:23Replication Issues - Overview Reliable replication scheme requires
- 4:47Presentation Outline
- 5:52Types of Locks in PostgreSQL
- 17:10Synching Up • Synchronous Streaming Replication
- 18:49Relevant Replication Catalogs and Views
- 20:47Replication Data: PostgreSQL Out of the Box • The only view to expose conflict statistics: Standby pg_stat_database_conflicts
- 21:52Replication Configuration • Configuration Parameters Sending Server
- 25:55What Conflicts?
- 27:04Replication Issues?
- 27:41Types of Replication Issues Faced
- 28:58Types of Conflict
- 32:34Replication: Lock Conflict
- 34:09Lock Conflict - Code Flow
- 44:34Snapshot Conflicts - Code Flow • Start XLog Processing
related talks
42mPostgreSQL Replication Conflicts: Avoiding Pitfalls
51mPostgreSQL Replication Conflicts: Avoiding Pitfalls
48mPostgreSQL Replication: 20 Pitfalls and Solutions
48mReplication Refresh
49mPostgreSQL Replication - The Most Exciting Technology on Earth
22mSecuring Postgres with streaming replication
22mPostgreSQL Disaster Recovery Fundamentals
44mThe journey towards active-active replication in PostgreSQL