PostgreSQL’s replication feature allows data to be mirrored from one database (the primary) to another (the replica), ensuring high availability and disaster recovery. One of the key features that PostgreSQL offers to manage streaming replication is replication slots. These slots ensure that WAL (Write-Ahead Logging) data is retained until it has been safely consumed by all replicas, making replication more reliable and robust.
In this blog post, we’ll walk you through the process of setting up physical replication slots in PostgreSQL, from creation on the primary server to configuring the replica to use the slot.
What is a Replication Slot?
A replication slot in PostgreSQL is a mechanism used to track the state of replication, ensuring that the primary database retains all necessary WAL files for replication until they have been consumed by the replicas. Replication slots are important for maintaining synchronization between primary and replica databases in streaming replication setups. By using a replication slot, you can prevent the primary database from recycling WAL segments before the replicas have processed them.
There are two types of replication slots in PostgreSQL:
- Physical Replication Slots: These are used in streaming replication setups, where the replica receives a copy of the primary database’s data.
- Logical Replication Slots: These are used for logical replication, where only specific database changes (e.g., INSERT, UPDATE, DELETE) are replicated.
In this post, we’ll focus on physical replication slots, which are typically used in standard replication setups.
Steps to Configure Replication Slots
1. Create a Replication Slot on the Primary Server
First, we need to create a replication slot on the primary server. This will allow the replica to consume WAL data and stay in sync with the primary.
To create a physical replication slot, log into the primary database and run the following SQL command:
SELECT pg_create_physical_replication_slot('replicationServer1');
In this case, we’ve named our replication slot replicationServer1. You can choose a name that fits your environment. This command creates a slot that will track the WAL data for replication purposes.
2. Configure the Replica Server
Once the replication slot is created on the primary server, we need to configure the replica server to connect to the primary using this replication slot. This is done by editing the recovery.conf file on the replica.
In PostgreSQL 10 and earlier, replication configuration is done in the recovery.conf file. For PostgreSQL 12 and later, configuration moves to postgresql.auto.conf and the presence of a standby.signal file.
On the Replica:
- Open the
recovery.conf(orpostgresql.auto.confin later versions) file on the replica server. - Add the following lines to specify the primary server and the replication slot:
# Set the replication source (primary server)
primary_conninfo = 'host=primary_host_ip port=5432 user=replicator password=your_password'
# Specify the name of the replication slot to use
primary_slot_name = 'replicationServer1'
# Optional: Specify a file to promote the replica in case of failure
trigger_file = '/tmp/postgresql.trigger.5432'
-
primary_conninfo: Contains the connection information for the primary server, such as the host, port, and credentials. -
primary_slot_name: Specifies the replication slot name that the replica will use. In this case, it'sreplicationServer1. -
trigger_file: Specifies a file that, when created, triggers the promotion of the replica to the primary role in case of failover.
3. Restart the Replica Server
After making changes to the recovery.conf file, the replica server needs to be restarted to apply the configuration.
You can restart the replica server using the following command:
sudo systemctl restart postgresql
This will cause the replica to connect to the primary server and start streaming WAL data using the replicationServer1 slot that we configured earlier.
4. Verify the Replication Status
After restarting the replica server, it’s important to verify that everything is working as expected.
On the Primary Server:
You can check the status of the replication and see if the replica is using the slot by running the following query on the primary server:
SELECT * FROM pg_stat_replication;
This will show you details about the replication connections, including the replication slot being used.
On the Replica Server:
You can also check the pg_stat_wal_receiver view on the replica server to verify that it's actively receiving WAL data from the primary:
SELECT * FROM pg_stat_wal_receiver;
This view will show information about the current replication state, including the WAL data being received from the primary server.
Additionally, you can verify that the slot is active on the primary by checking the pg_replication_slots table:
SELECT * FROM pg_replication_slots WHERE slot_name = 'replicationServer1';
5. Troubleshooting
If the replica is not using the slot or if replication is not working as expected, check the logs on both the primary and replica servers for any error messages. Common issues might include network connectivity problems, incorrect credentials, or mismatched slot names.
You can also manually drop and recreate the replication slot if needed:
SELECT pg_drop_replication_slot('replicationServer1');
SELECT pg_create_physical_replication_slot('replicationServer1');
Conclusion
By following these steps, we’ve configured a physical replication slot for streaming replication in PostgreSQL. Replication slots are a powerful tool that helps ensure WAL data is retained until replicas have consumed it, making your replication setup more robust and reliable.
Once set up, you can rest assured that your replicas will stay in sync with the primary server, and the replication slots will help manage the flow of WAL data in a controlled manner.
Let us know if you have any questions or need assistance with PostgreSQL replication!
Let me know if you'd like any changes or additions to this blog post!
Top comments (0)