gem-hunter

• Docker PG Backup
  • Overview
  • Getting the image
    • Pulling from Hub(Docker Hub)
    • Building the image locally
  • Running Services using the Image
    • Use docker-compose
  • Configuring backup with environment variables
    • Filename format
    • Backup Format
      • Using Custom Format
      • Using Directory Format
  • Backup Location
    • Backing up to S3 bucket
  • Mounting Configs
  • Restoring
    • Restore using Archive
    • Date Based Restore
      • Date
      • DateTime
  • Entrypoint
  • Credits

• A docker container that runs PostgreSQL / PostGIS backups (PostGIS is not required it will backup any PG database).
• It is primarily intended to be used with our kartoza/postgis docker image.
• By default, it will create a backup once per night (at 23h00) in a nicely ordered directory by a year / month.
• Environment variables to fine tune some backup parameters i.e.
  (e.g., hourly, every 30 minutes) using the CRON_SCHEDULE environment variable.
• Backup and restore to file or S3 environments (Tested with minio).

There are various ways to get the image onto your system:

POSTGRES_MAJOR_VERSION=18
POSTGIS_MAJOR_VERSION=3
POSTGIS_MINOR_RELEASE=6 
docker pull kartoza/pg-backup:$POSTGRES_MAJOR_VERSION-$POSTGIS_MAJOR_VERSION.${POSTGIS_MINOR_RELEASE}

We highly suggest that you use a tagged image that match the PostgreSQL image you are running i.e. (kartoza/pg-backup:18-3.6 for backing up kartoza/postgis:18-3.6 DB).

git clone https://github.com/kartoza/docker-pg-backup.git
cd docker-pg-backup
./build.sh 

The GitHub Actions produce the following images:

• kartoza/pg-backup:latest
• kartoza/pg-backup:$POSTGRES_MAJOR_VERSION-$POSTGIS_MAJOR_VERSION.${POSTGIS_MINOR_RELEASE}
• kartoza/pg-backup:$POSTGRES_MAJOR_VERSION-$POSTGIS_MAJOR_VERSION.${POSTGIS_MINOR_RELEASE}-$(date -u +%Y.%m.%d)

If there is a bug in the kartoza/pg-backup:$POSTGRES_MAJOR_VERSION-$POSTGIS_MAJOR_VERSION.${POSTGIS_MINOR_RELEASE} release, you need to update:

./github/workflows/deploy-image.yaml

and update pgbackupPatchMajorVersionBugs to yes to allow the action to update the current image otherwise it will only be updated once in the release cycle.

To create a running container do:

1. Make sure you have an env file. Use the example given

   cp .example.env .env

2. Start the services using docker compose

   docker-compose up -d 

For a full description of the environment variables available with this image look into docker-env.md

The default backup archive generated will be stored in the /backups directory (inside the container):

/backups/$(date +%Y)/$(date +%B)/${DUMPPREFIX}_${DB}.$(date +%d-%B-%Y-%H-%M).dmp

As a concrete example, with DUMPPREFIX=PG and if your postgis has DB name gis. The backup archive would be something like:

/backups/2019/February/PG_gis.17-February-2019-14-30.dmp

The filename includes hour and minute (%H-%M) to support sub-daily backups.

If you specify ARCHIVE_FILENAME instead (default value is empty). The filename will be fixed according to this prefix. Let's assume ARCHIVE_FILENAME=latest. The backup archive would be something like

/backups/latest.gis.dmp

You can use the env DUMP_ARGS to specify the dump format. The image defaults to specifying the following:

• DUMP_ARGS=-Fc Dumps a compressed archive of the database.

• DUMP_ARGS=-Fd Dumps the database into a directory format.

Note: For S3 backends, this is compressed into a tar archive.

• Directory inside the container or docker volume. or a directory mounted within the image.
• S3 Backends - cloud storage i.e. Minio

We currently use s3cmd for backing up files to S3 bucket.

For a quick start use docker-compose-s3.yml .

The image supports mounting the following configs:

• s3cfg when backing to S3 backend
• backup-cron for any custom configuration you need to specify in the file.
• backup_monitoring.sh For any custom monitoring state on database dump completion or failure.

If you need to mount s3cfg file. You can run the following:

-v /data:/settings

Where s3cfg is located in /data.

When the backend is S3, files are downloaded (.gz or .dir.tar.gz) locally and then restore can happen into an empty database.

Set the following environment variables:

• TARGET_DB The db name to restore
• WITH_POSTGIS Kartoza specific, to generate POSTGIS extension along with the restore process
• TARGET_ARCHIVE The full path of the archive to restore.
• STORAGE_BACKEND This will determine where the archive is fetched from with either it being downloaded and processed (S3 backends) or local file backups.

Note: The restore script will exit if you try to restore into an existing TARGET_DB.

It is generally a good practice to restore into an empty new database and then manually drop and rename the databases.

After setting up the environment variables in the docker-compose.yml and running it, you can execute the restore by running:

docker-compose exec dbbackups /backup-scripts/restore.sh

You can restore a specific backup based on time or date it was generated using the env

• TARGET_ARCHIVE_DATE.

Example:

Date only: "2023-03-24" - will restore the latest backup of that day.

• TARGET_ARCHIVE_DATETIME. Date and time: "2023-03-24-14-30" - will restore the backup from 14:30 on that day

The image supports running multiple entrypoints. The following are supported

1. backup - By default this will run the backup script either as a cron job or a once off depending on the environment variable configuration.

docker run -it -e ENTRYPOINT_START=backup kartoza/pg-backup:${TAG:-18-3.6}

2. restore - This will allow you to run the restore script without executing into the container first.

docker run -it -e ENTRYPOINT_START=restore kartoza/pg-backup:${TAG:-18-3.6}

3. shell - This will allow you to execute into the container and run interactive commands. This is just for testing purposes mainly.

Note: You are still required to pass other additional env params to allow you entrypoint command to be executed correctly i.e. backup requires the DB params etc

Tim Sutton (tim@kartoza.com)

Admire Nyakudya (addloe@gmail.com)

Rizky Maulana