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.