Postgres backup and restore
cron daily backup of db
https://gist.github.com/linuxkathirvel/90771e9d658195fa59e0f0b921f7e22e I ended up using this strategy to create a daily backup job.
# /etc/cron.d/postgres-cron-backup
# 1400UTC (1500UK Summer Time). nasa download happens at 0100 and 1300 UTC
0 14 * * * dave /home/dave/source/fire-map-infra/postgres-cron-backup.sh
then
#!/bin/bash
# /home/dave/source/fire-map-infra/postgres-cron-backup.sh
BACKUP_DIR="/home/dave/"
FILE_NAME=$BACKUP_DIR`date +%d-%m-%Y-%I-%M-%S-%p`.sql
# custom compressed file format which pg_restore can restore from
pg_dump -Fc -U postgres nasafiremap > $FILE_NAME
As we’re not using a password, we need to trust local connections
# /etc/postgres/12/main/pg_hba.conf
# sudo service postgresql restart
# Database administrative login by Unix domain socket
#local all postgres md5
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# opening up to outside on port 5432
host all all all md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
It would be very nice to backup to a shared drive on Azure so can keep backups between vm updates. See notes on smb mounting in 2021-09-17-azure file share.
backup and restore db for a new deployment
I like deployments to be fully automated. So when I run infra.sh to create a new PHP vm I want the old db to be backed up and restored on to the new vm
echo "backing up database from old vm on $resourcegroup"
pgpassword=$(<../secrets/pgpassword.txt)
file_name=`date +%d-%m-%Y-%I-%M-%S-%p`.backup
pg_dump -Fc -d postgres://postgres:${pgpassword}@$resourcegroup.westeurope.cloudapp.azure.com:5432/nasafiremap > $file_name
echo "restoring to new vm on $rg"
export PGPASSWORD=$pgpassword
pg_restore -h $rg.westeurope.cloudapp.azure.com -U postgres -v -d "nasafiremap" $file_name
This pulls the backup file down to my local dev machine, which is useful for a snapshot offsite backup