How to restore backups of PostgreSQL from S3 using Barman and barman-cloud-backup
In my previous post, I showed how to automate backups of a PostgreSQL database to S3 using Barman and barman-cloud-backup.
Step 1: verify hardware architecture and PostgreSQL version
For a successful restore, Barman requires that the hardware architecture and PostgreSQL version are both identical. You can verify these with some simple terminal commands:
rob@pg:~$ uname -m # report architecture
x86_64
rob@pg:~$ psql --version # PostgreSQL version
psql (PostgreSQL) 15.5 (Ubuntu 15.5-1.pgdg22.04+1)
Run these on both the source and target. Obviously, if you dont have the source any more (which is why you’re restoring), you’ll need to make some assumptions…
Step 2: Install Barman, barman-cli-cloud, AWS CLI, python-snappy
Follow the instructions in steps 5-10 of in my original article. When you’ve completed the restore, you can pick up from steps 11 onwards to automate backups again.
If you no longer have the credentials for your IAM user, log in to the console and generate a secondary key pair. Once you’ve completed the restore, you can swap them and retire the old keys.
Step 3: Verify S3 connectivity and identify the latest backup
We can use the barman-backup-list
command to see a list of our backups on S3. This will also verify connectivity.
rob@pg:~/.aws$ barman-cloud-backup-list s3://<bucket>/barman pg
Backup ID End Time Begin Wal Archival Status Name
20231023T132628 2023-10-23 13:33:25 000000010000004E00000060
20231103T130531 2023-11-03 13:22:11 000000010000005200000081
20231103T135700 2023-11-03 14:11:59 000000010000005200000083
20231107T211340 2023-11-07 21:28:10 0000000100000052000000B1
20231107T230140 2023-11-07 23:12:41 0000000100000052000000B9
20231107T231341 2023-11-07 23:22:42 0000000100000052000000BB
20231107T234029 2023-11-07 23:48:10 0000000100000052000000C1
20231108T060002 2023-11-08 06:12:46 0000000100000052000000C5
20231108T120002 2023-11-08 12:32:50 0000000100000052000000C9
20231108T180002 2023-11-08 18:13:02 0000000100000052000000CD
20231109T000002 2023-11-09 00:26:54 0000000100000052000000D1
20231109T060002 2023-11-09 06:12:19 0000000100000052000000D5
20231109T120002 2023-11-09 12:11:19 0000000100000052000000D9
20231109T180002 2023-11-09 18:12:55 0000000100000052000000DD
20231110T000002 2023-11-10 00:25:49 0000000100000052000000E1
20231110T060001 2023-11-10 06:18:26 0000000100000052000000E5
20231110T120002 2023-11-10 12:15:52 0000000100000052000000E9
20231110T180002 2023-11-10 18:12:24 0000000100000052000000EE
20231111T000002 2023-11-11 00:27:31 0000000100000052000000F0
20231111T060002 2023-11-11 06:13:08 0000000100000052000000F2
20231111T120002 2023-11-11 12:10:25 0000000100000052000000F5
Eventually we’ll restore 20231111T120002
.
Step 4: Restore the data
First, check the data_directory
setting on the new server to see where to restore to. In my server setup, i move this to another drive
rob@new_server:~$ sudo -u postgres psql -c "show data_directory;"
could not change directory to "/home/rob": Permission denied
data_directory
----------------------------------
/mnt/postgres/postgresql/15/main
(1 row)
Stop PostgreSQL then rename the folder:
rob@pg:~$ sudo systemctl stop postgresql
rob@pg:~$ sudo systemctl status postgresql
○ postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Sat 2023-11-11 12:18:06 UTC; 4s ago
Main PID: 850 (code=exited, status=0/SUCCESS)
CPU: 3ms
Nov 10 18:11:06 pg systemd[1]: Starting PostgreSQL RDBMS...
Nov 10 18:11:06 pg systemd[1]: Finished PostgreSQL RDBMS.
Nov 11 12:18:06 pg systemd[1]: postgresql.service: Deactivated successfully.
Nov 11 12:18:06 pg systemd[1]: Stopped PostgreSQL RDBMS.
rob@pg:~$ sudo mv /mnt/postgres/postgresql/15/main /mnt/postgres/postgresql/15/main_old
We’re now ready to execute the restore procedure. We do this as the postgres
user so that we avoid permission issues. Switch to them with the sudo -i -u postgres
command. Then run the restore command. 20231111T120002
is the timestamp corresponding to the backup we want to restore:
postgres@new_server:~$ barman-cloud-restore --verbose s3://<bucket>/barman pg 20231111T120002 /mnt/postgres/postgresql/15/main
postgres@pg:~$ barman-cloud-restore --verbose s3://pg.cynexia.net-backup/barman pg 20231111T120002 /mnt/postgres/postgresql/15/main
2023-11-11 12:24:48,875 [218010] INFO: Found credentials in shared credentials file: ~/.aws/credentials
2023-11-11 12:24:49,380 [218010] INFO: Found file from backup '20231111T120002' of server 'pg': barman/pg/base/20231111T120002/data.tar.snappy
Once that completes we can inspect the data folder and see it contains the right data:
postgres@new_server:~$ ls /mnt/postgres/postgresql/15/main
PG_VERSION base pg_commit_ts pg_hba.conf pg_logical pg_notify pg_serial pg_stat pg_subtrans pg_twophase pg_xact postgresql.conf
backup_label global pg_dynshmem pg_ident.conf pg_multixact pg_replslot pg_snapshots pg_stat_tmp pg_tblspc pg_wal postgresql.auto.conf
We now need to copy the config files postgresql.conf
, pg_hba.conf
and pg_ident.conf
to the PostgreSQL installation directory. First, we’ll rename the existing files, then copy the restored ones. Do this as the postgres
user:
postgres@new_server:~$ mv /etc/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf.backup
postgres@new_server:~$ cp /mnt/postgres/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/
postgres@new_server:~$ mv /etc/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/pg_hba.conf.backup
postgres@new_server:~$ cp /mnt/postgres/postgresql/15/main/pg_hba.conf /etc/postgresql/15/main/
postgres@new_server:~$ mv /etc/postgresql/15/main/pg_ident.conf /etc/postgresql/15/main/pg_ident.conf.backup
postgres@new_server:~$ cp /mnt/postgres/postgresql/15/main/pg_ident.conf /etc/postgresql/15/main/
We need to create the recovery signal to tell PostgreSQL to initialise a recovery:
touch /mnt/postgres/postgresql/15/main/recovery.signal
Step 5: Configure WAL recovery
First, we need to disable the archive_command
to stop the server from overwriting the bucket contents. First, find the archive_mode
setting and set it to off
:
sudo sed -i '/^#archive_mode/c\archive_mode = on' /etc/postgresql/*/main/postgresql.conf
sudo sed -i 's/^archive_mode\s*=.*/archive_mode = off/' /etc/postgresql/*/main/postgresql.conf
Just to be sure, we comment out archive_command
:
sudo sed -i 's/^\(archive_command\s*=.*\)/#\1/' /etc/postgresql/*/main/postgresql.conf
We then need to set the restore_command
and recovery_target_time
in /etc/postgresql/15/main/postgresql.conf
. We’ll use the barman-cloud-wal-restore
command:
sudo sed -i '/^#restore_command/c\restore_command = '"'"'barman-cloud-wal-restore s3://pg.cynexia.net-backup/barman pg %f %p'"'" /etc/postgresql/*/main/postgresql.conf
sudo sed -i '/^#recovery_target_timeline/c\recovery_target_timeline = '"'"'latest'"'" /etc/postgresql/*/main/postgresql.conf
Step 6: Start the server
start the server:
sudo systemctl start postgresql
check the logs with sudo tail -n 100 /var/log/postgresql/postgresql-15-main.log
. Fix any configuration errors by referring to your backup configuration files.
If you get an error like this:
2023-11-11 17:48:15.594 UTC {[local]} [260249] postgres@template1 FATAL: database locale is incompatible with operating system
2023-11-11 17:48:15.594 UTC {[local]} [260249] postgres@template1 DETAIL: The database was initialized with LC_COLLATE “en_US.UTF-8”, which is not recognized by setlocale().
2023-11-11 17:48:15.594 UTC {[local]} [260249] postgres@template1 HINT: Recreate the database with another locale or install the missing locale.
You can fix this by installing the correct locale:sudo locale-gen en_US.UTF-8
sudo update-locale
You can check that the restore was successful by running a query against the database, and checking that the /mnt/postgres/postgresql/15/main/recovery.signal
file is gone.
Step 7: set up scheduled backups and WAL archiving on the new server
Follow steps 11-13 on my ‘set up backup’ post. This will re-enable WAL archiving to S3, and create a full backup on a schedule.