Scheduled Backups for PostgreSQL Databases
PostgreSQL is an open-source object-relational database management system.
This document shows how to create scheduled backups of a PostgreSQL database on Opalstack.
The examples use
mydb as the database name,
mydbuser as the database user name, and
mydbpassword as the database user password - be sure to replace those values with the names of your own database, user, and password.
Log in to a SSH session on your Opalstack server.
Create the files and directories used by your scheduled MariaDB backups:
export DBNAME=mydb mkdir -m 700 -p ~/.local/bin mkdir -m 700 -p ~/.local/etc/psql_backups mkdir -m 700 -p ~/backups/psql touch ~/.pgpass chmod 600 ~/.pgpass touch ~/.local/bin/backup_$DBNAME chmod 700 ~/.local/bin/backup_$DBNAME
Edit the database credentials file named
~/.pgpassand add the following line, replacing
mydbpasswordwith your database username and password.
Edit the script
~/.local/bin/backup_mydband add the following lines that will run your backup:
#!/bin/bash export DBNAME=mydb export DBUSER=mydbuser /bin/pg_dump -b -Fp -U $DBUSER $DBNAME \ > $HOME/backups/psql/$DBNAME-$(date +\%Y\%m\%d\%H\%M).sql \ 2>> $HOME/backups/psql/$DBNAME.log
crontab -eand create a crontab entry to schedule the backup. The following example will schedule the backup to run daily at 3:30 AM UTC (server time):
30 3 * * * $HOME/.local/bin/backup_mydb
Please see the following pages for general help with cron jobs:
Once the above steps are complete, your backup will run at the schedule time to create a backup of your PostgreSQL database in
~/backups/psql on the server.