Instructions/Script to backup and restore PostgresQL databases from docker containers #1

something similar to

docker exec -i pg_container_name /bin/bash -c "PGPASSWORD=pg_password psql --username pg_username database_name" < /path/on/your/machine/dump.sql

something similar to ``` docker exec -i pg_container_name /bin/bash -c "PGPASSWORD=pg_password psql --username pg_username database_name" < /path/on/your/machine/dump.sql ```

Approach that I favor:

Import:

Start container, mounting gzip'ed file at /backup/export_name
within container exec gzip --decompress --stdout --keep /backup/export_name | pgsql ${USERNAME} ${DATABASE} ${PASSWORD}

Include an option for the started container to have volumes attached.

Note: The code above is just an example.

This doesn't need to be a bash script necessarily, it could be just a set of commands to run. It could even be based on a set of temporary environment variables.

Approach that I favor: Import: 1. Start container, mounting gzip'ed file at /backup/export_name 1. within container `exec gzip --decompress --stdout --keep /backup/export_name | pgsql ${USERNAME} ${DATABASE} ${PASSWORD}` Include an option for the started container to have volumes attached. Note: The code above is just an example. This doesn't need to be a bash script necessarily, it could be just a set of commands to run. It could even be based on a set of temporary environment variables.

EXPORT

To dump the database, run the following within the database container to create the backup:

pg_dumpall | gzip - > $(date -I)_backup.sql.gz

outside the database container, run the following to export the backup

docker cp ${CONTAINER_NAME}:$(date -I)_backup.sql.gz ./

# EXPORT To dump the database, run the following within the database container to create the backup: ```bash pg_dumpall | gzip - > $(date -I)_backup.sql.gz ``` outside the database container, run the following to export the backup ```bash docker cp ${CONTAINER_NAME}:$(date -I)_backup.sql.gz ./ ```

IMPORT

Start container and do one of the following

Import the backup when starting.
Start the container and then copy the file over.

Adding a volume to docker container

This involves either adding the backup file to a volume in docker compose or restarting the container with docker stop and docker start -v ...

`docker compose`

Add to the docker-compose.yaml file

volumes:
  - ./backup.sql.gz:/backup.sql.gz

`Docker start`

when starting the postgres container with docker

TODO: add some details here.

Copying the file over

docker cp ./${date_of_backup}_backup.sql.gz ${CONTAINER_NAME}:/${date_of_backup}_backup.sql.gz

Once the file is accessible in the container:

gzip -dk ${date_of_backup}_backup.sql.gz
psql -f ${date_of_backup}_backup.sql

Note that the gzip -k option will keep the original compressed file instead of getting rid of it.

To clean up we would do:

rm ${date_of_backup}_backup.sql*

# IMPORT Start container and do one of the following - Import the backup when starting. - Start the container and then copy the file over. ## Adding a volume to docker container This involves either adding the backup file to a volume in docker compose or restarting the container with `docker stop` and `docker start -v ...` ### `docker compose` Add to the docker-compose.yaml file ```yaml volumes: - ./backup.sql.gz:/backup.sql.gz ``` ### `Docker start` when starting the postgres container with docker TODO: add some details here. ## Copying the file over ```bash docker cp ./${date_of_backup}_backup.sql.gz ${CONTAINER_NAME}:/${date_of_backup}_backup.sql.gz ``` ## Once the file is accessible in the container: ```bash gzip -dk ${date_of_backup}_backup.sql.gz psql -f ${date_of_backup}_backup.sql ``` Note that the `gzip -k` option will keep the original compressed file instead of getting rid of it. To clean up we would do: ```bash rm ${date_of_backup}_backup.sql* ```

How to turn this into a script or better instructions?

Used this successfully to restore AACT.

Labels Milestones