How to Securely Backup Your Postgres Database With Docker and Cron

Are you tired of manually backing up your Postgres database and worrying about the safety of your sensitive data? Well, worry no more! In this article, I'll walk you through how to use Docker and Cron to automatically back up your Postgres database, keeping your data safe and secure.

Here is the Docker Compose file that we'll be using to set up our PostgreSQL container and backup container:

version: '3.8'
services:
  db-postgresql:
    image: postgres:13
    restart: always
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: P@ASSW0RD654
      POSTGRES_DB: bitbucket
      PGDATA: /var/lib/postgresql/data/pgdata
      POSTGRES_SYNCHRONOUS_COMMIT: 'on'
      POSTGRES_WAL_LEVEL: 'replica'
    ports:
      - published: 7649
        target: 5432
    volumes:
      - postgresql_data:/var/lib/postgresql/data/pgdata

  backup:
    image: postgres:13
    depends_on:
      - db-postgresql
    volumes:
      - ./backup:/backup
    command: >
      bash -c "while true; do
        PGPASSWORD=$$POSTGRES_PASSWORD pg_dump -h db-postgresql -U $$POSTGRES_USER -Fc $$POSTGRES_DB > /backup/$$(date +%Y-%m-%d-%H-%M-%S).dump
        echo ""Backup done at $$(date +%Y-%m-%d_%H:%M:%S)""
        ls -1 /backup/*.dump | head -n -2 | xargs rm -f
        sleep 86400
      done"
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: P@ASSW0RD654
      POSTGRES_DB: bitbucket

volumes:
  postgresql_data:
    external: true

First things first, let's take a look at the code. We have two services defined: db-postgresql, which runs the Postgres database, and backup, which is responsible for backing up the database.

The db-postgresql service uses the official Postgres image, with version 13 specified. We're also defining some environment variables, including the database name, username, and password.

Note that we're using a strong password, so make sure to choose a secure password for your own setup!

The backup service depends on the db-postgresql service, meaning that it won't start until the database is up and running. We're using a Postgres image here as well, with version 13 specified.

Now, let's take a closer look at the backup command. We're using a Bash script to backup the database, which runs on a schedule using Cron. The script runs every 24 hours, thanks to the sleep 86400 command.

The backup script uses the pg_dump command to dump the database into a file. We're also including a timestamp in the filename, which is generated using the date command. This means that each backup will have a unique filename, making it easy to keep track of multiple backups.

We're also deleting old backups using the ls, head, and xargs commands. This ensures that we don't run out of disk space due to old backups.

Finally, we're mounting a volume to the backup service, which maps to the ./backup directory on the host machine. This means that the backup files will be stored on the host machine, rather than in the container.

And that's it! With this setup, you can rest assured that your Postgres database is being backed up regularly and securely. Whether you're working on a personal project or a production system, having a reliable backup strategy is essential for maintaining the safety and integrity of your data.

So why not give this setup a try and enjoy the peace of mind that comes with knowing your data is protected?