In this blog, we will demonstrate How to Setup Streaming Replication with pgBackRest in PostgreSQL.
Below are the high-level steps we will follow to set up our replication.
1. Setup a shared repository (NFS) between the hosts
2.1. Prepare the Primary Node for Replication
2.2 Update pg_hba.conf
3. Configure pgBackRest to backup the Primary Node
4. Standby Setup
5. Test Streaming Replication
Below are setup details and the same will be used in this demonstration.
Sr. No. Hostname IP Role
1 test-machine01 192.168.114.177 Master / Primary Server
2 test-machine02 192.168.114.176 Standby / Secondary Server
Step 1. Setup a shared repository (NFS) between the hosts: To be able to share the backups between the hosts, we’ll here create an NFS shared folder from Primary Node and mount it on Standby Node. We have already configured NFS Share in a separate post click here to read about Setup Linux NFS Server and Client.
Step 2.1. Prepare the Primary Node for Replication: We will create a replication user repluser which will use used by the Standby PostgreSQL cluster to connect to the Primary PostgreSQL cluster. Update archive_command parameter to use pgBackRest archive-push command for WAL archiving.
[root@test-machine01 ~]#
[root@test-machine01 ~]# su - postgres
-bash-4.2$ psql
psql (13.1)
Type "help" for help.
postgres=#
postgres=# CREATE USER repluser REPLICATION PASSWORD 'Root@1234';
CREATE ROLE
postgres=#
postgres=# alter system set archive_mode = on;
ALTER SYSTEM
postgres=# alter system set archive_command = 'pgbackrest --stanza=mystanza1 archive-push %p';
ALTER SYSTEM
postgres=#
[root@test-machine01 ~]#
[root@test-machine01 ~]# systemctl restart postgresql-13.service
[root@test-machine01 ~]#
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command');
name | setting | unit
-----------------+-----------------------------------------------+------
archive_command | pgbackrest --stanza=mystanza1 archive-push %p |
archive_mode | on |
(2 rows)
postgres=#
Step 2.2. Update pg_hba.conf in Primary Node: To connect replication user repluser to Primary Node from Standby Node we need to update pg_hba.conf and tell PostgreSQL Server that you will get a replication connection request from Remote Server. Otherwise, you will receive an error. Once changes are done in pg_hba.conf reload the changes with pg_ctl reload or SELECT pg_reload_conf(), Please note we added the line “host replication all 192.168.114.0/24 trust“ at end of the file.
[postgres@test-machine01]pwd
/var/lib/pgsql/13/data
[postgres@test-machine01]cat pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.114.0/24 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host replication all 192.168.114.0/24 trust
[postgres@test-machine01]
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=#
Step 3. Configure pgBackRest to backup the Primary Node: Update primary node pgBackRest configuration file /etc/pgbackrest.conf with below parameter, Please note we are using the same repository as NFS share folder /u01/psql_backup. And finally, create the stanza using command pgbackrest stanza-create and check the configuration using command pgbackrest check and then perform the backup.
[root@test-machine01 ~]# cat /etc/pgbackrest.conf
[global]
repo-path=/u01/psql_backup
retention-full=1
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
[mystanza1]
pg1-path=/var/lib/pgsql/13/data
[root@test-machine01 ~]#
[postgres@test-machine01] pgbackrest --stanza=mystanza1 stanza-create
2021-08-15 11:51:31.571 P00 INFO: stanza-create command begin 2.34: --exec-id=109077-20ae97ed --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --repo1-path=/u01/psql_backup --stanza=mystanza1
2021-08-15 11:51:32.186 P00 INFO: stanza-create for stanza 'mystanza1' on repo1
2021-08-15 11:51:32.188 P00 INFO: stanza 'mystanza1' on repo1 and is valid
2021-08-15 11:51:32.188 P00 INFO: stanza-create command end: completed successfully (618ms)
[postgres@test-machine01]pgbackrest --stanza=mystanza1 check
WARN: configuration file contains invalid option 'repo-retention-full'
2021-08-15 12:21:30.800 P00 INFO: check command begin 2.34: --exec-id=72861-e8a0f393 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --repo1-path=/u01/psql_backup --stanza=mystanza1
2021-08-15 12:21:31.433 P00 INFO: check repo1 configuration (primary)
2021-08-15 12:21:31.741 P00 INFO: check repo1 archive for WAL (primary)
2021-08-15 12:21:31.944 P00 INFO: WAL segment 00000001000000000000000D successfully archived to '/u01/psql_backup/archive/mystanza1/13-1/0000000100000000/00000001000000000000000D-86ad232d293a31c259361ee6ee14cc0412ee33db.gz' on repo1
2021-08-15 12:21:31.944 P00 INFO: check command end: completed successfully (1145ms)
[postgres@test-machine01]
[postgres@test-machine01]
[postgres@test-machine01]pgbackrest --stanza=mystanza1 --type=full backup
WARN: configuration file contains invalid option 'repo-retention-full'
2021-08-15 12:24:24.583 P00 INFO: backup command begin 2.34: --exec-id=73044-d211413c --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/psql_backup --stanza=mystanza1 --start-fast --type=full
WARN: option 'repo1-retention-full' is not set for 'repo1-retention-full-type=count', the repository may run out of space
HINT: to retain full backups indefinitely (without warning), set option 'repo1-retention-full' to the maximum.
2021-08-15 12:24:25.364 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-08-15 12:24:25.773 P00 INFO: backup start archive = 00000001000000000000000F, lsn = 0/F000028
2021-08-15 12:24:35.561 P02 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16387/14027 (0B, 100%)
2021-08-15 12:24:35.565 P00 INFO: full backup size = 108.7MB
2021-08-15 12:24:35.565 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-08-15 12:24:35.767 P00 INFO: backup stop archive = 00000001000000000000000F, lsn = 0/F000138
2021-08-15 12:24:35.770 P00 INFO: check archive for segment(s) 00000001000000000000000F:00000001000000000000000F
2021-08-15 12:24:35.815 P00 INFO: new backup label = 20210815-122425F
2021-08-15 12:24:35.871 P00 INFO: backup command end: completed successfully (11289ms)
2021-08-15 12:24:35.871 P00 INFO: expire command begin 2.34: --exec-id=73044-d211413c --log-level-console=info --log-level-file=debug --repo1-path=/u01/psql_backup --stanza=mystanza1
2021-08-15 12:24:35.885 P00 INFO: option 'repo1-retention-archive' is not set - archive logs will not be expired
2021-08-15 12:24:35.885 P00 INFO: expire command end: completed successfully (14ms)
[postgres@test-machine01]
[postgres@test-machine01]pgbackrest --stanza=mystanza1 info
WARN: configuration file contains invalid option 'repo-retention-full'
stanza: mystanza1
status: ok
cipher: none
db (current)
wal archive min/max (13): 00000001000000000000000C/00000001000000000000000F
full backup: 20210815-122425F
timestamp start/stop: 2021-08-15 12:24:25 / 2021-08-15 12:24:35
wal start/stop: 00000001000000000000000F / 00000001000000000000000F
database size: 108.7MB, database backup size: 108.7MB
repo1: backup set size: 16.7MB, backup size: 16.7MB
[postgres@test-machine01]
[postgres@test-machine01]
Step 4. Standby Setup: Add .pgpass file in postgres os user home .pgpass, this file is required to avoid specifying a password in PostgreSQL cluster configuration file. Edit pgbackrest configuration file /etc/pgbackrest.conf and add the below parameters, Please note we are using the same NFS share folder used in the Primary node. Make sure the configuration is correct by executing the pgbackrest info command. It should print the same output as above on the primary server. Restore the backup taken from the primary server using command pgbackrest restore with option –type=standby. After the restore command is done check file postgresql.auto.conf pgbackrest restore command will add the replication-related parameter like primary_conninfo & restore_command and also creates the standby.signal file needed for PostgreSQL to start in standby mode. Once all verification is done, start the Standby PostgreSQL Cluster.
[postgres@test-machine02]pwd
/var/lib/pgsql
[postgres@test-machine02]echo "test-machine01:5432:replication:repluser:Root@1234" >> .pgpass
[postgres@test-machine02]chown postgres:postgres .pgpass
[postgres@test-machine02]chmod 0600 .pgpass
[root@test-machine02 ~]# cat /etc/pgbackrest.conf
[global]
repo-path=/u01/psql_backup
retention-full=1
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
[mystanza1]
pg1-path=/var/lib/pgsql/13/data
recovery-option=primary_conninfo=host=test-machine01 user=repluser
[root@test-machine02 ~]#
[postgres@test-machine02]
[postgres@test-machine02]pgbackrest --stanza=mystanza1 info
stanza: mystanza1
status: ok
cipher: none
db (current)
wal archive min/max (13): 00000001000000000000000C/00000001000000000000000F
full backup: 20210815-122425F
timestamp start/stop: 2021-08-15 12:24:25 / 2021-08-15 12:24:35
wal start/stop: 00000001000000000000000F / 00000001000000000000000F
database size: 108.7MB, database backup size: 108.7MB
repo1: backup set size: 16.7MB, backup size: 16.7MB
[postgres@test-machine02]
[postgres@test-machine02]
[postgres@test-machine02]
[postgres@test-machine02]pgbackrest --stanza=mystanza1 --type=standby restore
2021-08-15 12:29:57.038 P00 INFO: restore command begin 2.34: --exec-id=75973-004a37c2 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --process-max=2 --recovery-option="primary_conninfo=host=test-machine01 user=repluser" --repo1-path=/u01/psql_backup --stanza=mystanza1 --type=standby
2021-08-15 12:29:57.077 P00 INFO: repo1: restore backup set 20210815-122425F
ERROR: [040]: unable to restore to path '/var/lib/pgsql/13/data' because it contains files
HINT: try using --delta if this is what you intended.
2021-08-15 12:29:57.078 P00 INFO: restore command end: aborted with exception [040]
[postgres@test-machine02]
[postgres@test-machine02]
[postgres@test-machine02]rm -rf /var/lib/pgsql/13/data/*
[postgres@test-machine02]
[postgres@test-machine02]rm -rf pg1_tbls/*
[postgres@test-machine02]
[postgres@test-machine02]rm -rf pg2_tbls/*
[postgres@test-machine02]
[postgres@test-machine02]
[postgres@test-machine02]pgbackrest --stanza=mystanza1 --type=standby restore
2021-08-15 12:32:06.367 P00 INFO: restore command begin 2.34: --exec-id=76097-a9cbfce5 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --process-max=2 --recovery-option="primary_conninfo=host=test-machine01 user=repluser" --repo1-path=/u01/psql_backup --stanza=mystanza1 --type=standby
2021-08-15 12:32:06.406 P00 INFO: repo1: restore backup set 20210815-122425F
2021-08-15 12:32:06.483 P01 INFO: restore file /var/lib/pgsql/13/data/log/postgresql-Sun.log (1.7MB, 1%) checksum 23cb1111abcfacb0b7246a1701584248bc7ad754
2021-08-15 12:32:13.545 P02 INFO: restore file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16387/14037 (0B, 100%)
2021-08-15 12:32:13.746 P01 INFO: restore file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16387/14032 (0B, 100%)
2021-08-15 12:32:13.746 P02 INFO: restore file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16387/14027 (0B, 100%)
2021-08-15 12:32:13.748 P00 INFO: write updated /var/lib/pgsql/13/data/postgresql.auto.conf
2021-08-15 12:32:13.781 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2021-08-15 12:32:13.793 P00 INFO: restore command end: completed successfully (7427ms)
[postgres@test-machine02]
[postgres@test-machine02]pwd
/var/lib/pgsql/13/data
[postgres@test-machine02]
[postgres@test-machine02]cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
archive_mode = 'on'
wal_keep_size = '320 MB'
max_slot_wal_keep_size = '1024 MB'
wal_log_hints = 'on'
archive_command = 'pgbackrest --stanza=mystanza1 archive-push %p'
# Recovery settings generated by pgBackRest restore on 2021-08-15 12:32:13
primary_conninfo = 'host=test-machine01 user=repluser'
restore_command = 'pgbackrest --stanza=mystanza1 archive-get %f "%p"'
[postgres@test-machine02]
[root@test-machine02 etc]#
[root@test-machine02 etc]# systemctl start postgresql-13
[root@test-machine02 etc]#
postgres=#
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=#
Step 5. Test Streaming Replication: Create test table my_table_test1 in Primary node and check if same replicated in Standby node.
Master
postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=# CREATE TABLE my_table_test1 (col1 varchar, col2 varchar);
CREATE TABLE
sample1=# INSERT INTO my_table_test1 VALUES ('row1', 'this is row 1'), ('row2', 'this is row 2');
INSERT 0 2
sample1=#
Standby
postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=# \x
Expanded display is off.
sample1=# select * from my_table_test1 ;
col1 | col2
------+---------------
row1 | this is row 1
row2 | this is row 2
(2 rows)
sample1=#
postgres=#
postgres=# \x
Expanded display is on.
postgres=#
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 76324
status | streaming
receive_start_lsn | 0/10000000
receive_start_tli | 1
written_lsn | 0/10000148
flushed_lsn | 0/10000148
received_tli | 1
last_msg_send_time | 2021-08-15 12:38:07.721423+03
last_msg_receipt_time | 2021-08-15 12:38:07.720848+03
latest_end_lsn | 0/10000148
latest_end_time | 2021-08-15 12:36:07.544876+03
slot_name |
sender_host | test-machine01
sender_port | 5432
conninfo | user=repluser passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=test-machine01 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
postgres=# select pg_is_in_recovery(),pg_is_wal_replay_paused(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+-----------
pg_is_in_recovery | t
pg_is_wal_replay_paused | f
pg_last_wal_receive_lsn | 0/10000148
pg_last_wal_replay_lsn | 0/10000148
pg_last_xact_replay_timestamp |
postgres=#This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!