💾 Archived View for chirale.org › 2019-12-29_6264.gmi captured on 2024-05-12 at 15:06:28. Gemini links have been rewritten to link to archived content
-=-=-=-=-=-=-
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.
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 all.sh django2gmi.sh processing README.md wp2gmi.sh all.sh django2gmi.sh processing README.md wp2gmi.sh 0 postgres /bin/bash -c 'pg_dumpall -Upostgres | gzip > "/var/backup/myexport.sql.gz"'
In order:
Run on at 22:40 on Sunday Run as postgres user (cron is executed by root, and root can impersonate it without password) Use the bash interpreter (not sh) Run pg_dumpall to dump all database Pipe the output to gzip to compress the SQL into a /var/backup/myexport.sql.gz file
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.
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: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: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:
Download a postgres:4-alpine image (choose your version) Generate a random container name postgresql-myproj-YYYY-MM-DD-random-id based on MAC address and timestamp Generate a random password and save on a file Generate a directory structure on host system to keep postgres file and dump outside docker Create a new postgres container exposed to host on port 44432 Save postgres files on /opt/docker_data/$NAME/psqldata on host Expose the dump file directory on /extshare on guest Restore the dump using role postgres 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.
Usually on /opt/docker_data/$NAME/psqldata/pg_hba.conf, you’ve to add a line like this:
host all all 1/32 md5
giving to host (reachable by 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.
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 1:44432
Remember that if you haven’t a firewall, docker container will start with a permissive rule like:
0:44432->5432/tcp
So will be exposed and reachable using password.
https://web.archive.org/web/20191229000000*/https://crontab.guru/#40_22_*_*_0
https://web.archive.org/web/20191229000000*/https://en.wikipedia.org/wiki/Secure_Shell
https://web.archive.org/web/20191229000000*/https://hub.docker.com/_/postgres