In our previous blog post, we discussed how to set up pgBackrest on a single standalone PostgreSQL Instance. When you run backups on a Primary Instance, we would be utilizing the computing power of the primary instance that takes the WRITES traffic. For this reason, one may choose to shift the backup job from primary to standby. These days, It is quite common to have a High Availability cluster with 1 Primary and one or more Standby hosts. Considering this fact, we could also run backups from a standby server and offload the overhead from primary to a standby server, during backups. In this article, we will see the steps involved in running backup from a standby server using pgBackRest.
Pre-requisites
Let us see the prerequisites to make from the backups from standby work seamlessly.
- It is important to have Streaming Replication setup between the Primary and Standby PostgreSQL Host.
(This procedure is not applicable for a Subscriber (considered as a replica) of a Publisher replicating using logical replication). - pgBackrest must be installed in Primary, Standby, and also Backup Server (if applicable). If you have not installed pgBackrest, refer to our previous blog post where we have explained how to install pgBackrest.
Setup demonstrated in this Article
In this article, we have included a setup that includes the following 3 servers -
- pg1 (Primary Server)
- pg2 (Standby Server)
- backupserver (Backup repository Server)
When we don't have a Backup Server but have a shared NFS filesystem to store backups, the steps performed on the backupserver can be performed on the standby server (pg2). This means that the NFS filesystem is mounted on both Primary and Standby servers.
Steps to configure backups from a standby server using pgBackRest
Step 1 : Create the required directories on the Primary and Standby Servers for pgBackRest.
| sudo mkdir -p /var/log/pgbackrest sudo chown -R postgres:postgres /var/log/pgbackrest sudo chmod -R 700 /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo chown postgres:postgres -R /etc/pgbackrest sudo chmod -R 755 /etc/pgbackrest sudo mkdir /var/spool/pgbackrest sudo chown -R postgres:postgres /var/spool/pgbackrest sudo chmod -R 755 /var/spool/pgbackrest |
Step 2 : Enable trusted ssh authentication (password-less ssh) between all the three servers.
| pgbackrest@backupserver:~$ ssh-keygen -t rsa -b 2048 Generating public/private rsa key pair. .... .... postgres@pg1:~$ ssh-keygen -t rsa -b 2048 Generating public/private rsa key pair. .... .... postgres@pg1:~$ ssh-keygen -t rsa -b 2048 Generating public/private rsa key pair. .... .... pgbackrest@backupserver:~$ ssh-copy-id postgres@pg1 pgbackrest@backupserver:~$ ssh-copy-id postgres@pg2 postgres@pg1:~$ ssh-copy-id pgbackrest@backupserver postgres@pg2:~$ ssh-copy-id pgbackrest@backupserver postgres@pg1:~$ ssh-copy-id postgres@pg2 postgres@pg2:~$ ssh-copy-id postgres@pg1 |
Note: In a production database, we could expect either an Automatic failover or a manual switchover/failover. In such a case, a standby server can always become a Primary. So the following pgBackrest configuration files will be created on both pg1 and pg2 servers to make it easy to switch upon a failover.
Step 3 : Create the following pgBackRest configuration file on both PostgreSQL servers. This is only used for the purpose of Archiving from Primary.
| $ vi /etc/pgbackrest/backrest_primary.conf
[global] |
Step 4 : Create the following pgBackRest configuration file on both PostgreSQL servers. This configuration file is only used by restore_command on the standby to catch up with replication.
| $ vi /etc/pgbackrest/standby_restore_command.conf
[global] [backupstanza] |
Step 5 : Set archive_command and restore_command to following settings on Primary and Standby servers.
| postgres@pg1:~$ psql -c "ALTER SYSTEM SET archive_command TO 'pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza archive-push %p'" ALTER SYSTEM
postgres@pg1:~$ psql -c "ALTER SYSTEM SET restore_command TO 'pgbackrest --config=/etc/pgbackrest/standby_restore_command.conf --stanza=backupstanza archive-get %f "%p"'" postgres@pg2:~$ psql -c "ALTER SYSTEM SET archive_command TO 'pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza archive-push %p'" postgres@pg2:~$ psql -c "ALTER SYSTEM SET restore_command TO 'pgbackrest --config=/etc/pgbackrest/standby_restore_command.conf --stanza=backupstanza archive-get %f "%p"'" postgres@pg1:~$ psql -c "select pg_reload_conf()" postgres@pg2:~$ psql -c "select pg_reload_conf()" |
Step 6 : Create a New repository location for backups on the Backup Server. If there is no backup server but only the common mount on Standby, then, perform these steps on the Standby server.
| sudo mkdir -p /backupdir sudo chown -R pgbackrest:pgbackrest /backupdir sudo chmod -R 700 /backupdir |
Step 7 : Create a Stanza for the Primary server on the Backup Server. This command must be executed on the primary instance.
| $ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza stanza-create |
Output looks like following -
| postgres@pg1:~$ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza stanza-create
2021-10-10 23:11:44.742 P00 INFO: stanza-create command begin 2.35: --config=/etc/pgbackrest/backrest_primary.conf --exec-id=91412-8d6c4c1b --log-level-console=info --log-level-file=info --pg1-path=/var/lib/postgresql/13/main --pg1-port=5432 --repo1-host=192.168.131.33 --repo1-host-user=postgres --repo1-path=/backupdir --stanza=backupstanza |
Step 8 : Validate configuration from the Primary
| $ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza check |
Output appears as following
| postgres@localhost:~$ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza check
2021-10-10 23:17:56.466 P00 INFO: check command begin 2.35: --config=/etc/pgbackrest/backrest_primary.conf --exec-id=91597-4a03d0d8 --log-level-console=info --log-level-file=info --pg1-path=/var/lib/postgresql/13/main --pg1-port=5432 --repo1-host=192.168.131.33 --repo1-host-user=postgres --repo1-path=/backupdir --stanza=backupstanza 2021-10-10 23:17:56.871 P00 INFO: check repo1 configuration (primary) |
Step 9 : Create the following configuration file in the Backup server. This file will be used for performing backups from the standby server.
Please Note : If there is no backup server, but you have an NFS file system mounted on both pg servers, you will be running backups from the Standby server. In this case, you need to remove the parameter: pg2-host=pg2 from the following configuration file. This is because, the backup will run locally and pgBackRest does not expect the pg2-host parameter when it is initiated from a standby host.
In case you don’t have a separate backup server, we can schedule a cronjob in both the pg server which will check the status of the PostgreSQL server (select pg_is_in_recovery) which is running locally, and then trigger the pgBackrest command.
| $ vi /etc/pgbackrest/backrest_standby.conf
[global] [backupstanza] |
Step 10 : Run backup from the backup server using the following command as an example.
As the configuration file contains the parameter backup-standby=y, it will run a backup of Globals from Primary and the actual data directory from the Standby.
| $ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza=backupstanza --type=full --archive-timeout=2000 --compress backup |
Output from the above command -
| pgbackrest@backupserver:~$ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza=backupstanza --type=full --archive-timeout=2000 --compress backup
2021-10-10 23:35:05.003 P00 INFO: backup command begin 2.35: --archive-copy --archive-timeout=2000 --backup-standby --compress --config=/etc/pgbackrest/backrest_standby.conf --exec-id=1881235-bea4562f --log-level-console=info --log-level-file=info --pg1-host=pg1 --pg2-host=pg2 --pg1-path=/var/lib/postgresql/13/main --pg2-path=/var/lib/postgresql/13/main --pg1-port=5432 --pg2-port=5432 --pg1-user=postgres --pg2-user=postgres --process-max=8 --repo1-path=/backupdir --repo1-retention-full=1 --stanza=montymobile --start-fast --type=full 2021-10-10 23:35:07.269 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes |
Step 11 : Validate the backup using the below command.
| $ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza= backupstanza info |
Output Should look Similar to Below
| pgbackrest@backupserver:~$ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza=backupstanza info
stanza: backupstanza |
Conclusion :
This article gives us detailed instructions on how to take a Backup using pgBackRest from a standby server in PostgreSQL. In our previous articles, we discussed about how to perform a backup and restore or point-in-time-recovery using pgBackRest. Subscribe to our newsletters for more updates about the latest PostgreSQL features. If you are looking for any services around Migrations to PostgreSQL or Consulting services for PostgreSQL, please fill the following form or Contact Us by sending an email to sales@migops.com
