How to Setup PostgreSQL Streaming Replication with Replication Slots on Debian 10
October 27, 2024 ,

How to Setup PostgreSQL Streaming Replication with Replication Slots on Debian 10

PostgreSQL is a powerful and feature-rich relational database management system (RDBMS) that supports various types of replication, including streaming replication. This article will guide you through the process of setting up streaming replication with replication slots on Debian 10.

Prerequisites

Before starting, make sure you have a Debian 10 system installed with PostgreSQL version 12 or higher. You should also have a basic understanding of PostgreSQL and its configuration files.

Step 1: Create a Replication Slot

To set up streaming replication, you need to create a replication slot on the master node (the node that will be replicating data). To do this, run the following command:

sudo -u postgres psql -c "CREATE REPLICA SLOT my_replica_slot"

Replace my_replica_slot with the name of your replication slot.

Step 2: Configure the Master Node

In the PostgreSQL configuration file (postgresql.conf), set the following parameters:

  • wal_level: Set to logical.
  • max_wal_senders: Set to a value that is at least equal to the number of concurrent connections you expect.
  • hot_standby: Set to on.

Example:

listen_addresses = 'localhost'
port = 5432
wal_level = logical
max_wal_senders = 5
hot_standby = on

Step 3: Configure the Standby Node

On the standby node, create a file called recovery.conf in the PostgreSQL data directory. This file contains configuration settings for the standby node.

In this file, set the following parameters:

  • standby_mode: Set to on.
  • primary_conninfo: Set to the connection details of the master node (e.g., host=master_ip_address port=5432 user=replicator password=replicator_password).
  • primary_slot_name: Set to the name of the replication slot created on the master node.

Example:

standby_mode = 'on'
primary_conninfo = 'host=master_ip_address port=5432 user=replicator password=replicator_password'
primary_slot_name = 'my_replica_slot'

Step 4: Start Replication

Start the standby node and enable hot standby mode. This will initiate the replication process.

To test replication, perform a write operation on the master node (e.g., create a new database) and wait for a few seconds. Then, list the databases on the standby node to verify that the data has been replicated.

Step 5: Test Failover

To test failover, create a trigger file on the standby node to signal that it should take over as the primary node. For example:

touch /var/lib/postgresql/11/main/failover.trigger

Wait for a few seconds, then try performing a write operation on the standby node. Since it is now operating as the primary node, the operation will succeed.

To go back to standby mode, stop Postgres on the former standby node, reset the data directory, and re-run pg_basebackup to re-sync with the new master node.

###In this article, we walked through the process of setting up PostgreSQL streaming replication with replication slots on Debian 10. This feature allows for high availability and disaster recovery, as well as seamless failover between nodes.

References

Tags:
Share this