Page tree
Skip to end of metadata
Go to start of metadata

There are several approaches to backing up and restoring Postgres databases running in a docker container.  The process described here assumes you are using the docker-compose templates provided by the SSDT Utils package.  

The backup scripts connect to the database container and issue a pg_dump to create a database backup.  pg_dump will create a consistent backup even if the applications are running and users are accessing it concurrently.  The backups will be compressed using the standard gzip utility and placed in ./backup of the current project. The files will be named {IRN}{projectname}-{dbname}.{timestamp}.backup.gz (For example 99999Test1-usasdb.2019-11-15-08-10-03.backup.gz)



For permanent backup, the entire project directory including the ./backup folder should be backed up using your sites normal backup strategy. 

Step-by-step guide


  1. Set the working directory to the compose project: 

    cd /data/prod/sampletown
  2. The database containers must be running.  You can ensure they're running with: 

    docker-compose start usasdb uspsdb
  3. Execute the script to backup the USAS and/or USPS databases: 

    /ssdt/scripts/backup-usas.sh
    /ssdt/scripts/backup-usps.sh

    These scripts will execute a pg_dump command in the database container and pipe/compress the backup to ./backup.  Each backup will be named with the IRN, project_name, database name and timestamp. For example, the backup of the usasdb in an instance called test one will be named 99999test1t-usasdb.2019-01-24-01-53-07.backup.gz

Schedule backups/crontab

Sites may want to set up a cron job for regularly scheduled backups..  See Linux+Cheat+Sheet/Crontab for detailed information on setting up cron jobs.  This snippet of a cron job backs up all usas databases for projects in the /data/test/ subdirectories at 1:50 a.m., and writes a log in the /data/docker-logs directory which includes the date and time.

50 1 * * *  /ssdt/scripts/exec-all-projects.sh /data/test /ssdt/scripts/backup-usas.sh 2>&1 >> /data/docker-logs/backup-usas-`date +\%Y-\%m-\%d-\%H:\%M`.log

Delete old backups and old log files

Each site determines backup file retention times.  This is an EXAMPLE of a script that deletes backup files in /data/test that are older than 7 days.  It also deletes log files in /data/docker-logs that are older than 7 days. We recommend testing any script on non-production systems before moving them to production. 

This is just an example of one possible way to delete files. Since deleting files is a destructive process, each site should thoroughly test any script written to make sure it is correct.
#deletefiles.sh
echo "-----------------------------------------------------------------"
echo "Delete backup files in /data/test that are older than  7 days"
echo "Files to delete"
find /data/test -type f -mtime +7 -name '*.backup.gz'
echo "deleting files"
find /data/test -type f -mtime +7 -name '*.backup.gz' -delete
echo "-----------------------------------------------------------------"
echo "Verify delete backup files - should return 0 files"
echo "-----------------------------------------------------------------"
find /data/test -type f -mtime +7 -name '*.backup.gz'
echo "-----------------------------------------------------------------"
echo "Delete log files in /data/docker-logs that are older than 7 days"
echo "-----------------------------------------------------------------"
echo "Files to delete"
find /data/docker-logs -maxdepth 1 -type f -mtime +7 -name '*.log'
echo "deleting files"
find /data/docker-logs -maxdepth 1 -type f -mtime +7 -name '*.log' -delete
echo "verify no files remain"
find /data/docker-logs -maxdepth 1 -type f -mtime +7 -name '*.log'
echo "End of Script"
echo "-----------------------------------------------------------------"

This can be set as a cron job to run on a regular basis and write the results to a log file.  Example cron job setting that runs the script (located in /data/docker-logs)  at 3:00 a.m. daily and writes the results of the delete to a file in /data/docker-logs/ .

0 3 * * * /data/docker-logs/deletefiles.sh 2>&1 > /data/docker-logs/deletefiles-`date +\%Y-\%m-\%d-\%H:\%M`.log

Restoring a database backup

See here for details on restoring a database backup.