Task:
Verify postgres cluster primary to replica replication status.
Steps:
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
Notes:
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 = 10.0.1.12
...
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)
Notes:
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 10.0.2.12/32 trust
host replication all 10.0.3.12/32 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.
Note:
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 10.0.1.12 -U replication_username -p 5432 -X stream -C -S replica_01 -v -R -W -D /var/lib/postgresql/version/main/
or
$ sudo -h 10.0.1.12 -u postgres -p 5432 -U replication_username-D /var/lib/postgresql/version/main/ -Fp -Xs -R
Notes:
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 posgresql.auto.conf 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>
Example:
client_addr | state
10.0.2.12 | streaming
(1 row)
Close the session:
postgres=# \q
Note:
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
|