PostgreSQL Backup with Bash

Learn how to backup PostgreSQL to IPFS using Bash.

What is PostgreSQL?

PostgreSQL is an open-source relational database management service.

Read below to learn how to backup PostgreSQL to IPFS using Bash.

Prerequisites:

• Have a running PostgreSQL server.

• Sign up for a free Filebase account.

• Have your Filebase Access and Secret Keys. Learn how to view your access keys here.

• Create a Filebase IPFS Bucket. Learn how to create a bucket here.

Special thanks to Simon Bennett from SnapShooter for the original version of this tutorial.

1. Start by installing the S3cmd tool on your PostgreSQL server with the following commands:

apt-get install python3 python3-setuptools curl -y

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

tar -xvzf s3cmd-2.2.0.tar.gz

cd s3cmd-2.2.0

python3 setup.py install

2. Verify S3cmd was installed correctly using the following command to display the installed version:

s3cmd --version

3. Next, configure S3cmd for use with Filebase:

s3cmd --configure

You will be prompted to enter the following configuration details:

• Access Key: Filebase Access Key

• Secret Key: Filebase Secret Key

• Default Region: us-east-1

• S3 Endpoint: s3.filebase.com

Overall, your configuration output should resemble the following:

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: 54203D2S91DA6F043A3D
Secret Key: JSo3sFt7vMF1v3iISArDwWvzSHsPlSsroaL1591A
Default Region [US]: us-east-1
 
Use "s3.amazonaws.com" for S3 Endpoint and not modify it to the target Amazon S3.
S3 Endpoint [s3.amazonaws.com]: s3.filebase.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.filebase.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: xxxxxxxxxxxxxxxxxxxxx
  Secret Key: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  Default Region: us-east-1
  S3 Endpoint: s3.filebase.com
  DNS-style bucket+hostname:port template for accessing a bucket: %(bucket)s.s3.filebase.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

4. Test your configuration using the following command:

S3cmd info s3://filebase-bucket-name

Replace filebase-bucket-name with your Filebase bucket name. The output should resemble the following:

s3://filebase-bucket-name/ (bucket):
Location: us-east-1
Payer: none
Expiration Rule: none
Policy: <?xml version="1.0" encoding="UTF-8"?><ListBucketResult xmlns="<http://s3.amazonaws.com/doc/2006-03-01/>"><Name>filebase-bucket-name</Name><Prefix/><Marker/><MaxKeys>1000</MaxKeys><IsTruncated>false</IsTruncated></ListBucketResult>
CORS: none
ACL: [e-mail] : FULL_CONTROL

5. Next, login to your PostgreSQL server with the following commands:

Su - postgresql

psql

6. Show the available databases with the command:

postgres=# \\l

7. Then, backup all databases using the following command.

pg_dumpall > dump.sql

To backup just one database, use the command:

pg_dump -U username -W -F t -d testdb > testdb.tar

8. Run the following S3cmd command to copy the dump.sql file into your Filebase bucket:

s3cmd put dump.sql s3://filebase-bucket-name/

To upload just one database file, use the following command:

s3cmd put testdb.tar s3://postgresql-filebase/

Replace filebase-bucket-name with your Filebase bucket name.

9. Verify that all files have been uploaded correctly using the following command:

s3cmd ls s3://filebase-bucket-name

The output will resemble the following:

2022-08-08 05:31 10485760 s3://filebase-bucket-name/dump.sql

Looking for an easier, automated way to backup PostgreSQL to Filebase? Snapshooter allows you to schedule automated backups to Filebase for free, no credit card required. Get started today here.