How to Backup PostgreSQL Database to Backblaze with Bash

Introduction

Backblaze is a cloud-based storage service that provides a cost-effective solution to store your data in a safe and secure location. It offers simple, affordable, and predictable pricing. Storage is $0.005/GB/Month, with download costs of $0.01/GB.

SnapShooter is a cost-effective backup solution provider for keeping servers backed up, very easy to get everything set up and running. SnapShooter provides an easy, cost-effective, and secure way to backup your PostgreSQL database. SnapShooter will work with PostgreSQL hosted on your own VPS or dedicated servers.

This post will show you how to backup the PostgreSQL database to Backblaze Bucket manually and using SnapShooter.

Create your Backblaze Bucket

First, you will need to create a new bucket on the Backblaze website. Follow the below steps to create a new bucket on the Backblaze:

Step 1 - Sign in to your Backblaze account and go to the Bucket page to create a new Bucket.

Step 2 - Click on the Create a Bucket button. You should see the following page:

Step 3 - Provide all required information and click on the Create a Bucket button. Once the Bucket is created, you should see the following page:

Note down the Backblaze Bucket URL from the above image. You will need it later in this article.

Create Your Backblaze Credentials

After creating a Bucket on Backblaze, you will need to create the credentials to access it from the remote application and server.

Step 1 - On the Backblaze dashboard, click on the "App Keys" in the left pane. You should see the following page:

Step 2 - Now, click on "Add a new Application Key" button. You should see the following page:

Step 3 - Provide all required details and click on the Create New Key button. You should see the following page:

Note down the keyID, and applicationKey from the above image. You will need both to integrate Backblaze with the application.

Backup PostgreSQL Database to Backblaze Bucket Manually

In this section, we will show you how to back up a PostgreSQL database from your server to the Backblaze Bucket using the S3cmd tool.

Install S3cmd

S3cmd is a free and open-source command-line tool that allows you to upload, download and manage data in Backblaze Bucket and other cloud storage service providers. Follow the below steps to install the S3cmd to your server.

First, install the Python and other dependencies by running the following command:

apt-get install python3 python3-setuptools curl -y

Next, download the latest version of S3cmd using the following command:

curl -LO https://github.com/s3tools/s3cmd/releases/download/v2.2.0/s3cmd-2.2.0.tar.gz

Next, extract the downloaded file with the following command:

tar -xvzf s3cmd-2.2.0.tar.gz

Next, navigate to the extracted directory and install it using the following command:

cd s3cmd-2.2.0
python3 setup.py install

Once the S3cmd is installed, verify the S3cmd version using the following command:

s3cmd --version

You will get the following output:

s3cmd version 2.2.0

Configure S3cmd to Connect Backblaze Bucket

Now, you will need to configure the S3cmd using the BackBlaze credentials. You can configure it using the following command:

s3cmd --configure

You will be asked to provide your Backblaze Access Key, Secret Key, Region, and Endpoint as shown below:

Enter new values or accept defaults in brackets with Enter.
Refer to user manual for detailed description of all options.
 
Access key and Secret key are your identifiers for Amazon S3. Leave them empty for using the env variables.
Access Key: 004d55130756c060000000004
Secret Key: K004+FpgCwxcms+K+AlvVBJ1MR5DlOU
Default Region [US]: us-west-004
 
Use "s3.amazonaws.com" for S3 Endpoint and not modify it to the target Amazon S3.
S3 Endpoint [s3.amazonaws.com]: s3.us-west-004.backblazeb2.com
 
Use "%(bucket)s.s3.amazonaws.com" to the target Amazon S3. "%(bucket)s" and "%(location)s" vars can be used
if the target S3 system supports dns based buckets.
DNS-style bucket+hostname:port template for accessing a bucket [%(bucket)s.s3.amazonaws.com]: %(bucket)s.s3.us-west-004.backblazeb2.com
 
Encryption password is used to protect your files from reading
by unauthorized persons while in transfer to S3
Encryption password:
Path to GPG program [/usr/bin/gpg]:
 
