In this howto I will show how to backup all PostgreSQL databases, roles and permission on a server and restore them on another server on a docker.

1. Backup databases on origin server

To backup all PostgreSQL databases automatically, preserving roles and permission, you can create this cron.d script (this example is from a CentOS):

# Dump postgresql database automatically
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
40 22 * * 0 postgres /bin/bash -c 'pg_dumpall -Upostgres | gzip > "/var/backup/myexport.sql.gz"'

In order:

1. Run on at 22:40 on Sunday
2. Run as postgres user (cron is executed by root, and root can impersonate it without password)
3. Use the bash interpreter (not sh)
4. Run pg_dumpall to dump all database
5. Pipe the output to gzip to compress the SQL into a /var/backup/myexport.sql.gz file

2. Transfer on another server

Transfer the dump using ssh using something like this:

rsync -rltvz --no-o --no-g myuser@originserver:/var/backup/dumpall.sql.gz /opt/backup/myproj/data/

Use .ssh/config to store connection info.

3. Restore on a clean docker container

The following bash script will create a docker container, populating it with

set -e
echo "Remove identical container, keep data on directory"
NOW=`date +%Y-%m-%d`
# create an id based on timestamp plus MAC address 
UNIQUID=`uuidgen -t`
NAME="postgresql-myproj-$NOW-$UNIQUID"
echo "Get postgres docker image"
docker pull postgres:9.4-alpine
echo "Generate 1 password with 48 char length"
PPASS=`pwgen 48 1`
mkdir -p /opt/docker_data/$NAME
echo "Save psql password on /opt/docker_data/$NAME.pwd"
echo $PPASS > "/opt/docker_data/$NAME/psql.pwd"
echo "extract database"
mkdir -p /opt/docker_data/$NAME/psqldata
mkdir -p /opt/docker_data/$NAME/share
gunzip -c /opt/backup/myproj/data/dumpall.sql.gz > /opt/docker_data/$NAME/share/restore.out
echo "Run a clean docker"
docker run --name $NAME -e POSTGRES_PASSWORD=$PPASS -d -p 44432:5432 -v /opt/docker_data/$NAME/psqldata:/var/lib/postgresql/data -v /opt/docker_data/$NAME/share:/extshare --restart always postgres:9.4-alpine
sleep 10
echo "Restore from /extshare/restore.out using user postgres (-upostgres) the database postgres (all dbs)"
docker exec -upostgres $NAME psql -f /extshare/restore.out postgres
echo "Clear the restore.out file"
rm /opt/docker_data/$NAME/share/restore.out

In order, this script:

1. Download a postgres:9.4-alpine image (choose your version)
2. Generate a random container name postgresql-myproj-YYYY-MM-DD-random-id based on MAC address and timestamp
3. Generate a random password and save on a file
4. Generate a directory structure on host system to keep postgres file and dump outside docker
5. Create a new postgres container exposed to host on port 44432
6. Save postgres files on /opt/docker_data/$NAME/psqldata on host
7. Expose the dump file directory on /extshare on guest
8. Restore the dump using role postgres
9. Delete the dump

Resulting directory structure on host will be:

.
└── postgresql-myproj-2019-28-12-*******
    ├── psqldata [error opening dir]
    ├── psql.pwd
    └── share
        └── restore.out

Then to connect just use the same user and password of the database on origin server.

Permissions

Usually on /opt/docker_data/$NAME/psqldata/pg_hba.conf, you’ve to add a line like this:

host all all 172.17.0.1/32 md5

giving to host (reachable by 172.17.0.1 inside docker) full access to database. But the default image ship a handy, permissive entry:

host all all all md5

So you can connect without any step to the database.

Connection

If you connect to the destination server with ssh, if you cannot access the port remember to forward the PostgreSQL on .config like:

Host destinationsrv
    Hostname destinationaddr
    User mydestservuser
    IdentityFile /home/myuser/.ssh/id_rsa_destination_server
    LocalForward 44432 127.0.0.1:44432

Remember that if you haven’t a firewall, docker container will start with a permissive rule like:

0.0.0.0:44432->5432/tcp

So will be exposed and reachable using password.