PostgreSQL Replication Conflicts: Avoiding Pitfalls
summary
Demo uses PostgreSQL 14.1 on CentOS 7 with a single primary and standby and shows two reproducible failures: a lock conflict when a standby transaction touches a table and the primary drops it after max_standby_streaming_delay = 30s, and a snapshot conflict when the primary VACUUMs rows the standby cursor still reads. Observability relies on pg_stat_activity/pg_locks and pg_stat_database conflicts counters; mitigation recommendations are increase wal_keep_size, tune streaming delay, consider hot_standby_feedback, and use PG Stat Monitor to persist transient diagnostics.
AI-written summary · about
chapters (31)
- 0:00Introduction
- 1:55Contact Hamid
- 2:12PostgreSQL Community Forum
- 3:02PostgreSQL SPGstatMonitor
- 5:01Outline
- 5:44Lightweight locks
- 5:54Spin locks
- 8:52Heavyweight locks
- 9:30Write ahead log
- 9:46Bright red log
- 11:57Wall files
- 13:58LSN
- 15:27History
- 19:41Synchronous
- 21:06Replication Views
- 22:50Conflicts
- 23:37Configuration Parameters
- 27:36Replication Conflicts
- 28:52Survey Results
- 29:28Key Issues
- 30:12Conflict Types
- 31:11Lock vs Snapshot
- 31:51Environment
- 33:46Under the Hood
- 35:13Understanding Your Workload
- 36:48Snapshot Conflict
- 37:51Database Conflict
- 38:04Code
- 38:51Vacuum
- 39:59Closing Thoughts
- 41:22Thank You
related talks
51mPostgreSQL Replication Conflicts: Avoiding Pitfalls
51mPostgreSQL Replication Conflicts: Avoiding Pitfalls
48mPostgreSQL Replication: 20 Pitfalls and Solutions
48mReplication Refresh
22mSecuring Postgres with streaming replication
49mPostgreSQL Replication - The Most Exciting Technology on Earth
02h 26mTutorial
55mHigh Availability with PostgreSQL and Pacemaker