Setup Streaming Replication with pgBackRest in PostgreSQL

August 18, 2021
()

Setup Streaming Replication with pgBackRest PostgreSQL

In the recent two blogs, we discuss How to set up Streaming Replication Click here to read more and Configure pgbackrest on Database Server – Local Click here to read more. 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.HostnameIPRole
1test-machine01192.168.114.177Master / Primary Server
2test-machine02192.168.114.176Standby / 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!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *