PostgreSQL provides infrastructure to stream the modifications performed via SQL to external consumers. This functionality can be used for a variety of purposes, including replication solutions and auditing. Changes are sent out in streams identified by logical replication slots. Each stream outputs. PostgreSQL streaming replication with slots works for one slave, but not for another slave. Streaming Replication in PostgreSQL. PostgreSQL streaming versus file-based replication (In terms of server behavior & configuration). Postgres 9.4, replication slots, doesn't work failover master.
On this page
- Testing
PostgreSQL is a powerful and feature-rich relational database management system (RDBMS). It is free and open-source, and has been in development since 1996. Postgres offers different ways of archiving and replicating data, one of which is streaming replication. In this mode, a primary (master) instance handles the main active database and executes operations. The secondary (slave) instance copies all changes from the primary, maintaining an identical copy of the active database. The secondary server can also accept read-only queries. If the primary fails, the secondary server can exit standby mode and operate as the new master (this is called failover).
PostgreSQL replication usually relies on write-ahead logging (WAL), the process of logging data changes before writing them to disk. These WAL records are then either copied to a second node as files (file-based log shipping), or directly streamed between nodes (streaming replication). In most cases, the latter reduces the delay for changes on the master node to be received by the standby node.
The problem with using streaming replication without file-based log shipping is that the secondary server may miss some WAL records if the primary discards them too soon. A number of configuration parameters can reduce this risk but often come with an unnecessary storage cost. The solution is replication slots, a feature provided by Postgres that ensures the primary server only discards WAL records after they have been received by the standby node.
We will be setting up streaming replication with replication slots on two Debian 10 nodes.
Requirements
- Two identical Debian 10 instances.
- Root access to both instances.
- The $EDITOR environment variable should be set on both instances.
Step 1: Installing PostgreSQL
Update and reboot both nodes:
Install Postgres on both nodes and make sure PostgreSQL is enabled and running:
NOTE: When updating PostgreSQL, updating the standby first is the safer option according to their documentation.
Step 2: Initial Configuration
By default, PostgreSQL only listens on the loopback interface and is not externally accessible. Change the listen address on both nodes by editing postgresql.conf:
Find the following line:
Change it to:
If both nodes share the same local network, you can use private addresses for node_ip_address, though Postgres won't be internet accessible. Otherwise, use public addresses.
Save the change then restart both instances:
Step 3: Master Configuration
This step only pertains to the primary/master server.
Open the Postgres terminal:
The standby node will be using a user to connect to the master. Create it:
Then create a replication slot and exit:
For the sake of simplicity, the replication role and slot are both named 'replicator', though they do not have to be identical.
Next, create an entry in pg_hba.conf to allow the replicator user to connect from standby to master. Open it:
Append the following line to the end:
Restart the master instance:
Step 4: Base backup
The commands in this step should be executed on the secondary/slave server.
First, stop Postgres on the secondary node:
Backup the old data directory:
Use the following command to clone the master's data directory to the slave:
You will be prompted for a password. Enter the password you chose for the replicator role during its creation on the master. Once the transfer is complete, grant ownership of the data directory to the postgres user:
Step 5: Standby Configuration
This step only pertains to the secondary/slave server.
Enable hot standby mode in postgresql.conf:
Find and uncomment the following line:
Create the file recovery.conf in the Postgres data directory:
Enable standby mode:
Set the replication connection parameters using the credentials created on the master:
Set the name of the replication slot you've created on the master:
Set the path to a failover trigger file:
If the trigger_file parameter is set, Postgres will exit standby mode and start normal operation as a primary server when this trigger file is created. This parameter is not required.
After creating recovery.conf, grant ownership to the postgres user:
You can now start Postgres:
Postgresql 10 Stream Replication
It is now in standby mode and should be replicating any new transaction.
Testing
Testing Replication
To test replication, perform any write action on the master. For example, create a new database on the master:
Wait a few seconds then list the databases on the slave:
You should see that the replitest database was indeed replicated by the standby server:
Testing Failover
NOTE: Testing failover as shown here will require resetting the standby server after failover.
Since Postgres is in standby mode, you should not be able to perform any write operation on the secondary node before failover. For example, execute the following command:
The command should fail:
To signal failover, create the trigger file specified in recovery.conf
Postgresql Streaming Replication Setup
Wait a few seconds, then try performing a write operation. For example:
Since Postgres is no longer operating as a standby, the operation will succeed. Postgres will also rename your recovery.conf file to recovery.done, and will delete the trigger file.
To go back to standby, stop Postgres on the (former) secondary node:
Reset the data directory:
Postgresql Streaming Replication Monitoring
And recreate recovery.conf:
Finally, restart Postgres:
The secondary instance is now back to standby mode. You may want to re-test replication at this point.
Finishing up
Postgresql Replication Slot
Remove any unnecessary databases on the master node, for example:
And delete the old data directories on your standby node: