Thanks to the new utilities barman-cloud-restore and barman-cloud-wal-restore introduced in Barman 2.11, it is now possible to execute the recovery of a PostgreSQL instance using a full backup previously executed using barman-cloud-wal-archive and barman-cloud-backup commands. Let’s discover together how to implement this in the following article.
It is worth noting that in Barman 2.11, all cloud utilities for Barman are now in a separate package called barman-cli-cloud.
Requirements
1. barman-cli-cloud package
2. A PostgreSQL instance
3. An AWS S3 bucket
4. A second virtual machine where to execute the restore
In this article, we will test barman-cli-cloud functionalities in a virtual machine with Debian Buster and PostgreSQL 12. In order to properly follow the instructions contained within this article, we also assume you have:
- configured Postgres to archive WAL files to an existing S3 bucket using
barman-cloud-wal-archive - executed a backup and ship it to the same S3 bucket through
barman-cloud-backup
You may easily achieve this by following the instructions contained in these previous blog articles:
Set-up the recovery server
As a result, we have an S3 bucket on AWS named barman-s3-test which already contains the WAL files and backup archived via barman-cloud-wal-archive and barman-cloud-backup tools, now we need to properly configure a server that will be the host for the recovery of the PostgreSQL instance.
1. Install PostgreSQL 12 from the official PGDG repository
2. Install the 2ndQuadrant Public repository
3. Install the barman-cli-cloud package:
root@recovery:~# apt update
root@recovery:~# apt install barman-cli-cloud
4. Install awscli package:
root@recovery:~# apt install awscli
5. Configure AWS credentials with the awscli tool as the postgres user:
postgres@recovery:~$ aws configure --profile barman-cloud
AWS Access Key ID [None]: AKI*****************
AWS Secret Access Key [None]: ****************************************
Default region name [None]: eu-west-1
Default output format [None]: json
Execute the restore procedure
Now that the recovery server is correctly configured, we are ready to start the restore procedure.
Barman 2.11 introduces the barman-cloud-backup-list command that allows you to retrieve information about the backups made with barman-cloud-backup:
postgres@recovery:~$ barman-cloud-backup-list \
--profile barman-cloud \
s3://barman-s3-test pg12
Backup ID End Time Begin Wal
20200713T120856 2020-07-13 12:09:05 00000001000000000000000C
We are now ready to execute the restore using the barman-cloud-restore command:
postgres@recovery:~$ barman-cloud-restore \
--profile barman-cloud \
s3://barman-s3-test \
pg12 20200713T120856 \
/var/lib/postgresql/12/main/
Once the restore terminates successfully we can check the PGDATA directory contents:
postgres@recovery:~$ ls /var/lib/postgresql/12/main/
PG_VERSION global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf
backup_label pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_wal postgresql.conf
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_xact
Now, in order to be sure that the restore process worked properly, we need to start the recovered PostgreSQL instance and verify that everything works as expected. This process requires some additional steps.
First, since we are on a Debian system, we need to copy the files containing the PostgreSQL configuration under the /etc/postgresql/12/main/ directory:
postgres@recovery:~$ cp /var/lib/postgresql/12/main/postgresql.conf /etc/postgresql/12/main/
postgres@recovery:~$ cp /var/lib/postgresql/12/main/pg_hba.conf /etc/postgresql/12/main/
postgres@recovery:~$ cp /var/lib/postgresql/12/main/pg_ident.conf /etc/postgresql/12/main/
Second, create the recovery.signal file:
postgres@recovery:~$ touch /var/lib/postgresql/12/main/recovery.signal
Then, disable the archive_command to prevent the recovered instance from writing in the same bucket as the original instance:
postgres@recovery:~$ echo \"archive_command = 'cd .'\" >> /etc/postgresql/12/main/postgresql.conf
After that, you need to configure PostgreSQL to retrieve the WAL files of the latest available timeline from the S3 bucket by using the barman-cloud-wal-restore in the restore_command:
postgres@recovery:~$ echo \"restore_command = 'barman-cloud-wal-restore --profile barman-cloud s3://barman-s3-test pg12 %f %p'\" >> /etc/postgresql/12/main/postgresql.conf
postgres@recovery:~$ echo \"recovery_target_timeline = 'latest'\" >> /etc/postgresql/12/main/postgresql.conf
IMPORTANT: Before proceeding please ensure that the PostgreSQL instance is not running, and that the destination directory (the default PostgreSQL datadir) is empty.
Finally, we are ready to start the new recovered instance:
root@recovery:~# systemctl restart postgresql@12-main.service
Great! As we can see from the PostgreSQL log, WAL files are recovered from the S3 bucket and the instance was correctly started:
postgres@recovery:~$ less /var/log/postgresql/postgresql-12-main.log
...
2020-07-13 12:43:25.093 UTC [9458] LOG: starting PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-07-13 12:43:25.093 UTC [9458] LOG: listening on IPv4 address "127.0.0.1", port 5432
2020-07-13 12:43:25.095 UTC [9458] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-07-13 12:43:25.111 UTC [9459] LOG: database system was interrupted; last known up at 2020-07-13 12:08:56 UTC
2020-07-13 12:43:25.508 UTC [9459] LOG: starting archive recovery
2020-07-13 12:43:26.010 UTC [9459] LOG: restored log file "00000001000000000000000C" from archive
2020-07-13 12:43:26.052 UTC [9459] LOG: redo starts at 0/C000028
2020-07-13 12:43:26.054 UTC [9459] LOG: consistent recovery state reached at 0/C000138
2020-07-13 12:43:26.054 UTC [9458] LOG: database system is ready to accept read only connections
2020-07-13 12:43:26.469 UTC [9459] LOG: restored log file "00000001000000000000000D" from archive
2020-07-13 12:43:26.823 UTC [9459] LOG: redo done at 0/D0001B0
2020-07-13 12:43:27.242 UTC [9459] LOG: restored log file "00000001000000000000000D" from archive
2020-07-13 12:43:27.592 UTC [9459] LOG: selected new timeline ID: 2
2020-07-13 12:43:27.644 UTC [9459] LOG: archive recovery complete
2020-07-13 12:43:27.979 UTC [9458] LOG: database system is ready to accept connections
Conclusion
As usual with any new release of Barman, we recommend that everyone updates their systems to the latest version. A complete list of changes and bug fixes is available here.
Please note that if you are using already barman-cloud-wal-archive or barman-cloud-backup installed via RPM/Apt package and you are upgrading your system, you must install the barman-cli-cloud package. This is due to the fact that, with the Barman 2.11 release, all cloud related tools are part of the barman-cli-cloud package as explained at the beginning of the article.
The next versions of Barman might improve the usability and automation capabilities of the recover command, for example by preparing a recovery.conf or recovery.signal file like the actual Barman does.