How to Set Up Cascading Streaming Replication in PostgreSQL 17

Streaming replication is one of PostgreSQL’s most powerful features, enabling standby servers to continuously replicate data changes from a primary server in near real-time. Cascading replication takes this a step further by allowing a standby server to act as a replication source for another standby, creating a chained, scalable architecture. This setup is ideal for high-availability systems, load balancing, and geographically distributed databases.

In this comprehensive guide, we’ll walk you through the process of setting up cascading streaming replication in PostgreSQL 17 using source-built installations. We’ll configure three servers:

* Primary server running on port 5432.

* First standby server running on port 5433, replicating from the Primary.

* Second standby server running on port 5434, replicating from Standby 1 (not the Primary).

By the end, you’ll have a fully functional replication chain, understand the role of each component, and learn best practices for monitoring and maintaining your setup. Let’s dive in!

What You’ll Achieve

This tutorial will guide you through

* Cloning and building PostgreSQL 17 from source for three independent instances.

* Configuring a Primary server and two Standby servers with cascading replication.

* Verifying the replication setup to ensure data consistency across all servers.

* Understanding the internal components (e.g., walsender, walreceiver, WAL records) and their roles.

Cascading replication reduces the load on the Primary server by distributing replication tasks to intermediate standbys, making it perfect for large-scale systems or environments with multiple read replicas.

Step 1: Download and Build PostgreSQL Source Code

To maximize control and flexibility, we’ll compile PostgreSQL 17 from source. This approach allows you to customize the build, apply patches, or test cutting-edge features.

1.1 Clone the PostgreSQL Repository

Clone the PostgreSQL source code from the official GitHub repository for each instance:

git clone https://git.postgresql.org/git/postgresql.git -b REL_17_STABLE ~/postgres-5432

cp -r ~/postgres-5432 ~/postgres-5433

cp -r ~/postgres-5432 ~/postgres-5434

1.2 Compile and Install Each Instance

Build and install PostgreSQL for the Primary (5432), Standby 1 (5433), and Standby 2 (5434). Each instance will have its own installation directory to avoid conflicts.

Primary Server (5432)

cd ~/postgres-5432

./configure --prefix=$(pwd) --enable-cassert --enable-debug

make 

make install

Standby 1 (5433)

cd ~/postgres-5433

./configure --prefix=$(pwd) --enable-cassert --enable-debug

make 

make install

Standby 2 (5434)

cd ~/postgres-5434

./configure --prefix=$(pwd) --enable-cassert --enable-debug

make 

make install

Configuration Options:

* --prefix: Specifies a unique installation directory (e.g., $HOME/install/5432).

* --enable-debug: Includes debugging symbols for troubleshooting.

Step 2: Initialize the Primary Server (5432)

Each PostgreSQL instance requires a data directory to store its database cluster. We’ll initialize the Primary server first.

bin/initdb primary_data

2.1 Configure postgresql.conf

Edit postgresql.conf of data directory named primary_data to enable replication:

# Network and replication settings

listen_addresses = '*'               # Listen on all interfaces

port = 5432                         # Primary server port

wal_level = replica                 # Enable replication

max_wal_senders = 10                # Support up to 10 replication connections

wal_keep_size = 512MB               # Retain 512MB of WAL files

archive_mode = on                   # Enable WAL archiving

archive_command = 'cp %p $HOME/archive/%f'  # Archive WAL files

hot_standby = on                    # Allow read-only queries on standbys

Key Settings:

* listen_addresses = '*': Allows connections from any IP (restricted to specific IPs in production).

* wal_level = replica: Ensures WAL contains enough data for replication.

* max_wal_senders: Supports multiple standby connections.

* wal_keep_size: Retains WAL files to prevent standbys from falling behind.

* archive_command: Copies WAL files to an archive directory for PITR (create $HOME/archive first).

* hot_standby: Enables read-only queries on standbys (useful for load balancing).

2.2 Configure pg_hba.conf

Edit pg_hba.conf of data directory named primary_data to allow replication connections:

# TYPE  DATABASE  USER  ADDRESS       METHOD

host  replication  all   127.0.0.1/32  trust

host replication: Permits replication connections.

127.0.0.1/32: Limits connections to localhost (modify for remote standbys).

trust: Allows passwordless connections (use scram-sha-256 in production).

2.3 Start the Primary Server

cd postgres-5432/postgres

bin/pg_ctl -D primary_data -o ‘-p 5432’ -l logfile start

Verify the server is running:

bin/psql -p 5432 -U cybrosys -c "SELECT pg_is_in_recovery();"

Output should show f (false), confirming the server is the Primary.

Step 3: Create the First Standby (5433)

Standby 1 will replicate directly from the Primary using streaming replication.

3.1 Take a Base Backup

Use pg_basebackup to clone the Primary’s data directory

cd postgres-5433/postgres

bin/pg_basebackup -h 127.0.0.1 -p 5432 -D /home/postgres-5433/postgres/standby_1 -U cybrosys -Fp -Xs -P -R

* -h and -p: Specify the Primary’s host and port.

* -D: Target data directory.

* -U cybrosys: Superuser for authentication.

