Step-by-Step: Sanity Check for Cascading Replication in PostgreSQL

Cascading replication allows PostgreSQL replicas to stream data not just from the primary, but from other replicas as well. This setup is ideal for scaling read operations and distributing replication load.

In this guide, we’ll walk through a simple sanity check to ensure your 4-node cascading replication setup is working as expected.

🧩 Assumptions
We’re working with a 4-node setup:

Node 1 is the Primary
Node 2 replicates from Node 1
Node 3 replicates from Node 2
Node 4 replicates from Node 3

Replication is already configured and running, and you have access to psql on all nodes.

Step 1: Create reptest Database on Primary (Node 1)

Open a terminal and connect to PostgreSQL:

psql -U postgres

Create the database and switch to it:

sql

CREATE DATABASE reptest;

\c reptest

Step 2: Create a Sample Table

Inside the reptest database, create a simple table:

sql

CREATE TABLE sanity_check (id SERIAL PRIMARY KEY,message TEXT,created_at TIMESTAMP DEFAULT now());

Step 3: Insert Sample Data

Add a few rows to test replication:

sql

INSERT INTO sanity_check (message) VALUES ('Hello from Node 1'), ('Replication test'), ('Cascading is cool!');

Step 4: Wait a Few Seconds

Give replication a moment to propagate. Cascading replication may introduce slight delays, especially for downstream nodes like Node 4.

Step 5: Verify Data on Nodes 2, 3, and 4

On each replica node, connect to the reptest database:
psql -U postgres -d reptest

Then run:
sql
SELECT * FROM sanity_check;

You should see the same rows that were inserted on the primary.

✅ Conclusion

If the data appears correctly on all replicas, your cascading replication setup is working as expected. This simple sanity check can be repeated periodically or automated to ensure ongoing replication health.