1. Preparation

Install PostgresSQL version 18 on both servers.

2. Setup

Login to primary server.

2.1. Create the user

Create user which will be used for replication.

Example of user creation
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'your_strong_password';

  • REPLICATION: This is a special permission. It doesn’t need "SELECT" or "INSERT" rights; it just needs the right to stream the WAL logs.

  • LOGIN: Allows the user to actually connect over the network.

2.2. Allow the Connection

PostgreSQL’s "firewall" (pg_hba.conf) blocks all remote connections by default. You must tell it to trust your replica.

Add this line to the end of your pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     replicator      10.222.0.12/32          scram-sha-256 (1)
1 Note: Use scram-sha-256 for the method, as it is the most secure and the default in version 18. After saving this file, run SELECT pg_reload_conf(); in your SQL tool.

2.3. Create replication slot

SELECT pg_create_physical_replication_slot('db_1');

2.3.1. Other operations

Just for sake of completeness. You do not need to execute those commands.

List slots
SELECT slot_name, slot_type, active, wal_status, safe_wal_size, inactive_since FROM pg_replication_slots;
_What to look for

  • active:

    • t (True): Your replica (10.222.0.12) is connected and using this slot.

    • f (False): The replica is disconnected. The Primary is now holding WAL files for it.

  • wal_status:

    • reserved: Everything is fine.

    • extended: The primary is holding more WAL than usual.

  • lost: The replica fell so far behind that the primary was forced to delete the files (usually because you hit max_slot_wal_keep_size).

  • safe_wal_size: Tells you how many bytes can be written before this slot risks being lost.

Delete slot

If a replica is decommissioned, you must delete its slot manually. If you don’t, the primary will keep every WAL file forever, eventually filling up the disk and crashing your database.

To delete a slot, use the function pg_drop_replication_slot('slot_name'):

SELECT pg_drop_replication_slot('db_1');

Important Rule for Deletion:

You cannot delete an active slot. If the replica is still connected, the command will fail with an error:

ERROR: replication slot "{db1-rep-slot-name}" is active for PID XXXX

The correct order to remove a replica permanently:

  1. Stop the PostgreSQL service on the Replica (10.222.0.12).

  2. Run the pg_drop_replication_slot command on the Primary (10.222.0.11).

2.4. Listen on the Network

By default, Postgres only listens on "localhost". Check your postgresql.conf:

listen_addresses = '*' (1)
1 If you change this from 'localhost' to '', you must restart the Postgres service.

2.5. Create the Replica

Install postgreSQL 18. Stop service and delete main database cluster.

Now, on your empty second server, run the pg_basebackup command. This will use the user you just created:

# Run this as the 'postgres' OS user
pg_basebackup -h 10.222.0.11 -U replicator -D /var/lib/postgresql/18/main -v -Fp -Xs -P -R --slot=db_2

What do these flags mean?

  • -h / -U: The hostname of the Primary and the user with replication privileges.

  • -D: The Destination directory on the replica where the data will live.

  • -Fp: Format "plain." This creates a standard data directory exactly like the primary.

  • -Xs: Crucial. This stands for "stream WAL." It opens a second connection to the primary to stream the logs while the backup is happening. This ensures the backup is consistent the moment it finishes.

  • -P: Shows a progress bar (useful for large databases).

  • -R: The Magic Flag. This automatically creates the standby.signal file and writes the primary_config to postgresql.auto.conf. Without this, your replica will just sit there as a standalone server instead of starting to follow the primary.

  • --slot: Tells the primary to use a specific Replication Slot so it doesn’t delete WAL files if the backup takes a long time.

What happens next?

  1. It will ask for the password you set in Step 1.

  2. It will copy the entire database from 10.222.0.11 to 10.222.0.12.

  3. Because of the -R flag, it will automatically create standby.signal and configure the connection details.

  4. Once it finishes, just start Postgres on 10.222.0.12, and it will immediately start following the Primary.

Quick Check

To see if it’s working, run this on the Primary 10.222.0.11:

SELECT * FROM pg_stat_replication;

If you see a row with the IP 10.222.0.12, you are successfully replicating!

3. Operations

3.1. Is replica in sync?

Run following query on primary database.

SELECT pg_current_wal_lsn() AS primary_lsn,
       replay_lsn AS replica_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

If lag_bytes is 0, they are perfectly in sync.

When you run systemctl stop postgresql, it performs a Smart Shutdown. This:

  • Disallows new connections.

  • Waits for existing transactions to finish.

  • Performs a final Checkpoint (flushing everything to disk).

  • Crucially: It sends the final WAL records to the replica.

3.2. How to switch replicas keeping read only mode

We are looking for a Graceful Switchover with Zero Downtime for Reads. In a standard PostgreSQL setup, once you start the shutdown process, the database stops accepting all connections (Read and Write). To achieve your goal of keeping db1 in Read-Only (RO) mode while db2 promotes, you need to change the order of operations. Here is the workflow to transition roles while maintaining Read-Only access on the old primary.

3.2.1. The "Read-Only" Flip (On db1)

Instead of stopping the service, we first put the database into a state where it refuses writes but still allows reads.

Run this on db1:

-- This prevents any new write transactions but stays online for SELECTs
ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

  • Effect: The application’s "Analysis" or "Read" queries continue to work. Any "Write" attempt will now receive an error: cannot execute INSERT in a read-only transaction.

