What is streaming replication, and how can I set it up?

In PostgreSQL, there are two types of replication features: streaming replication (physical replication) that collectively replicates a database cluster, and logical replication that replicates in units of tables and databases.

In this article, we focus on the mechanism and configuration of streaming replication that replicates a database cluster in batch.

What is streaming replication?

Streaming replication, a standard feature of PostgreSQL, allows the updated information on the primary server to be transferred to the standby server in real time, so that the databases of the primary server and standby server can be kept in sync. The streaming replication features below bring great benefits to your system.

• Failover: When the primary server fails, the standby server can take over the operation.
• Read-only load balancing: Read-only SQL processing can be distributed among multiple servers.

Failover

Read-only load balancing

You can process read-only SQL on the standby server to distribute read-only workload. However, PostgreSQL does not have a distribution feature that considers queries and server load. By using the load balancing feature of pgpool-II, an open source extension, read-only queries can be efficiently distributed between the PostgreSQL instance, and workload can be balanced.

Streaming replication mechanism

To understand how streaming replication works, let's take a closer look at what to transfer and how to transfer it.

What is shipped

PostgreSQL saves the updated information of the primary server as a transaction log known as write-ahead log, or WAL, in preparation for crash recovery or rollback. Streaming replication works by transferring, or shipping, the WAL to the standby server in real time, and applying it on the standby server.

WAL transfer

How the WAL is shipped and applied

WAL shipping between the primary server and the standby server is performed by the WAL sender process on the primary server to the WAL receiver process on the standby server. These processes are started by setting postgresql.conf and pg_hba.conf parameters – we will discuss them later in this article.

WAL sender/receiver process

Setup

This section describes the setups that can be configured with streaming replication, and extends the discussion to synchronous and asynchronous replications.

Multi-standby setup and cascade setup

Streaming replication can be built in a 1:N configuration, where only one primary server is configurable, but multiple standby servers can be set up. The configuration that connects (ships WAL from) a primary server to all standby servers is called a multi-standby setup. You can also build a cascade setup where a standby server connects (ships WAL) to another standby server.

Multi-standby setup

Cascade setup

Synchronous replication and asynchronous replication

For streaming replication, you can select either synchronous or asynchronous replication for each standby server. This section describes the characteristics of each, and key points for configuration.

Characteristics of synchronous and asynchronous replications

The difference between synchronous replication and asynchronous replication is whether or not to wait for the response from the standby server before completing the processing on the primary server. Choose the configuration according to your operation, since this affects the response time of SQL processing and high availability.

Synchronous/asynchronous setup (synchronous_standby_names)

Synchronous setup is configured by synchronous_standby_names in postgresql.conf on the primary server. If there are multiple standby servers, you can specify the servers to be synchronized and the order of priority for COMMIT. Standby servers not specified in this parameter will be asynchronous.

Example: 'synchronous_standby_names = FIRST 2 (s1, s2, s3)'

You can also specify the method by which to select synchronous standby servers from a list of servers, such as  'FIRST n (list)' or  'ANY n (list)'. The FIRST keyword specifies that the n first standby servers in list will be synchronous and that transaction commits will wait until their WAL records are replicated to the first n standby servers in list. The ANY keyword specifies a quorum-based synchronous replication and that transaction commits should wait until their WAL records are replicated to at least n standby servers in list.

For example, in an environment using standby servers s1, s2, s3, and s4, setting synchronous_standby_names to  'FIRST 2 (s1, s2, s3') will configure s1 and s2 for synchronous replication, and s3 and s4 for asynchronous replication. The primary server will wait s1 and s2 to complete processing before it commits. If s1 or s2 fails, then s3 changes to synchronous replication.

Setup as FIRST 2 (s1 ,s2, s3)

Same setup, after failure of s1

Make sure that the name set in synchronous_standby_names matches the name set in application_name of primary_conninfo in postgresql.conf on the standby server. This parameter is discussed later in this article.

Setting the synchronization level

To set the synchronization level of the standby server, set synchronous_commit in postgresql.conf on the primary server. This section describes the values that can be set and their overview. The circled numbers in the 'Guaranteed range' column of the table match the numbers in the diagram directly below the table.

Synchronization level

Configuring the primary and standby servers

We list below the parameters required to use the streaming replication feature, setting up synchronous replication and archiving the WAL.

Settings on the primary server

Set the values below in postgresql.conf and pg_hba.conf on the primary server.

Settings on the standby server

Set the values below in postgresql.conf on the secondary server.

In case the primary server fails, a standby server will be promoted. For this reason, we recommend setting common parameters on the primary and standby servers in advance.