* -Fp: Uses plain format for the backup.

* -Xs: Includes WAL streaming during backup.

* -P: Shows progress.

-R: Creates a standby.signal file and configures primary_conninfo.

3.2 Configure postgresql.conf

Edit $HOME/postgres-5433/postgres/standby_1/postgresql.conf:

port = 5433

hot_standby = on

primary_conninfo = 'host=127.0.0.1 port=5432 user=cybrosys application_name=standby1'

wal_level = replica

max_wal_senders = 10

* primary_conninfo: Specifies the Primary as the upstream server.

* application_name: Uniquely identifies Standby 1 in replication monitoring.

* wal_level and max_wal_senders: Enable Standby 1 to act as a source for Standby 2.

3.3 Start Standby 1

cd home/postgres-5433/postgres

bin/pg_ctl -D standby_1 -o ‘-p 5433’ -l logfile start

Verify replication on the Primary:

bin/psql -p 5432 -U cybrosys -c "SELECT * FROM pg_stat_replication;"

Look for standby1 in the application_name column with a state of streaming.

* Standby 1 connects to the Primary (port 5432) via the walreceiver process.

* The walsender process on the Primary streams WAL records to Standby 1.

* The primary_conninfo setting ensures Standby 1 knows where to fetch WAL data.

Step 4: Create the Second Standby (5434)

Standby 2 will replicate from Standby 1, demonstrating cascading replication.

4.1 Take a Base Backup

Clone Standby 1’s data directory:

bin/pg_basebackup -h 127.0.0.1 -p 5433 -D home/postgres-5434/postgres/standby_2 -U cybrosys -Fp -Xs -P -R

4.2 Configure postgresql.conf

Edit home/postgres-5434/standby_2/postgresql.conf:

port = 5434

hot_standby = on

primary_conninfo = 'host=127.0.0.1 port=5433 user=cybrosys application_name=standby2'

4.3 Start Standby 2

bin/pg_ctl -D /home/postgres-5434/postgres -o ‘-p 5434’ -l logfile start

Verify replication on Standby 1:

bin/psql -p 5433 -U cybrosys -c "SELECT * FROM pg_stat_replication;"

Look for standby2 in the output.

* Standby 2 streams WAL records from Standby 1 (port 5433), not the Primary.

* This chained setup reduces the Primary’s load, as Standby 1 handles replication to Standby 2.

* The walsender process on Standby 1 and the walreceiver process on Standby 2 manage this connection.

Step 5: Verify Replication

To ensure the replication chain is working, perform the following checks:

On the Primary (5432)

After setting up the servers, it's crucial to ensure that the replication chain is functioning correctly.

On the Primary (5432) check the replication status

bin/psql -p 5432 -U cybrosys -c "SELECT * FROM pg_stat_replication;"

This will show details of the standby (5433) connected to the primary.

On Standby 1 (5433) check the replication status and WAL receiver status

bin/psql -p 5433 -U cybrosys -c "SELECT * FROM pg_stat_replication;"

bin/psql -p 5433 -U cybrosys -c "SELECT * FROM pg_stat_wal_receiver;"

* - `pg_stat_replication` shows standby 2 (5434) connected to standby 1.

* - `pg_stat_wal_receiver` confirms that standby 1 is receiving WALs from the primary.

On Standby 2 (5434) Check the WAL receiver status

bin/psql -p 5434 -U cybrosys -c "SELECT * FROM pg_stat_wal_receiver;"

This ensures that standby 2 is successfully receiving WALs from standby 1.

Why Cascading Replication?

Cascading replication offers several benefits:

* Load Balancing: Standby 1 handles replication to Standby 2, reducing the Primary’s walsender load.

* Network Efficiency: In geographically distributed setups, intermediate standbys can serve as regional hubs, minimizing latency.

* Scalability: You can extend the replication chain by adding more standbys without overloading the Primary.

* Read Scalability: Standbys can serve read-only queries, distributing query load across multiple servers.

Conclusion

Cascading streaming replication in PostgreSQL 17 provides an efficient and scalable solution for building fault-tolerant database architectures. By allowing standby servers to serve as replication sources for other standbys, the replication load on the primary server can be significantly reduced, improving overall system performance and availability.

This setup enables better disaster recovery, minimizes downtime, and supports geographical distribution by strategically placing standby nodes closer to users. Understanding the roles of processes like walsender and walreceiver, and how WAL data flows between servers, is essential for maintaining a healthy replication environment.

Proper monitoring, timely backups, and tuning of replication parameters are critical to ensuring a resilient and responsive PostgreSQL system that can handle growth and unexpected failures effectively.

whatsapp_icon
location

Calicut

Cybrosys Technologies Pvt. Ltd.
Neospace, Kinfra Techno Park
Kakkancherry, Calicut
Kerala, India - 673635

location

Kochi

Cybrosys Technologies Pvt. Ltd.
1st Floor, Thapasya Building,
Infopark, Kakkanad,
Kochi, India - 682030.

location

Bangalore

Cybrosys Techno Solutions
The Estate, 8th Floor,
Dickenson Road,
Bangalore, India - 560042

Send Us A Message