3.2.2. Verify Sync (The "Zero Data Loss" Moment)

Now that db1 is frozen for writes, the Replication LSN (Log Sequence Number) will stop moving. You must ensure db2 has caught up to that final point.

Check on db1
SELECT pg_current_wal_lsn();
-- Let's say it returns '0/3000060'
Check on db2
SELECT pg_last_wal_receive_lsn();
-- Wait until this matches '0/3000060'

3.2.3. Promote the Standby (On db2)

Once the LSNs match, you know 100% of the data is on db2.

Now, promote it.
# On Ubuntu
sudo -u postgres pg_ctlcluster 18 main promote

  • Status: db2 is now your New Primary (RW).

  • JDBC Note: Since your JDBC URL has targetServerType=primary, your application will automatically detect that db2 is now writable and move its "Write" traffic there.

3.2.4. Drain and Maintenance (On db1)

Now that db2 is handling the load, you can safely shut down db1 for your patches. Since it was in read_only mode, you are guaranteed that no data was written there that isn’t already on db2.

sudo systemctl stop postgresql
# Perform Maintenance / Reboot

3.2.5. Re-integrate db1 as the New Replica

After rebooting db1, it is still configured as a "Primary" (though a read-only one). You must now use pg_rewind to point it back to db2.

Bash# On db1
sudo -u postgres pg_rewind \
  --target-pgdata=/var/lib/postgresql/18/main \
  --source-server="host=10.222.0.12 user=postgres dbname=postgres"

# IMPORTANT: Reset the read-only flag so it can act as a normal standby
# (Optional: pg_rewind might overwrite the config, but it's good to check)
# Ensure 'standby.signal' is created and 'postgresql.auto.conf' points to db2.

sudo systemctl start postgresql

3.2.6. Summary of the "No-Downtime-Read" Strategy

Step

Action

db1 State

db2 State

App Experience

1

Set RO on db1

Read-Only

Standby

Writes Fail / Reads OK

2

Sync Check

Read-Only

Standby

Consistent Data

3

Promote db2

Read-Only

Primary (RW)

Full Service

4

Stop db1

Down

Primary

Full Service (via db2)

If your application can handle a few seconds of "Write Errors," this is the safest way to move. The only "Downtime" is the few seconds it takes for the JDBC driver to realize db1 is RO and db2 is now the Primary.

4. Test with docker

4.1. Replication db1 → db2

Create network to interconnect the PostgreSQLs
docker network create pg_net
Run primary database container
docker run -d `
  --name db1 `
  --network pg_ha_net `
  -p 5411:5432 `
  -e POSTGRES_PASSWORD=postgres `
  -v ./db1:/var/lib/postgresql `
  postgres:18-alpine `
  -c listen_addresses='*' `
  -c wal_level=replica `
  -c max_wal_senders=10 `
  -c max_replication_slots=10
Create replication user
docker exec -it db1 psql -U postgres -c "CREATE ROLE replicator WITH REPLICATION PASSWORD 'aaa' LOGIN;"
Create replication slot
docker exec -it db1 psql -U postgres -c "SELECT pg_create_physical_replication_slot('db_2');"
Allow access via docker network
# Add the rule to the HBA file
# Note: I used trust above to get you moving quickly.
# In a production environment, you would use scram-sha-256 and provide a password.
docker exec -it db1 sh -c "echo 'host replication replicator 0.0.0.0/0 trust' >> /var/lib/postgresql/18/docker/pg_hba.conf"

# Reload config
docker exec -it db1 psql -U postgres -c "SELECT pg_reload_conf();"
Create base backup into volume for DB2
docker run --network pg_ha_net --rm --user postgres -v ./db2:/var/lib/postgresql postgres:18-alpine bash -c "pg_basebackup -h db1 -D /var/lib/postgresql/18/docker -U replicator -Fp -Xs -P -R -v --slot db_2"
Run 2nd Postgres
docker run -d `
  --name db2 `
  --network pg_ha_net `
  -p 5412:5432 `
  -e POSTGRES_PASSWORD=postgres `
  -v ./db2:/var/lib/postgresql `
  postgres:18-alpine `
  -c listen_addresses='*'

Now should the replication start.

5. Replication db2 → db1

Configure db2 to be able to replicate back to db1 in case of switch over.

docker exec -it db2 psql -U postgres -c "CREATE ROLE replicator WITH REPLICATION PASSWORD 'aaa' LOGIN;"
docker exec -it db2 psql -U postgres -c "SELECT pg_create_physical_replication_slot('db_1');"
docker exec -it db2 sh -c "echo 'host replication replicator 0.0.0.0/0 trust' >> /var/lib/postgresql/18/docker/pg_hba.conf"
docker exec -it db2 psql -U postgres -c "SELECT pg_reload_conf();"

5.1. Switch over

Stop db1.

❓ How to find out that replica is in sync?

Promote db2 to Primary
docker exec -U postgres -it db2 pg_ctl promote -D /var/lib/postgresql/18/docker

Do maintenance on db1.

make db1 replica of db2
docker run --network pg_ha_net --rm -it --user postgres -v ./db1:/var/lib/postgresql postgres:18-alpine bash -c "rm -rf /var/lib/postgresql/18 && pg_basebackup -h db2 -D /var/lib/postgresql/18/docker -U replicator -Fp -Xs -P -R -v --slot db_1"

Start db1.