Database Backup
Shell Script
We use the script backup-database.sh to dump the
database and upload it into an S3-compatible object storage cloud using
rclone. This script needs the database password
as an environment variable. The key must be PGPASSWORD. The variable can live
in .envrc as such:
export PGPASSWORD=db-password
Commands
To create a database dump run:
pg_dump -Fc --no-acl mataroa -h localhost -U mataroa -f /home/deploy/mataroa.dump -W
To restore a database dump run:
pg_restore --disable-triggers -j 4 -v -h localhost -cO --if-exists -d mataroa -U mataroa -W mataroa.dump
Initialise and configure backup script
cp /var/www/mataroa/backup-database.sh /home/deploy/
Setup rclone
- Create bucket on Scaleway or any other S3-compatible object storage.
- Find bucket URL.
- On Scaleway: it's on Bucket Settings.
- Acquire IAM Access Key ID and Secret Key.
- On Scaleway: IAM -> Applications -> Project default -> API Keys
rclone config