How to Backup and Upgrade your database with Kamal

It's essential to back up your database externally in case of failure. One advantage of using Kamal is that you're not locked into a specific hosting provider, which greatly simplifies migration.

Even if your database files are stored on a db accessory, you can't just copy the folder to another machine. You need to create a proper database dump using the command pg_dump, which ensures consistent backups, even during active usage.

In my setup, I'll store the dump on Object Storage from Scaleway, which is S3-compatible. But you can use any storage option, even a basic server with rsync. Just make sure it's not the same machine running your database.

To automate the process, I'm using the Docker image from eeshugerman/postgres-backup-s3 to both create and upload the dump.

ℹ️ You might be not comfortable using third-party scripts with your database and S3 credentials, and in this case I suggest you to write your own Bash scripts. It's just a combination of simple commands run in a crontab.

How to backup and restore your database?

At this point, I'll assume your app is up and running on your server with Kamal.

Go ahead and create your S3 bucket with any S3-compatible provider, then add your credentials to .kamal/secrets.

Add the s3_backup accessory as shown below, and adjust the environment variables to fit your setup:

accessories:
  # ...
  db:
    image: postgres:16
    host: 192.168.0.1
    env:
      secret:
        - POSTGRES_DB
        - POSTGRES_USER
        - POSTGRES_PASSWORD
    directories:
      - data:/var/lib/postgresql/data

  s3_backup:
    image: eeshugerman/postgres-backup-s3:16
    host: 192.168.0.1
    env:
      clear:
        SCHEDULE: '@daily'
        BACKUP_KEEP_DAYS: 30
        S3_REGION: your-s3-region
        S3_BUCKET: your-s3-bucket
        S3_PREFIX: backups
        S3_ENDPOINT: https://your-s3-endpoint # optional, for non-AWS S3-compatible storage provider
        POSTGRES_HOST: my_awesome_app-db # This will use the kamal internal Docker network
        POSTGRES_DATABASE: my_awesome_app_production
        POSTGRES_USER: my_awesome_app
      secret:
        - POSTGRES_PASSWORD
        - S3_ACCESS_KEY_ID
        - S3_SECRET_ACCESS_KEY

You can run this accessory with the following command:

$ kamal accessory boot s3_backup

💾 This will automatically back up your database every day at midnight to your configured S3 bucket.

Running commands manually

You can also trigger a backup on demand:

$ kamal accessory exec s3_backup "sh backup.sh"

Or restore the latest backup with:

$ kamal accessory exec s3_backup "sh restore.sh"

This is especially handy when upgrading your database or migrating away from a PaaS provider.

How to upgrade your database to a major version?

Unfortunately, upgrading from PostgreSQL 16 to 17 isn't as simple as changing the image version of the container. The data stored in your data directory won't be compatible, and trying to do so will result in errors.

The first step is to put your server in maintenance, it will be soon available in Kamal natively thanks to this PR but for now, stopping the app is the safest approach:

$ kamal app stop

This will prevent writes during the migration between the backup on PG 16.x and the restore on PG 17.x.

Generate a final backup with the latest state of your data:

$ kamal accessory exec s3_backup "sh backup.sh"

Modify the db accessory configuration in your config/deploy.yml:

accessories:
  # ...
  db:
-   image: postgres:16
+   image: postgres:17
    host: 51.15.199.102
    env:
      secret:
        - POSTGRES_DB
        - POSTGRES_USER
        - POSTGRES_PASSWORD
    directories:
-     - data:/var/lib/postgresql/data
+     - data_17:/var/lib/postgresql/data

This ensures a clean new data volume for the new major version.

Reboot the database accessory to use the updated image and directory:

$ kamal accessories reboot db

Now restore the backup into the new Postgres 17 instance:

$ kamal accessory exec s3_backup "sh restore.sh"

Finally, bring your app back online:

$ kamal app boot

You should now have your application running with the latest PostgreSQL version and all your data intact!

You can also remove the old data directory to free up some disk space on your server. The previous data lives in /root/data, while the new PostgreSQL 17 data is stored in /root/data_17.

Conclusion

I'll admit, it's a bit more involved than simply clicking a button on your favorite PaaS. But honestly, it's not that much harder.

And now, with this setup in place, you have everything you need to confidently migrate away from a PaaS and take full control of your infrastructure and reduce your costs.

But that’s a topic for another blog post. 🚀

How to migrate your database from PaaS to Kamal

If you're looking to migrate to Kamal, I've already written several blog posts that can guide you through the process:

1. Easy: Perfect for side/small projects: How to deploy Rails with Kamal and SSL certificate on any VPS
2. Medium: Perfect for most projects: How to deploy Rails with Kamal, PostgreSQL, Sidekiq and Backups on a single host
3. Expert: Perfect for big projects: How to Deploy and Scale your Rails application with Kamal