When using secure HTTPS protocol all communication with Amazon S3
servers is protected from 3rd party eavesdropping. This method is
slower than plain HTTP, and can only be proxied with Python 2.7 or newer
Use HTTPS protocol [Yes]:
 
On some networks all internet access must go through a HTTP proxy.
Try setting it here if you can't connect to S3 directly
HTTP Proxy server name:
 
New settings:
  Access Key: 004d55130756c060000000004
  Secret Key: K004+FpgCwxcms+K+AlvVBJ1MR5DlOU
  Default Region: us-west-004
  S3 Endpoint: s3.us-west-004.backblazeb2.com
  DNS-style bucket+hostname:port template for accessing a bucket: %(bucket)s.s3.us-west-004.backblazeb2.com
  Encryption password:
  Path to GPG program: /usr/bin/gpg
  Use HTTPS protocol: True
  HTTP Proxy server name:
  HTTP Proxy server port: 0
 
Test access with supplied credentials? [Y/n] Y
Please wait, attempting to list all buckets...
Success. Your access key and secret key worked fine :-)
 
Now verifying that encryption works...
Not configured. Never mind.
 
Save settings? [y/N] y
Configuration saved to '/root/.s3cfg'
 
 
Once the S3cmd is configured, you can verify your Bucket information using the following command:
 
s3cmd info s3://snapshooter-postgresql-backup/
 
You will get the following output:
 
s3://snapshooter-postgresql-backup/ (bucket):
   Location:  us-west-004
   Payer:     none
   Expiration Rule: none
   Policy:    none
   CORS:      none
   ACL:       d55130756c06: FULL_CONTROL

Use S3cmd to Backup PostgreSQL Database to Backblaze Bucket

First, log in to the server from where you want to backup your PostgreSQL database. Then, log in to your PostgreSQL shell and list all databases using the following command:

postgres= \l

You should see all databases in the following output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 wpdb1     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 wpdb2     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Now, use the pg_dumpall utility to backup all PostgreSQL databases:

pg_dumpall -U postgres > all_pg_dbs.sql

Next, run the following command to copy all_pg_dbs.sql backup file to the Backblaze Bucket.

s3cmd put all_pg_dbs.sql s3://snapshooter-postgresql-backup/

Output:

upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 1 of 7, 15MB] [1 of 1]
 15728640 of 15728640   100% in    0s    18.45 MB/s  done
upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 2 of 7, 15MB] [1 of 1]
 15728640 of 15728640   100% in    0s    22.57 MB/s  done
upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 3 of 7, 15MB] [1 of 1]
 15728640 of 15728640   100% in    0s    18.09 MB/s  done
upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 4 of 7, 15MB] [1 of 1]
 15728640 of 15728640   100% in    0s    23.14 MB/s  done
upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 5 of 7, 15MB] [1 of 1]
 15728640 of 15728640   100% in    0s    26.97 MB/s  done
upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 6 of 7, 15MB] [1 of 1]
 15728640 of 15728640   100% in    0s    21.76 MB/s  done
upload: 'all_pg_dbs.sql' -> 's3://snapshooter-postgresql-backup/all_pg_dbs.sql'  [part 7 of 7, 10MB] [1 of 1]
 10485760 of 10485760   100% in    0s    19.11 MB/s  done

You can now verify all database backups on the Backblaze Bucket using the following command:

s3cmd ls s3://snapshooter-postgresql-backup/

You will get the following output:

2022-03-29 04:05    104857600  s3://snapshooter-postgresql-backup/all_pg_dbs.sql

You can also log in to the Backblaze Bucket and verify your backup files as shown below:

Scheduled PostgreSQL Single Databases Backups SnapShooter

Backup a single PostgreSQL database to your external storage

Learn more about PostgreSQL Single Databases Backups

Get started for free

No credit card required. Cancel anytime!

Conclusion

In this guide, you learned how to back up the PostgreSQL database to Backblaze Bucket using SnapShooter and manually. SnapShooter provides a reliable and secure backup solution that takes care of the entire backup process for you.