Barman 2.11: barman-cloud-restore and barman-cloud-wal-restore
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 [email protected]
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.
I setup restore_command in /etc/postgresql/10/main/postgresql.conf and then got following error when starting postgresql 10
restore_command = ‘barman-cloud-wal-restore -P barman-cloud s3://barman-s3-test ubuntu18 %f %p’
2020-08-01 18:24:31.485 GMT [21944] LOG: unrecognized configuration parameter “restore_command” in file “/etc/postgresql/10/main/postgresql.conf” line 666
2020-08-01 18:24:31.485 GMT [21944] FATAL: configuration file “/etc/postgresql/10/main/postgresql.conf” contains errors
It seems you’re getting this error because you’re using PostgreSQL 10.
In PostgreSQL 10 the
restore_command parameter
has to be placed inside therecovery.conf
file in the standby server.One of the major changes in PostgreSQL 12 indeed, is the replacement of
recovery.conf
and the conversion of its parameters to normal PostgreSQL configuration options. This removes the need for a separate configuration file and enables replication to be configured as any other option insidepostgresql.conf
.I setup restore_command in /etc/postgresql/10/main/postgresql.conf and then got following error when starting postgresql 10
restore_command = ‘barman-cloud-wal-restore -P barman-cloud s3://barman-s3-test ubuntu18 %f %p’
2020-08-01 18:24:31.485 GMT [21944] LOG: unrecognized configuration parameter “restore_command” in file “/etc/postgresql/10/main/postgresql.conf” line 666
2020-08-01 18:24:31.485 GMT [21944] FATAL: configuration file “/etc/postgresql/10/main/postgresql.conf” contains errors
Error when restore:
$ barman-cloud-restore –profile barman-cloud s3://bucket myhostname) 20200925T224745 /var/lib/postgresql/12/main
2020-09-25 22:49:27,991 [911717] ERROR: Barman cloud restore exception: seeking backwards is not allowed
And this seems to be a Python3 error?
I setup restore_command in /datos/data_5432/recovery.conf and then got following error when starting postgresql 11
2020-12-02 15:53:34.452 -03 3 LOG: invalid checkpoint record
2020-12-02 15:53:34.452 -03 4 FATAL: could not locate required checkpoint record
2020-12-02 15:53:34.452 -03 5 HINT: If you are not restoring from a backup, try removing the file “/datos/data_5432/backup_label”.
My recovery.conf file contains:
restore_command = ‘barman-cloud-wal-restore -P barman-cloud s3://sempre-prod-barman/ pg11 %f %p’
recovery_target_timeline = ‘latest’
What I’m doing wrong?