Schedule - PGConf.EU 2023
PostgreSQL Replication: 20 Pitfalls and Solutions
Replication is everywhere: We use it for redundancy, high availability, aggregation or distribution of data, database migration and upgrading, or even horizontal scale-out. While replication is well supported and easy to get started with in PostgreSQL, there are some misconceptions, misconfigurations, corner cases, and other issues to be aware of that can make or break a project.
This talk covers both replication mechanisms that are built into PostgreSQL: binary replication and logical replication. We will discuss common issues, such as WAL recycling or long running transactions, that need to be considered in any replication setup. We will also discuss complex issues, such as transaction snapshots growing too large or subtransaction overflow, that might prevent replication or result in downtime when certain circumstances collide. All in all, we will cover at least 20 different pitfalls and their solutions.
I've been installing, administrating and most importantly troubleshooting PostgreSQL replication setups for over five years for large and small customers, running a lot of different workloads and applications on their databases. Last year I finished my Master’s degree with a thesis on PostgreSQL replication mechanisms and recently I discovered and fixed a serious bug with recovery of prepared transactions in PostgreSQL 13 and 14.
The following slides have been made available for this session: