Postgres Replication Status

Mindwatering Incorporated

Author: Tripp W Black

Created: 01/26 at 12:03 PM



Verify postgres cluster primary to replica replication status.

1. Login/SSH to the postgres node
2. Switch user to the postgres user
3. Query using the following selections:
On master node:
postgres=# select * from pg_stat_replication;
postgres=# \q

or with subset of columns:
postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication;
postgres=# \q

On replica node:
postgres=# select * from pg_stat_wal_receiver;
postgres=# \q

If there are 0 rows, replication is not set-up/occurring.
Replication has three types: streaming, volume-level replication, and incremental backups.
Example query results on primary node table should show: state as streaming and sync_state as async.

To start over replication:

1. On the primary node, create replication user
If the replication user does not exist, create it with:
postgres=# CREATE USER 'replication_username' REPLICATION LOGIN ENCRYPTED PASSWORD 'myrepaccountpwd';
postgres=# \q

2. On the primary node, update the main postgresql.conf with replication settings
Update the /etc/postgresql/version/main/postgresql.conf with new replication settings:
$ sudo vi /etc/postgresql/version/main/postgresql.conf
a. Under the "Connection Settings" section, verify the listen_addresses, which is localhost by default. We need it to listen on its IP. Update if needed.
listen_addresses =

b. Under the "Write-Ahead Log" section, uncomment/add the following lines, update for your preferred backup method and load/bandwidth
max_wal_senders = 10
wal_level = logical
wal_log_hints = on
max_wal_senders = 8
hot_standby = on
<esc>:wq (to save)

Use wal_level = logical for object replication and replica for block level replication
You can confirm a location of a config file with:
$ sudo -u postgres psql -c 'SHOW configfilename'

3. On the primary node, update the pg_hba.conf file, typically in the data folder, add a host entry to allow/trust the secondary(s) to replicate.
For example, if you have two secondary nodes on neighboring subnets, you would append the following two lines at the end of the file:
$ sudo vi /etc/main/postgresql/version/main/pg_hba.conf

host replication all trust
host replication all trust
<esc>:wq (to save)

4. On the replica node(s), we need to wipe the existing data, and setup the replica to receive the database from the primary node.

You may need to open the firewall port, 5432 if not already open. If it is not open, create a rich-text rule allowing the secondary/replica hosts to connect.

a. Stop the service:
$ sudo systemctl stop postgresql

b. Remove the existing data, and re-apply permissions as needed:
$ sudo rm -rv /var/lib/posgresql/version/main/*
$ sudo -u postgres chmod 700 /var/lib/postgresql/version/main

c. Check for the slot from the replication slot table
$ sudo psql -c "SELECT * FROM pg_drop_replication_slots;"
<view output. if the slot name is already there, we cannot use the same name again.>

If the slot name is still there and active, we need to drop it:
$ sudo psql -c "SELECT pg_drop_replication_slot('replica_01');"

d. Manually copy the data from the primary via the pg_basebackup utility program:
$ sudo pg_basebackup -h -U replication_username -p 5432 -X stream -C -S replica_01 -v -R -W -D /var/lib/postgresql/version/main/
$ sudo -h -u postgres -p 5432 -U replication_username-D /var/lib/postgresql/version/main/ -Fp -Xs -R

For -h, use the IP of the primary host.
For -u, use the local postgres service/user account.
For -p, you can omit if you are using the standard port 5432.
For -U, use the replication_username created above.
For -X, use can spell out stream or change to -Xs.
For -C, use to create a replica slot before the backup starts.
For -S, use to give the replica its slot name (S is used with C).
For -v, use to get a verbose output.
For -D, use the data folder on the local replica just cleared above.
For -R, this automatically creates an empty database file, and sets the secondary/replica node as a standby (hot standby) which occurs when the replica node's service is restarted. It also populates the primary hosts information into the file.
For -Fp, you can set the transfer in plain-text instead of using a .tar file.

5. Restart the replica into its standby mode and verify that replication is streaming:
a. Restart the service:
$ sudo systemctl restart postgresql@version-main
<watch restart info>

$ sudo systemctl status postgresql@version-main
<verify status>

Verify the data w/in the database:
$ sudo -u postgres psql
postgres=# SELECT client_addr, state FROM pg_stat_replication;
<verify output>

client_addr | state | streaming
(1 row)

Close the session:
postgres=# \q

For streaming replication, the status query on the replica/secondary node:
$ sudo -u postgres psql
postgres=# SELECT client_addr, state FROM pg_stat_wal_receiver;
<verify output>

previous page