How to deal with PostgreSQL Backups using Kamal
What is Kamal
Developed by 37signals (creators of Ruby on Rails, Basecamp and Hey), Kamal is a modern evolution of Capistrano for the world of containerised applications.
In addition to its hassle-free deployment on servers, whether virtual machines or bare metal, Kamal is known for its zero downtime deployments and rolling restarts. This ensures continuous service availability.
Originally built for Ruby on Rails, Kamal is versatile enough for any containerised workload. Written in Ruby, using YAML for configuration and Bash for Docker commands, Kamal is simple and efficient.
It avoids the complexity of tools like Kubernetes.
Its name is inspired by the ancient navigational instrument. It reflects its role in guiding different deployment strategies using familiar tools like Bash and Docker.
Prerequisites
For the purposes of this article, I'm assuming that you have a Kamal deployment for your application. I will be covering one in my blog in the near future.
I'm also assuming that you already have an S3-compatible object storage bucket. This could be Amazon S3, DigitalOcean Spaces, Scaleway Object Storage or others...
I use Scaleway Object Storage which is S3 compliant.
What's the concept
The approach I will describe uses the postgres-backup-s3 project, which is a combination of postgres-backup-s3 and postgres-restore-s3.
In short we're creating backups using another kamal accessory resource and pushing the backup to an S3 bucket.
Later on, the created backups can be restored using the same accessory service.
Create database backups and export to S3
Let's start with creating backups. Assuming you have a working Kamal deployment with a database accessory like mine:
accessories:
db:
image: postgres:14.0
host: 10.0.1.10
port: 5432
env:
secret:
- POSTGRES_USER
- POSTGRES_PASSWORD
- POSTGRES_DB
- PGPASSWORD
directories:
- /mnt/db-storage-01/psql-data:/var/lib/postgresql/data
We can add the configuration for creating backups to the accessories block
db_backup:
image: eeshugerman/postgres-backup-s3:15
host: 10.0.1.10
env:
clear:
SCHEDULE: '@daily' # optional
BACKUP_KEEP_DAYS: 7 # optional
S3_REGION: region
S3_ACCESS_KEY_ID: key
S3_SECRET_ACCESS_KEY: secret
S3_BUCKET: my-bucket
S3_ENDPOINT: https://bucket_endpoint
S3_PREFIX: backups
POSTGRES_HOST: 10.0.1.10
POSTGRES_DATABASE: dbname
POSTGRES_USER: user
secret:
- POSTGRES_PASSWORD
- PASSPHRASE # optional for encrypted backups
Let me explain the configuration.
You'll define an accessory called db_backup using the docker image eeshugerman/postgres-backup-s3:15. The ending 15 defines the PostgreSQL version in use.
The SCHEDULE variable defines a GOcron based schedule for creating backups. The example uses @daily which creates daily backups at midnight. Omitting this value results in no schedule.
If you pass BACKUP_KEEP_DAYS, the tool will take care of cleaning up old backups. I will use a lifecycle rule for my terraform managed bucket to take care of old backups.
If PASSPHRASE is specified, the backup will be encrypted using GPG.
All S3_* variables are needed to access the previously created object storage bucket. Most likely you will want to pass this information using an .env file as recommended in the kamal documentation.
The values required for the POSTGRES_* variables are most likely already in use and just need to be passed a second time. Unfortunately there is no way to reuse already defined variables.
Configuration hints
If you're using S3-compatible object storage, as I am with Scaleway, you'll need to set the S3_ENDPOINT variable. You'll get the endpoint from your provider after you create a bucket. In this case the bucket name will also be different. Basically the endpoint without the protocol part. You can read why in my other article in case you're getting some certificate verify failed issues.
You don't need the endpoint if you are using AWS S3. There you can also use the bucket name as is.
I use a bastion/jump host to connect to hosts on my internal network. That's why the host IP is 10.0.1.10. This may be a public IP for you.
Make sure that your database version and that of postgres-backup-s3 match.
Getting started with database backups
After defining the kamal accessory for backups, you can start the accessory with
kamal accessory boot db_backup -d <deployment-name>
Then you want to check the status
kamal accessory details -d <deployment-name>
You should see something like this
➜ medone-core git:(main) ✗ kamal accessory details db_backup -d nonprod
INFO [ac01a1a7] Running docker ps --filter label=service=med1-core-db_backup on 10.0.1.10
INFO [ac01a1a7] Finished in 2.809 seconds with exit status 0 (successful).
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
00bfbeb0e00f eeshugerman/postgres-backup-s3:14 "sh run.sh" About a minute ago Up About a minute med1-core-db_backup
Depending on the defined schedule the first backup will take some time to be taken for the first time. See GOcron schedules for what to expect.
Changing the accessory configuration
If you change the backup schedule or any other configuration parameter, you must ensure that the environment variables are updated. Execute
kamal envify -d <deployment-name>
before rebooting the accessory to load the new configuration
kamal accessory reboot db_backup -d <deployment-name>
Running on-demand backups
You can run on-demand backups using the following command
kamal accessory exec db_backup "sh backup.sh"
This will spin up a new container and run the database backup. If you want to reuse the already running db_backup container, you can do so by adding the --reuse parameter
kamal accessory exec db_backup "sh backup.sh" -d <deployment-name> --reuse
The log output will show
Launching command from existing container...
If you want to see the output generated by the backup.sh script, you must run the command in an interactive shell using --interactive.
kamal accessory exec db_backup "sh backup.sh" -d <deployment-name> --reuse --interactive
showing the output created in the container
Creating backup of med1_core_production database...
Uploading backup to med1-app-nonprod-med1-io-backup-01.s3.nl-ams.scw.cloud...
upload: ./db.dump to s3://med1-app-nonprod-med1-io-backup-01.s3.nl-ams.scw.cloud/backups/med1_core_production_2024-01-08T16:30:43.dump
Backup complete.
Connection to 10.0.1.10 closed.
Restoring a backup
To restore a previously created backup, run the following command
kamal accessory exec db_backup "sh restore.sh" -d <deployment-name>
No further changes to the accessory configuration are required. This will restore the latest backup found in the defined bucket.
NOTE: The project maintainer explains that if you have more than 1000 backups, the latest backup won't be restored because only one S3 ls command is used to determine the backup to restore.
To restore a specific backup, use
kamal accessory exec db_backup "sh restore.sh <timestamp>" -d <deployment-name>
The format of the timestamp is %Y-%m-%dT%H:%M:%S, for example 2024-01-08T16:46:53.
You may want to check the files in your bucket for the correct timestamp so that you can restore them.
Caveat for restoring from S3-compatible object storage
Restoring the latest backup using any S3-compatible object storage fails because the postgres-backup-s3 container internally uses the aws s3 ls command, which is not supported. At least not for Scaleway object storage. I have seen similar behaviour with Active Storage using object storage
➜ medone-core git:(main) ✗ kamal accessory exec db_backup "sh restore.sh" -d <deployment-name> --reuse --interactive
An error occurred (NoSuchKey) when calling the ListObjectsV2 operation: The specified key does not exist.
Fetching backup from S3...
fatal error: An error occurred (404) when calling the HeadObject operation: Key "backups/" does not exist
Restoring from backup...
pg_restore: error: could not open input file "db.dump": No such file or directory
rm: can't remove 'db.dump': No such file or directory
Restore complete.
Connection to 10.0.1.10 closed.
You need to run the restore command with a specific timestamp. This works perfectly well. 🙌
➜ medone-core git:(main) ✗ kamal accessory exec db_backup "sh restore.sh 2024-01-08T16:46:53" -d <deployment-name> --reuse --interactive
Fetching backup from S3...
download: s3://<bucket-name>/backups/med1_core_production_2024-01-08T16:46:53.dump to ./db.dump
Restoring from backup...
Restore complete.
Connection to 10.0.1.10 closed.
Going further
While there are WAL (write-ahead logging) based approaches that essentially provide point-in-time recovery, the process described is perfectly adequate for my needs. For the majority of my applications I use a daily backup schedule.
For reference, here are two projects that I might look at in the future
This is it for now! 🏁
Having database backups and restores in place makes me happy and sleep well.
I hope you found this article useful and that you learned something new.
If you have any questions or feedback, didn't understand something, or found a mistake, please send me an email or drop me a note on twitter / x. I look forward to hearing from you.
Please subscribe to my blog if you'd like to receive future articles directly in your email. If you're already a subscriber, thank you.
Bonus section
While researching database backup issues, I came across an interesting approach that I'd like to share with you.
Use the following command from your local machine (or deployment host) to create an SQL dump of the defined database and save it locally in dump.sql.
kamal accessory exec db --reuse --interactive --quiet "pg_dump -h 10.0.1.10 -d database_name -U your_user" -d nonprod > dump.sql
Replace 10.0.1.10 with the IP of your database host.
You will need to set the PGPASSWORD environment variable on the database host which will be picked up by pg_dump.
Such a simple SQL dump can be restored using the files parameter provided for your database accessory.
Your database plugin configuration might look like this
accessories:
db:
image: postgres:16
host: myhost
port: 5432
env:
clear:
POSTGRES_USER: 'rails'
POSTGRES_DB: 'db'
secret:
- POSTGRES_PASSWORD
files:
- dump.sql:/docker-entrypoint-initdb.d/setup.sql
directories:
- data:/var/lib/postgresql/data
The interesting bit is to pass the local sql dump dump.sql to be used by the postgres docker container as the init file to bootstrap your database.
files:
- dump.sql:/docker-entrypoint-initdb.d/setup.sql
This allows you to remove the current database
# Be sure to execute this, as it will completely destroy your current database
kamal accessory remove db
and start a new database using the SQL dump for the initial data
# this will upload dump.sql and bootstrap the new database with it
kamal accessory boot db