/postgresql-backup-restore

Primary LanguageShellMIT LicenseMIT

postgresql-backup-restore

Service to backup and/or restore a PostgreSQL database to/from S3

How to use it

  1. Create an S3 bucket to hold your backups
  2. Turn versioning on for that bucket
  3. Supply all appropriate environment variables
  4. Run a backup and check your bucket for that backup

Environment variables

MODE Valid values: backup, restore

DB_HOST hostname of the database server

DB_NAME name of the database

DB_OPTIONS optional arguments to supply to the backup or restore commands

DB_ROOTPASSWORD password for the DB_ROOTUSER

DB_ROOTUSER database administrative user, typically "postgres" for PostgreSQL databases

DB_USERPASSWORD password for the DB_USER

DB_USER user that accesses the database (PostgreSQL "role")

AWS_ACCESS_KEY used for S3 interactions

AWS_SECRET_KEY used for S3 interactions

S3_BUCKET e.g., s3://database-backups NOTE: no trailing slash

It's recommended that your S3 bucket have versioning turned on.

Docker Hub

This image is built automatically on Docker Hub as silintl/postgresql-backup-restore

Playing with it locally

You'll need Docker, Docker Compose, and Make.

  1. Copy local.env.dist to local.env.
  2. Edit local.env to supply values for the variables.
  3. Ensure you have a gz dump in your S3 bucket to be used for testing. A test database is provided as part of this project in the test folder. You can copy it to S3 as follows:
  • aws s3 cp test/world.sql.gz ${S3_BUCKET}/world.sql.gz
  1. make db # creates the Postgres DB server
  2. make restore # restores the DB dump file
  3. docker ps -a # get the Container ID of the exited restore container
  4. docker logs <containerID> # review the restoration log messages
  5. make backup # create a new DB dump file
  6. docker ps -a # get the Container ID of the exited backup container
  7. docker logs <containerID> # review the backup log messages
  8. make restore # restore the DB dump file from the new backup
  9. docker ps -a # get the Container ID of the exited restore container
  10. docker logs <containerID> # review the restoration log messages
  11. make clean # remove containers and network
  12. docker volume ls # find the volume ID of the Postgres data container
  13. docker volume rm <volumeID> # remove the data volume
  14. docker images # list existing images
  15. docker image rm <imageID ...> # remove images no longer needed