Streaming Replication in PostgreSQL: Overview
In PostgreSQL, streaming replication is based on log shipping. The master server writes every transaction to a write-ahead log (WAL) to achieve durability. The slave servers use these WAL to replicate changes from their master.
This feature, added to PostgreSQL 9.0, allows the database to continuously ship and apply the WAL records to standby servers to keep them updated.
How Streaming Replication Works
At a high level, Postgres streams records of database modifications from the primary server to one or more replicas. You can use these replicas as read-only, to scale queries; or failovers for high availability (HA).
The base for streaming replication is the WAL, an append-only series of instructions that captures every transaction. Using a WAL is a common approach to achieve durability, which means that the resulting data from a transaction remains queryable even in the case of a server crash.
Postgres achieves this by making changes to a queryable state and writing them to WAL concurrently. In the event of a server crash, the WAL serves as the record of all database changes. Therefore, you can replay from the WAL the changes in memory but not yet written to the main data directory.
However, this approach doesn’t help in the case of an unrecoverable issue, such as the physical loss of a server or disk corruption. This is where the clever streaming replication approach comes in. PostgreSQL uses the same WAL, shipping it to other servers, which replay the WAL as if restarting a server, thus replicating the database on a different server.
Streaming Replication in PostgreSQL (with Local Storage)
In this approach, you have a primary server that contains all the tables data and write-ahead logs. The WALs are streamed to a secondary server. You can use local disks or if working on the cloud, attach persistent volumes to your instances.
However, the issue with this approach lies in creating a new secondary server. When you build a secondary server, it replays the entire state from the primary node, since the primary server’s creation, therefore increasing significantly the load on the primary server.
Replicated Block Device
This approach relies on volume replication, that means, the system writes the changes to a persistent volume. The volume then is mirrored to another volume. The advantage is that it works for all relational databases.
Keep in mind, though, that the disk mirroring approach requires replicating both the table and WAL log data, synchronizing each write to the database over the network.
Reconstruct From WAL (and Switch to Streaming Replication)
In this third and final approach, you write to the primary node which in turn does a full database backup every day, in addition to incremental backups every 60 seconds.
Therefore, when you need to build a new secondary server, it replay the entire state from the backups. This approach has the advantage that doesn’t load the primary database with extra weight. For example, you can reconstruct new secondary servers from S3.
Setup Streaming Replication with PostgreSQL
Using master/slave database replication, a database copies data from a master server to the slave’s server, allowing for distributing databases to multiple machines. This has the benefit of having a backup machine if the master server goes down. Streaming replication, goes a step ahead, continuously shipping and applying the WAL records to standby servers. The standby servers (the replicas) apply the WAL changes, making them available for queries.
How do you set up streaming replication with PostgreSQL?
First, you need to have Postgres installed in the primary and standby server, as usual, creating a master-server relation. Then you need to edit the PostgreSQL configuration file to allow for the hot standby replication. You can find a step-by-step tutorial here.
The steps to configure the Master Server include:
- Initialize the database on the primary server using the initdb utility.
- Create a role/user with replication privileges using this command:
CREATE USER <user_name> REPLICATION LOGIN ENCRYPTED PASSWORD ’<password>’;
You can verify the command by running du to list them on psql.
- Configure the properties related to streaming replication in the master PostgreSQL configuration file.
- Add the replication entry in the master to allow replication connections between the servers.
- Restart the PostgreSQL service on the master server to allow the changes to apply.
Afterward, you should configure the standby servers. The steps required to do it include:
- Create the Base Backup on the master server using pg_basebackup utility, so you can use it as a starting point for the standby.
- Create the Replication Configuration File—you can have it created automatically by providing an R option in the step before.
- Start the Standby to connect to the master and start streaming logs. You should repeat this configuration on all the standby servers.
You can verify the replication by running SELECT * FROM pg_stat_replication;.
The database sets the streaming replication model as asynchronous by default. If you wish to make it synchronous, you should set synchronous_commit for synchronous replication. On the other hand, if you set synchronous_commit to off or local, the replication mode will make it work like asynchronous.
Enabling archive mode is useful when planning to use point-in-time recovery. Otherwise, you should use the replication slots feature.
Use Cases and Challenges of Postgres Streaming Replication (SR)
Streaming replication provides a safety net, in terms of achieving High Availability (HA), during disasters and unexpected failures. Use cases include:
- Analytics—providing a read-only replica to lighten the load off the primary server.
- The need for HA—for example, in virtual environments or geographically distributed servers.
This approach also comes with its challenges. Some of them include:
- No built-in monitoring and failover—if the primary server fails you need to promote a secondary to be the new primary.
- Different clients talking to a single endpoint—in different languages, will keep retrying the same IP when the primary server fails, making the failover visible.
- Entire state replication—when you need to build a new secondary server, it needs to replay the entire state change history from the primary. While exhaustive, this process is expensive and resource intensive.
To overcome these challenges, companies are using third-party solutions for postgres replication in single-master or multi-master modes.This has the added benefit to allow for replication between geographically distributed servers.
Final Thoughts
Using streaming replication with PostgreSQL can help you achieve high availability while keeping your data secure by replicating it in several databases. Postgres even gives you the flexibility to use the replication approach that best fits you.
The three above-mentioned approaches to replication each have their advantages and setbacks. Simply streaming replication using a local disk enables performance with high throughput and large storage; the replicated block device works with all relational databases and helps attain durability in cloud environments; while the WAL-based approach has the benefit of reconstructing the servers in the background. Whichever you choose will depend on your organization’s needs and requirements.