Archives 2023/11

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…

I was surprised not to find many up to date instructions on this. I have a few basic requirements:

• Back up daily to an S3 bucket
• Keep a certain number of backups
• Run automatically, preferably using systemd not cron as it’s easier to set up and troubleshoot
• Use a user with least privileges on the database, operating system, and in AWS/S3
• Send the results of each backup activity to healthchecks.io

After a bit of playing around, I decided to use Barman for the backups - it’s significantly easier to configure and use than pgBackRest and has native support for backing up to S3, point-in-time restore, and more. The major downside compared to, say, running pg_dump every night, is that it requires an identical setup to restore to - identical hardware and PostgreSQL version. Least privileges in the database is tricky - to be able to back up things like roles, the account basically needs full access to all schemas. The Barman documentation says that it should run as the same user as PostgresQL, postgres.