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.