Backing up PostgreSQL in Docker

So your new shiny project is up and running, everything is deployed automatically and running smoothly. A few weeks in and you get this call: "Yeah we accidentally deleted half our data from the shiny new app, help"

Well ... bummer, there are no backups of the database. You're basically screwed.

Enter autopostgresqlbackup

There is this nice project called autopostgresqlbackup and I usually install it on database servers via apt, configure it and it just runs daily. With docker projects, the setup could be a bit more complex if you start adding it into you Dockerfile and usually this isn't necessary.

Checking on dockerhub reveals a few projects but the one most maintained is rogersik/autopostgresqlbackup unfortunately the documentation is non existent on docker-hub. The documentation for it can be found on Roger Sikorski's Git server.

Docker-compose

Adding auto backup capabilities is as easy as adding these few lines to your docker-compose

  autopgbackup:
    image: rogersik/autopostgresqlbackup:latest
    environment:
      - DBHOST=db
      - USERNAME=${POSTGRES_USER}
      - PASSWORD=${POSTGRES_PASSWORD}
      - CRON_LOG_LEVEL=0
      - CRON_SCHEDULE=5 4 * * *
      - LATEST=yes

Here for reference a full docker-compose file.

version: '3.2'
services:
  db:
    image: postgres:13
    volumes:
      - /opt/docker/foobar/db:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - PGUSER=${POSTGRES_USER}
      - PGHOST=db
      - PGPASSWORD=${POSTGRES_PASSWORD}
      - HISTFILE=/var/lib/postgresql/data/.bash_history
      - PSQL_HISTFILE=/var/lib/postgresql/data/.psql_history
  autopgbackup:
    image: rogersik/autopostgresqlbackup:latest
    environment:
      - DBHOST=db
      - USERNAME=${POSTGRES_USER}
      - PASSWORD=${POSTGRES_PASSWORD}
      - CRON_LOG_LEVEL=0
      - CRON_SCHEDULE=5 4 * * *
      - LATEST=yes
    volumes:
     - /opt/docker/foobar/db_dumps:/backups
     - /etc/localtime:/etc/localtime:ro
    depends_on:
      - db
    links:
      - db

You can set the backup time via ENV variables and it usually just works out of the box. Just make sure to use correct credentials, or even better work with ENV vars to not have your credentials in the docker-compose file and ensure that the correct credentials are used for both, postgresql and autopostgresqlbackup.

Drawbacks

Of course this is not perfect, having this run daily means that you might loose a full day if you need to restore from these backups. While there are more sophisticated ways to have better backups with point-in-time recovery, this version at least ensures you have at least a backup where in most cases users could live with a minimal data loss. Also you have automatically rotating backups (daily, weekly, monthly) which helps you go back in time if need be.