Configure pgbackrest on Backup Host – Remote in PostgreSQL
In the previous blog, we demonstrated How to install pgbackrest on Database Server – Local in PostgreSQL Click here to read more. Such setups are not used in a production environment to perform the backup on the same server where the database is running.
In this blog, we will configure pgbackrest on Remote Backup Host in PostgreSQL.
We need to install pgBackRest on the database and the backup server to configure it. Make sure you install the same version on both. We have already install pgBackRest PostgreSql Server in the previous blog. So will install pgBackRest only in Backup Server. Below are machine configuration details:
| Sr No. | Hostname | IP | Role |
| 1 | test-machine01 | 192.168.114.177 | PostgreSql Database Server |
| 2 | test-machine02 | 192.168.114.176 | pgbackrest Backup Server |
Step 1. Verify PostgresQL repository in Backup Server: Use OS command yum repolist to confirm PostgreSQL repository exist in Backup Server. Click here to configure postgresql repository if it is not configured. Packages for pgBackRest are available in the PGDG yum repositories.
[root@test-machine02 ~]# yum repolist
Loaded plugins: langpacks, ulninfo
repo id repo name status
!mongodb-org-4.4/7Server MongoDB Repository 76
!mysql-connectors-community/x86_64 MySQL Connectors Community 203
!mysql-tools-community/x86_64 MySQL Tools Community 129
!mysql80-community/x86_64 MySQL 8.0 Community Server 265
!ol7_UEKR6/x86_64 Latest Unbreakable Enterprise Kernel Release 6 for Oracle Linux 7Server (x86_64) 323
!ol7_latest/x86_64 Oracle Linux 7Server Latest (x86_64) 22,766
!pgdg-common/7Server/x86_64 PostgreSQL common RPMs for RHEL/CentOS 7Server - x86_64 303
!pgdg10/7Server/x86_64 PostgreSQL 10 for RHEL/CentOS 7Server - x86_64 918
!pgdg11/7Server/x86_64 PostgreSQL 11 for RHEL/CentOS 7Server - x86_64 1,006
!pgdg12/7Server/x86_64 PostgreSQL 12 for RHEL/CentOS 7Server - x86_64 572
!pgdg13/7Server/x86_64 PostgreSQL 13 for RHEL/CentOS 7Server - x86_64 298
!pgdg95/7Server/x86_64 PostgreSQL 9.5 for RHEL/CentOS 7Server - x86_64 763
!pgdg96/7Server/x86_64 PostgreSQL 9.6 for RHEL/CentOS 7Server - x86_64 889
repolist: 28,511
[root@test-machine02 ~]#
Step 2. Install pgbackrest in Backup Server: Use OS command yum install pgbackrest to install pgbackrest package in Backup Server.
[root@test-machine02 ~]# yum install pgbackrest
Loaded plugins: langpacks, ulninfo
pgdg10 | 3.6 kB 00:00:00
pgdg11 | 3.6 kB 00:00:00
pgdg12 | 3.6 kB 00:00:00
pgdg13 | 3.6 kB 00:00:00
pgdg95 | 3.6 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package pgbackrest.x86_64 0:2.34-1.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================
Installing:
pgbackrest x86_64 2.34-1.rhel7 pgdg-common 320 k
Transaction Summary
==============================================================================================================================================================================================
Install 1 Package
Total download size: 320 k
Installed size: 855 k
Is this ok [y/d/N]: y
Downloading packages:
pgbackrest-2.34-1.rhel7.x86_64.rpm | 320 kB 00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgbackrest-2.34-1.rhel7.x86_64 1/1
Verifying : pgbackrest-2.34-1.rhel7.x86_64 1/1
Installed:
pgbackrest.x86_64 0:2.34-1.rhel7
Complete!
[root@test-machine02 ~]#
Step 3. Verify pgbackrest package installation: Use simple commands like pgbackrest version or pgbackrest help to verify collection.
[root@test-machine02 ~]# pgbackrest version
pgBackRest 2.34
[root@test-machine02 ~]#
[root@test-machine02 ~]# pgbackrest help
pgBackRest 2.34 - General help
Usage:
pgbackrest [options] [command]
Commands:
archive-get Get a WAL segment from the archive.
archive-push Push a WAL segment to the archive.
backup Backup a database cluster.
check Check the configuration.
start Allow pgBackRest processes to run.
stop Stop pgBackRest processes from running.
version Get version.
Use 'pgbackrest help [command]' for more information.
[root@test-machine02 ~]#
Step 4: Set Password for OS Account postgres in Backup Server: Use OS command passwd postgres to reset postgres OS account in Backup Server.
[root@test-machine02 ~]# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.
[root@test-machine02 ~]#
Step 5: Set Password for OS Account postgres in Postgresql Server: Use OS command passwd postgres to reset postgres OS account in Postgresql Server.
[root@test-machine01 ~]# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.
[root@test-machine01 ~]#
Step 6: Configure Passwordless Authentication in Backup Server: Use OS command ssh-keygen to generate public and private keys. Once keys are generated use OS command ssh-copy-id to transfer the public key to PostgreSQL Database Server.
[root@test-machine02 ~]# su - postgres
Last login: Wed Jun 16 17:44:29 +03 2021 on pts/2
-bash-4.2$
-bash-4.2$
-bash-4.2$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:jiouqVEgyZz+dmsHM+H0yzn4GgdhYnqBkbJ3Es/ralw postgres@test-machine02
The key's randomart image is:
+---[RSA 2048]----+
| .. |
|+o= |
|=* B o |
|+.= Bo. |
| +.+ooo S |
| .o E=.+ |
|.o = oB.+ |
|o.= o++* |
|o+oooo+.. |
+----[SHA256]-----+
-bash-4.2$
-bash-4.2$ ls -ltr .ssh
total 8
-rw-r--r--. 1 postgres postgres 405 Jun 22 10:12 id_rsa.pub
-rw-------. 1 postgres postgres 1679 Jun 22 10:12 id_rsa
-bash-4.2$
-bash-4.2$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@test-machine01
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@test-machine01's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@test-machine01'"
and check to make sure that only the key(s) you wanted were added.
-bash-4.2$
-bash-4.2$ ssh 'postgres@test-machine01'
Last login: Tue Jun 22 10:32:10 2021
-bash-4.2$
-bash-4.2$ exit
logout
Connection to test-machine01 closed.
-bash-4.2$
Step 7: Configure Passwordless Authentication in Postgresql Server: Use OS command ssh-keygen to generate public and private keys. Once keys are generated use OS command ssh-copy-id to transfer the public key to Backup Server.
[root@test-machine01 ~]# su - postgres
Last login: Sun Jun 27 16:37:19 +03 2021 from test-machine02 on pts/2
-bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
-bash-4.2$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:zBWHyvE8fXqzxXuwBz97rsOqX9UyCEzHkJciTY+5deQ postgres@test-machine01
The key's randomart image is:
+---[RSA 2048]----+
| o==o.. |
| oo=B+o |
| . **+o E |
| oo.++.o. .|
| S ...oo.o|
| . =+o|
| o.O.|
| .=o=|
| .oo..**|
+----[SHA256]-----+
-bash-4.2$
-bash-4.2$ ls -ltr .ssh
total 12
-rw-------. 1 postgres postgres 405 Jun 27 16:37 authorized_keys
-rw-r--r--. 1 postgres postgres 405 Jun 27 16:40 id_rsa.pub
-rw-------. 1 postgres postgres 1675 Jun 27 16:40 id_rsa
-bash-4.2$
-bash-4.2$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@test-machine02
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"
The authenticity of host 'test-machine02 (192.168.114.176)' can't be established.
ECDSA key fingerprint is SHA256:NbJdl0Pi8lmjbKHbz7q0GgYAy++evY3rNH7gCupnx+c.
ECDSA key fingerprint is MD5:39:33:1f:a7:bb:09:ac:81:cc:ff:cc:18:46:ca:76:c1.
Are you sure you want to continue connecting (yes/no)? yes
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@test-machine02's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@test-machine02'"
and check to make sure that only the key(s) you wanted were added.
-bash-4.2$
-bash-4.2$ ssh 'postgres@test-machine02'
Last login: Sun Jun 27 16:36:05 2021
-bash-4.2$
-bash-4.2$ exit
logout
Connection to test-machine02 closed.
-bash-4.2$
Step 8: Configure the pgBackRest configuration file in PostgreSql Server: Configure the pgBackRest configuration file with the database path and backup host as follows:
[root@test-machine01 ~]# vi /etc/pgbackrest.conf
[global]
repo1-host=test-machine02
repo1-host-user=postgres
process-max=2
log-level-console=info
log-level-file=debug
[prod_backup]
pg1-path=/var/lib/pgsql/13/data
:wq!
Step 9: Change the postgresql.conf in PostgreSql Server: Update postgresql.conf to have the following changes.
postgres=#
postgres=# alter system set archive_mode = on;
ALTER SYSTEM
postgres=# alter system set archive_command = 'pgbackrest --stanza=prod_backup 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=prod_backup archive-push %p |
archive_mode | on |
(2 rows)
postgres=#
Step 10: Change the pgbackrest.conf in Backup Server: On the Backup Server, configure the pgBackRest configuration file with the database host and path, along with the repository path where the backups will be stored. We have added the database host as ‘pg1-host‘ so that we can add more database hosts later as ‘pg2-host‘ and so on. The same goes for pg1-path.
[root@test-machine02 ~]#vi /etc/pgbackrest.conf
[global]
repo1-path=/u01/pg_backup
repo1-retention-full=2
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
stop-auto=y
[prod_backup]
pg1-path=/var/lib/pgsql/13/data
pg1-host=test-machine01
pg1-host-user=postgres
:wq!
Step 11: Create the stanza and check the configuration in Backup Server: Create a stanza on the Backup Server and check if it’s working.
-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup stanza-create
2021-06-28 12:44:52.072 P00 INFO: stanza-create command begin 2.34: --exec-id=2201-252f8058 --log-level-console=info --log-level-file=debug --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --repo1-path=/u01/pg_backup --stanza=prod_backup
2021-06-28 12:44:53.082 P00 INFO: stanza-create for stanza 'prod_backup' on repo1
2021-06-28 12:44:53.261 P00 INFO: stanza-create command end: completed successfully (1190ms)
-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup check
2021-06-28 12:45:01.055 P00 INFO: check command begin 2.34: --exec-id=2203-f7b65363 --log-level-console=info --log-level-file=debug --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --repo1-path=/u01/pg_backup --stanza=prod_backup
2021-06-28 12:45:01.866 P00 INFO: check repo1 configuration (primary)
2021-06-28 12:45:02.072 P00 INFO: check repo1 archive for WAL (primary)
2021-06-28 12:45:02.776 P00 INFO: WAL segment 00000001000000000000000A successfully archived to '/u01/pg_backup/archive/prod_backup/13-1/0000000100000000/00000001000000000000000A-151bfd75cc90447fbb895b36203ff5115d98caf1.gz' on repo1
2021-06-28 12:45:02.884 P00 INFO: check command end: completed successfully (1829ms)
-bash-4.2$
-bash-4.2$
Step 12: Check the configuration on PostgreSql Server: Check the configuration on Postgresql server using below comamnd. With these all steps pgbackrest setup is ready.
-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup check
2021-06-28 12:49:29.127 P00 INFO: check command begin 2.34: --exec-id=3963-dfe673e5 --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/13/data --repo1-host=test-machine02 --repo1-host-user=postgres --stanza=prod_backup
2021-06-28 12:49:29.740 P00 INFO: check repo1 configuration (primary)
2021-06-28 12:49:30.317 P00 INFO: check repo1 archive for WAL (primary)
2021-06-28 12:49:30.733 P00 INFO: WAL segment 00000001000000000000000B successfully archived to '/u01/pg_backup/archive/prod_backup/13-1/0000000100000000/00000001000000000000000B-01216940bff63282eb64e5dd6655692b047c2fdc.gz' on repo1
2021-06-28 12:49:30.834 P00 INFO: check command end: completed successfully (1708ms)
-bash-4.2$
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
- Backups from the Standby Server using pgBackRest in PostgreSQL
- Traditional Barman Setup With WAL Streaming in PostgreSQL
- Traditional Barman Setup With WAL archiving via archive_command in PostgreSQL
- Streaming Backup With Fallback WAL Archiving in PostgreSQL
- Configure Streaming Backup and WAL Streaming using BARMAN in PostgreSQL
Thanks for your sharing post is useful
Thanks, Sardar for the review and feedback.
Regards,
Team DBsGuru.