How to Backup PostgreSQL Database to Filebase Using Bash
Filebase provides an S3-compatible object storage service that helps users to store and serve large amounts of data over the internet. It is an alternate solution to AWS S3 and offers simple, affordable, and predictable pricing. It offers all users a 5GB free tier with no expiration or trials. Filebase uses edge caching technology that increases throughput and lowers response times for both ingress and egress.
This post will show you how to backup PostgreSQL databases to Filebase using Bash.
Table Of Contents
- Create a Bucket on Filebase
- Install S3cmd Tool
- Configure S3cmd to Connect PostgreSQL Server to Filebase
- Use S3cmd to Backup PostgreSQL Database to Filebase Bucket
- Conclusion
Create a Bucket on Filebase
First, you will need to create a bucket on Filebase where you want to store your PostgreSQL databases. Follow the below steps to create a Filebase bucket.
Step 1 - First, sign in to your Filebase account.
Step 2 - Click on the Buckets on the left pane, you should see the following screen:
Step 3 - Click on the Create Bucket button. You should see the bucket creation screen:
Step 4 - Provide your bucker name, choose your storage location, and click the Create Bucket button. Once the bucket is created, you should see the following screen:
Step 5 - Click on the Access Keys on the left pane, you should see your Filebase access key and secrets on the following screen:
Please note down this access key, secret key, and API endpoint. You will need all to connect to the Filebase bucket.
Install S3cmd Tool
Next, you will need to install the S3cmd tool on your PostgreSQL database server from where you want to back up your PostgreSQL database.
S3cmd is a free and open-source command-line tool that allows you to upload, download and manage data in Filebase 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.0python3 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 PostgreSQL Server to Filebase
Next, you will need to configure the S3cmd using the Filebase Access key and Secret key. You can configure it using the following command:
s3cmd --configure
You will be asked to provide your Filebase 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: 54203D2S91DA6F043A3DSecret Key: JSo3sFt7vMF1v3iISArDwWvzSHsPlSsroaL1591ADefault 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 usedif 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 readingby unauthorized persons while in transfer to S3Encryption password:Path to GPG program [/usr/bin/gpg]: When using secure HTTPS protocol all communication with Amazon S3servers is protected from 3rd party eavesdropping. This method isslower than plain HTTP, and can only be proxied with Python 2.7 or newerUse 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 directlyHTTP Proxy server name: New settings: Access Key: 54203D2S91DA6F043A3D Secret Key: JSo3sFt7vMF1v3iISArDwWvzSHsPlSsroaL1591A 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] YPlease 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
Once the S3cmd is configured, you can verify your Filebase Bucket information using the following command:
s3cmd info s3://postgresql-filebase/
You will get the following output:
s3://postgresql-filebase/ (bucket):Location: us-east-1Payer: noneExpiration Rule: nonePolicy: <?xml version="1.0" encoding="UTF-8"?><ListBucketResult xmlns="http://s3.amazonaws.com/doc/2006-03-01/"><Name>postgresql-filebase</Name><Prefix/><Marker/><MaxKeys>1000</MaxKeys><IsTruncated>false</IsTruncated></ListBucketResult>CORS: noneACL: simon@snapshooter.io: FULL_CONTROL
Use S3cmd to Backup PostgreSQL Database to Filebase Bucket
First, log in to your server and connect to the PostgreSQL shell with the following command:
su - postgresqlpsql
Once you are connected, list all PostgreSQL databases using the following command:
postgres=# \l
You will get all databases in the following output:
List of databasesName | 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/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgrestestdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |(7 rows)
Next, use the pg_dumpall tool to backup all PostgreSQL databases:
pg_dumpall > dump.sql
If you want to back up a single database, run the following command:
pg_dump -U username -W -F t -d testdb > testdb.tar
Next, run the S3cmd command to copy the dump.sql file to the Filebase Bucket:
s3cmd put dump.sql s3://postgresql-filebase/
You will get the following output:
upload: 'dump.sql' -> 's3://postgresql-filebase/dump.sql' [1 of 1]26690 of 26690 100% in 0s 57.07 KB/s done
If you want to copy a single backup file, run the following command:
s3cmd put testdb.tar s3://postgresql-filebase/
You will get the following output:
upload: 'testdb.tar' -> 's3://postgresql-filebase/testdb.tar' [1 of 1]2170880 of 2170880 100% in 3s 672.95 KB/s done
You can now verify all backups on the Filebase Bucket using the following command:
s3cmd ls s3://postgresql-filebase
You will get the following output:
2022-07-15 05:25 26690 s3://postgresql-filebase/dump.sql2022-07-15 05:25 2170880 s3://postgresql-filebase/testdb.tar
You can also log in to the Filebase Bucket and verify your backup files as shown below:
Conclusion
In this post, you learned how to back up the PostgreSQL database to the Filebase bucket using bash. You can now easily copy your PostgreSQL databases to the Filebase bucket via the s3cmd command line.
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 freeThank you for helping us improve!