Configure pgbackrest on Backup Host – Remote in PostgreSQL

June 29, 2021
()

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.HostnameIPRole
1test-machine01192.168.114.177PostgreSql Database Server
2test-machine02192.168.114.176pgbackrest 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

, , ,


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?