Introduction
In this article, we look at how to configure the various replication methods described earlier.
We set up environments using the most commonly discussed methods: log shipping, streaming, and logical replication. Issues that are not directly related or that may come up along the way are covered in a separate article,
PostgreSQL Replication - Trouble Shooting.The environment for the setup is as follows. Replication slot settings are not included.
Case | Replication method | Main Server (10.10.45.230) | Standby Server (10.10.45.231) |
1 | Log Shipping | CentOS 7.9 / PostgreSQL 14 | CentOS 7.9 / PostgreSQL 14 |
2 | Streaming | CentOS 7.9 / PostgreSQL 14 | CentOS 7.9 / PostgreSQL 14 |
3 | Logical | CentOS 7.9 / PostgreSQL 14 | CentOS 7.9 / PostgreSQL 14 |
📢 This article is based on PostgreSQL 14.
The configuration file layout changed in PostgreSQL 12. With version 14 we use the postgresql.conf file, but if you are on a version earlier than 12, you must also use the recovery.conf file. For details, see PostgreSQL Replication - Trouble Shooting.
📢 Consistent terminology
Several terms are used interchangeably when describing PostgreSQL replication. This article uses the terms below.
- Main Server
: production server, Primary Server, Base Server, Master Server, Active Server
- Standby Server
: replica server, Secondary Server, Replication Server, Slave Server
Case 1. Configuring File-based Log Shipping Replication
Step 1. [Main Server] Generate an SSH key and register it on the Standby Server
To send WAL files from the Main Server to the Standby Server via SCP, generate an SSH key on the Main Server so that no password is asked when WAL files are transferred.
📢 For ssh-keygen settings, see
PostgreSQL Replication - Trouble Shooting.Step 2. [Main Server] Configure postgresql.conf
Step 3. [Main Server] Restart the PostgreSQL server
Step 4. [Standby Server] Stop the PostgreSQL server
Step 5. [Standby Server] Delete the PostgreSQL data directory
If
initdb was not run during the PostgreSQL installation, as on Red Hat-family Linux, you can skip deleting the data directory. On Debian-family systems (e.g. Ubuntu), however, initdb runs automatically during installation, so you must delete the PostgreSQL data directory.📢 The default data directory on Ubuntu is
/var/lib/postgresql/{MAJOR_VERSION}/main.Step 6. [Standby Server] Copy the Main Server data directory
Copy the Main Server's data directory to the Standby Server. You can also copy it with the
pg_basebackup application, but that is covered in the streaming replication section, so in this case we copy it with OS commands.Method 1) Copy the data directory with the Main Server stopped (cold backup)
Compress the data directory used by the Main Server and send it to the Standby Server. Compression and transfer time vary with the database size. The database must be stopped when you compress the data directory.
Method 2) Copy the data directory with the Main Server running (hot backup)
To copy the data directory while the Main Server is running, use the
pg_start_backup and pg_stop_backup functions. The backup_label file created when pg_start_backup runs tells the Standby Server the point from which to recover using WAL files.To fully apply the changes made between
pg_start_backup and pg_stop_backup to the Standby Server, be careful not to lose any WAL files. In this case, the archive_command set in Step 2. sends the WAL files generated on the Main Server to the Standby Server, so no WAL files are lost.📢 For details on backing up the data directory online, see
PostgreSQL Replication - Trouble Shooting.Step 7. [Standby Server] Create the signal file
After the data directory is copied, create a standby.signal file in the PostgreSQL data directory so the server runs as a Standby Server.
Step 8. [Standby Server] Configure postgresql.conf
Set additional parameters to restore the WAL files received from the Main Server. Depending on the PostgreSQL version, the parameters go in recovery.conf or postgresql.conf.
📢 For the recovery.conf parameters, see
PostgreSQL Replication - Trouble Shooting.postgreSQL 11 and earlier (recovery.conf)
PostgreSQL 12 and later (postgresql.conf)
Step 9. [Standby Server] Create the archive directory
Create a directory to store the WAL files received from the Main Server.
Step 10. [Standby Server] Start the PostgreSQL server
Case 2. Configuring Streaming Replication
Step 1. [Main Server] Edit postgresql.conf
Step 2. [Main Server] Create a replication user
📢 A role (user) created with CREATE ROLE gets the Cannot login option, so either create it with "CREATE ROLE username REPLICATION LOGIN" or grant the LOGIN privilege after creating the role. For details, see
PostgreSQL Replication - Trouble Shooting.Step 3. [Main Server] Edit pg_hba.conf
📢 Streaming replication cannot be set up unless you edit pg_hba.conf. For details, see
PostgreSQL Replication - Trouble Shooting.Step 4. [Main Server] Restart the PostgreSQL server
Step 5. [Standby Server] Stop the PostgreSQL server
Step 6. [Standby Server] Delete the PostgreSQL data directory
If
initdb was not run during the PostgreSQL installation, as on Red Hat-family Linux, you can skip deleting the data directory. On Debian-family systems (e.g. Ubuntu), however, initdb runs automatically during installation, so you must delete the PostgreSQL data directory.📢 The default data directory on Ubuntu is
/var/lib/postgresql/{MAJOR_VERSION}/main.Step 7. [Standby Server] Copy the Main Server data directory
Copy the Main Server's data directory. This time, instead of the OS commands used in Case 1, use the
pg_basebackup application.With the
-R option, the primary_conninfo parameter is written to postgresql.auto.conf and the standby.signal file is created automatically.Option | Description |
-h (--host) | Main Server IP |
-U (--username) | Replication DB User |
--checkpoint=fast | Perform a fast checkpoint |
-D (--pgdata) | Path to use as the data directory on the Standby Server |
-R (--write-recovery-conf) | Configure the Standby Server automatically (creates the standby.signal file) |
-S (--slot) | Name of the slot to use for replication |
Step 8. [Standby Server] Start the PostgreSQL server
📢 You can also combine log shipping (Case 1) and streaming (Case 2) replication.
Streaming makes up for the data lag of log shipping replication, and log shipping makes up for streaming replication stopping when WAL files have been deleted and can no longer be streamed. The two complement each other.
CASE 3. Configuring Logical Replication
Step 1. [Main Server] Configure postgresql.conf
📢 Setting
wal_level to logical adds the information needed for logical decoding on top of what the Replica level collects. In other words, the logical setting generates many more WAL files, so check your free disk space.Step 2. [Main Server] Create a dedicated DB user for replication
Step 3. [Main Server] Configure pg_hba.conf
Step 4. [Main Server] Restart PostgreSQL
Step 5. Configure the publication and subscription
With the settings on the Main Server so far, the preparation for logical replication is complete. Now the publisher (Main) creates a publication for the replication targets, and the subscriber (Standby) creates a subscription to set up replication. Each server must follow the steps below; let's set up replication with an example.
Main Server (Publisher, Publication) | Standby Server (Subscriber, Subscription) |
1. Select replication targets | 1. Create the replication target table |
2. Create the publication | 2. Create the subscription |
3. Grant privileges to the DB user |
[Main Server] Select replication targets
You can replicate one or more tables. You can select existing tables or newly created ones. In the example, we create a new table as the replication target.
[Main Server] Create the publication
Create a publication that includes the tables selected for replication. When creating the publication, you can limit replication to specific operations among INSERT, UPDATE, DELETE, and TRUNCATE. In the example, replication runs for all 4 cases above.
📢 Example of a publication that includes only INSERT
[Main Server] Grant privileges to the DB user
[Standby Server] Create the replication target table
Logical replication does not replicate DDL, so you must create the Main Server's replication target table in advance. The table can define more columns than the publication, but the replicated columns must have the same types and names. In the example, we defined an extra string2 column.
📢 You can create the table structure on the Standby Server with pg_dump. The indexes on the table are created as well.
[Standby Server] Create the subscription
📢 If you did not create a replication slot separately, creating the subscription creates a replication slot named after the subscription on the Main Server. The default output plugin is pgoutput.
Related Articles
