This post will help you with easy, cost-effective and secure way to backup your PostgreSQL database to cloud.
Login as postgres user on the console (tmux is recommended).
Create .pgpass file to access database without entering credentials manually.
$ nano .pgpass
Enter the credentials in the following format. Making changes according to the host if necessary.
Set permissions to the file. login_username will be postgres, if you are logged in as that user.
$ chmod 600 .pgpass $ chown login_username:login_username .pgpass
Set the environment variable in the .profile file. Where /var/lib/pgsql is the home directory for the user postgres in my setup.
$ nano .profile export PGPASSFILE='/var/lib/pgsql/.pgpass'
Test the login. Making changes according to host if necessary.
$ psql -h localhost -U dbusername dbname
Restic is a free open-source backup program available for multiple distributions. Download the one appropriate to your operating system, extract it, move it to /var/lib/pgsql/bin/ (home directory of user postgres) as restic, make it executable and set the PATH to that folder.
$ bzip2 -d restic_*_linux_amd64.bz2 $ mv restic_*_linux_amd64 /usr/bin/restic $ chmod u+x /var/lib/pgsql/bin/restic $ nano .profile export PATH=$PATH:/var/lib/pgsql/bin
Creat restic-pw.txt file with password for encrypting the Postgres database dump.
$ nano restic-pw.txt [Enter the password for encryption] $ chmod 600 restic-pw.txt $ chown postgres:postgres restic-pw.txt
Backblaze is a cloud storage service, offering easy encrypted end-to-end cloud backup solutions for home and business users.
For backing up our PostgreSQL database to their cloud, we will use their B2 service for backing up our data via their API. First 10GB of storage on B2 is free as of this writing, but charges may apply when we download those data and so read the pricing details carefully.
Create a B2 account to get the following credentials and export them in .bashrc.
$ nano .bashrc export B2_ACCOUNT_ID="XXXXX" export B2_ACCOUNT_KEY="XXXXX" export RESTIC_REPOSITORY="b2:bucket-name" export RESTIC_PASSWORD_FILE="restic-pw.txt"
Setup a cronjob to create a PostgresSQL database dump using pg_dump tool, here it is done every day at 23:00 hours and is upload to B2 at 00:00 hours.
$ crontab -e 0 23 * * * pg_dump -U dbusername -w -F t dbname > /var/lib/pgsql/db.tar 0 0 * * * . /var/lib/pgsql/.bashrc; /usr/bin/restic backup -q /var/lib/pgsql/db.tar
Note: Depending upon the size of your database, how often it changes, you might need to change the schedule for the cron job accordingly.
Check the Backblaze portal to verify that the snapshot was created in our b2 bucket.
We can download the backup using restic again, when we need it.
Get the snapshot-ID.
$ restic -r b2:bucket-name snapshots
Restore the snapshot to a folder using the snapshot_ID.
$ restic -r b2:bucket-name restore snapshot_ID -t /tmp/restic-restore
Note: When downloading the snapshot in another machine, requisite credentials for restic and b2 should be provided as discussed earlier.
Postgres dump would be available in the folder where the snapshot was restored.
$ cd /tmp/restic-restore
Create a database of the same name after logging in as the database user.
$ CREATE DATABASE dbname;
Use the pg_restore tool to restore the database from the dump.
$ pg_restore --dbname=dbname --verbose db.tar
That’s all, now we can have a safe and reliable backup postgres database in the cloud. You can tweet to me for queries and share this if you found it useful.
If you would like unlimited automated backup your entire computer storage instead, then Backblaze has solutions for home and businesses users. Check them out using my affiliate links.