DeployU
Interviews / Databases / How do you set up replication in PostgreSQL?

Questions

What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?
Q C
What are window functions and how do you use them in PostgreSQL?
Q C
What are common table expressions (CTEs) and how do you use them in PostgreSQL?
Q C
What is the difference between `UNION` and `UNION ALL` in PostgreSQL?
Q C
Your database query is slow. How do you optimize it?
Q C
What are indexes and how do they work in PostgreSQL?
Q C
What are transactions and why are they useful in PostgreSQL?
Q C
How do you set up replication in PostgreSQL?
Q C
How do you do a backup and restore of a PostgreSQL database?
Q C
What is the `JSONB` data type and how do you use it in PostgreSQL?
Q C
How do you do full-text search in PostgreSQL?
Q C
What is database normalization and why is it important?
Q C

How do you set up replication in PostgreSQL?

practical Replication Interactive Quiz Code Examples

The Scenario

You are a database administrator at a social media company. You are responsible for a PostgreSQL database that is critical to the business.

You need to set up replication to a secondary database to provide high availability and disaster recovery.

The Challenge

Explain how you would set up replication in PostgreSQL. What are the different types of replication, and what are the trade-offs between them?

Wrong Approach

A junior engineer might not be aware of replication. They might try to solve this problem by just taking periodic backups of the database, which would not provide high availability.

Right Approach

A senior engineer would know that replication is a critical part of database administration. They would be able to explain the different types of replication and would have a clear plan for how to set up replication for a production database.

Step 1: Understand the Different Types of Replication

Replication TypeDescription
Streaming ReplicationThe primary server sends a stream of WAL (Write-Ahead Logging) records to the secondary server. The secondary server then replays the WAL records to keep itself up-to-date.
Logical ReplicationThe primary server sends a stream of logical changes to the secondary server. The secondary server then applies the logical changes to its own data.

For our use case, we will use streaming replication. It is the most common type of replication, and it is well-suited for providing high availability and disaster recovery.

Step 2: Configure the Primary Server

The first step is to configure the primary server. We need to edit the postgresql.conf file and set the following parameters:

wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

We also need to edit the pg_hba.conf file to allow the secondary server to connect to the primary server.

host  replication  all  <secondary_server_ip>/32  md5

Step 3: Create a Base Backup

The next step is to create a base backup of the primary server. We can use the pg_basebackup utility to do this.

pg_basebackup -h <primary_server_ip> -D /var/lib/postgresql/data -U replication -P -v

Step 4: Configure the Secondary Server

The final step is to configure the secondary server. We need to create a recovery.conf file in the data directory with the following content:

standby_mode = 'on'
primary_conninfo = 'host=<primary_server_ip> port=5432 user=replication password=...'

Step 5: Start the Secondary Server

Now we can start the secondary server. It will connect to the primary server and start replaying the WAL records.

Practice Question

You want to replicate only a subset of the tables in your database. Which type of replication would you use?