This post will help you with easy, cost-effective and secure way to backup your PostgreSQL database to cloud.

Create local scheduled backup of PostgreSQL database

1. Login as postgres user on the console (tmux is recommended).

2. Create .pgpass file to access database without entering credentials manually.

$ nano .pgpass

3. Enter the credentials in the following format. Making changes according to the host if necessary.

localhost:5432:dbname:dbusername:dbpassword

4. 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

5. 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'

6. Test the login. Making changes according to host if necessary.

$ psql -h localhost -U dbusername dbname

Setup Restic

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

Setup Backblaze

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"

Initialize the restic repository in the Backblaze.

$ restic init

Setup a cron job to schedule the backup and upload

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.

backup.sh

#!/bin/bash
/usr/local/pgsql/bin/pg_dump -U postgres -w -F t database_name > /var/lib/pgsql/database.tar -v 2> /var/lib/pgsql/backup.log
. /var/lib/pgsql/.bashrc; /usr/bin/restic backup -q /var/lib/pgsql/database.tar &>> /var/lib/pgsql/backup.log

$ crontab -e

0 10 * * * sh ./backup.sh

Note: Depending upon the size of your database, how often it changes, you might need to change the schedule for the cron job accordingly.

Verify

Check the Backblaze portal to verify that the snapshot was created in our b2 bucket.

Downloading the backup

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. If the cr

Retrieving the Postgres dump

Postgres dump would be available in the folder where the snapshot was restored.

$ cd /tmp/restic-restore

Restoring the Postgres dump

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.

Backblaze entire storage backup solutions for Home and Business

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.

Backblaze unlimited cloud backup for home users.

Backblaze business backup.

Change log

Nov-6-2021: Added restic init command to initialize the repository.