### The Many Faces of PostgreSQL Replication Whether running Postgres on premise or in the Cloud, on Docker, in Kubernetes pods, or anywhere else you can imagine, the principals of replication remain the same. Let's discuss PostgreSQL's streaming and logical replication methods - what options there are and what they mean, how to set them, and how to keep it all running. We'll try to understand not just what, but why you should use replication technologies in a Production system. We'll discuss the term High Availability in the context of a database, and compare this with Recoverability. Then we'll really get into the good stuff. PostgreSQL now ships with two major forms of replication: streaming and logical. Each has advantages and caveats, and we'll talk about when to use either or both. We'll walk through the configuration necessary, explain the available options, and talk through good practice for running a replicated database in your Production environment. Streaming Replication has been available in Postgres for several years now. As it is well tested and understood, we will spend the majority of our time discussing the configuration, security, and monitoring aspects of this core concept. Postgres 10 has seen the addition of Logical Replication, and many newcomers are sure to be curious about the configuration and maintenance of this exciting new technology. We will be sure to talk through the details of logical replication, and get a better understanding of what this feature offers. This talk is welcome to beginners. ### Database Replication #### What and why *(6 minutes)* ##### Availability Means. . . * Available systems respond to demand * Available systems react to system failure * Available systems can upgrade in place ##### Availability is not Recovery * Outages, corruption, and human error happen in the real world * Untargeted (and targeted) bitlocker-style ransom attacks are increasingly common #### Types of replication ##### Streaming *(16 minutes)* * What is the Write Ahead Log * WAL archiving and basebackup * Configuring Primary and Replica(s) * Considering replication slots * Querying replicas and `hot_standby_feedback` * Allowing access via `pg_hba.conf` * Measuring replication delay * Cascading replicas * Thinking about containers ##### Synchronous streaming *(4 minutes)* * What it offers * How to enable it * Multiple synchronous replicas ##### Logical Replication *(11 minutes)* * Limitations of the current implementation * Creating a Publication * Creating a Subscription * Starting and stopping with `ALTER SUBSCRIPTION` * Roles and security * Conflicts * Monitoring ##### Other methods *(1 minute)* * Trigger based, pgpool-ii, file system #### Failover *(5 minutes)* * Detecting a problem * Promoting a standby * recovery target timeline * Understanding the split-brain problem #### Conclusion *(2 minutes)* * Additional considerations ##### Questions *(5 minutes)*