How to Backup PostgreSQL Database to Amazon S3 with Bash

Amazon S3 also called Amazon Simple Storage Service is a cloud object storage service provided by Amazon Web Services. It provides object storage through a web service interface and API. It is designed for storing and recovering any amount of data at any time. Generally, it is used by the website owner to organise data for websites, mobile applications, backup and restore and other purposes.

Manually backing up a PostgreSQL database is a very hard and time-consuming process for any system administrator. In this case, SnapShooter provides an easier way to backup your PostgreSQL database to Amazon S3. It supports many cloud providers, including, Amazon AWS, Google Cloud, Vultr, DropBox, Hetzner, UpCloud, and more.

This guide will show you how to backup the PostgreSQL database to Amazon S3.

Create an S3 Bucket in AWS

Before you can store any object or data in S3, you need to create a bucket on AWS S3. Follow the below steps to create a bucket on AWS S3:

Step 1 - Login to Amazon Management Console, search for S3 and open the S3 service.

Step 2 - Click on Create bucket button. You should see the General configuration screen:

Step 3 - Type your bucket name, select your region, Select object ownership, Block all public access, and click on the Create bucket. Once the bucket is created, you will get the following screen:

Create a New IAM User

After creating an S3 bucket, you must create a new IAM user and assign permission for your S3 bucket. Follow the below steps to create an IAM user:

Step 1 - Log in to Amazon Management Console, search for IAM from the top services menu, and click on Users on the left sidebar. You should see the IAM user screen:

Step 2 - Click the Add users button to create a new IAM user. You should see the following screen:

Step 3 - Type your username, select "Access Type Programmatic access", then click on the Next: Permissions:

Step 4 - Leave all default options and click on the Next: Tags:

Step 5 - Leave all default options and click on the Next: Review:

Step 6 - Review your configuration and click on the Create user button. You should see the following screen:

Step 7 - Note the Access key ID and Secret access key in a safe location. In the next step, you will need both keys to connect to the AWS S3. Now, click on the Close button. You should see the following screen:

Step 8 - Click on your newly created user. You should see the following screen:

Step 9 - Click on Add inline policy. You should see the Create Policy screen:

Step 10 - Choose JSON Editor and add the following contents:

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Effect": "Allow",
           "Action": [
               "s3:ListBucket",
               "s3:DeleteObject",
               "s3:GetObject",
               "s3:PutObject",
               "s3:PutObjectAcl"
           ],
           "Resource": [
             "arn:aws:s3:::postgresql-database/*",
             "arn:aws:s3:::postgresql-database"
           ]
       }
   ]
}

Step 11 - Replace the PostgreSQL-database with the newly created bucket. Then, click on the Review policy button. You should see the following screen:

Step 12 - Provide your policy name and click on the Create policy button to save the policy.

Backup PostgreSQL Database to Amazon S3 Manually

In this section, we will show you how to manually backup the PostgreSQL database to Amazon S3 using the AWS CLI tool.

Install AWS CLI Tool

Before starting, you will need to install the AWS CLI tool on the PostgreSQL server from where you want to backup your databases. The AWS CLI is a command-line tool to manage your AWS services. It helps you to control multiple AWS services from the command line and automate them through scripts.

First, download the AWS CLI utility using the command given below:

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"

Next, unzip the downloaded file and install it using the following command:

unzip awscliv2.zip
sudo ./aws/install

After the installation, you will need to configure AWS CLI with your AWS credentials.

aws configure

Provide your AWS access key ID, secret key, and the region as shown below:

AWS Access Key ID [None]: YOUR-AWS-ACCESS-KEY
    AWS Secret Access Key [None]: YOUR-AWS-SECRET-KEY
    Default region name [None]: us-east-1
    Default output format [None]:

At this point, the AWS CLI tool is configured on the server to control all AWS services.

Use AWS CLI to Backup PostgreSQL Database to Amazon S3

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
-----------+----------+----------+-------------+-------------+-----------------------
 magentodb | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 studentdb | 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
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 wpdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

Now, use the pg_dump utility to backup a testdb database:

pg_dump -U postgres -h localhost -d testdb > testdb.sql

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

pg_dumpall -f all_pg_dbs.sql

After backing up your desired databases, you will need to copy the database backup file to the AWS S3:

Use the following syntax to copy a PostgreSQL database to AWS S3:

aws s3 cp [your-db-name] "s3://your-s3-bucket-name"

For example, to back up a PostgreSQL database named testdb.sql to AWS S3, use the following command:

aws s3 cp testdb.sql  "s3://postgresql-database"

You will get the following output:

upload: ./testdb.sql to s3://postgresql-database/testdb.sql

Next, copy the all_pg_dbs.sql database to AWS S3 using the following command:

aws s3 cp all_pg_dbs.sql  "s3://postgresql-database"

You will get the following output:

upload: ./all_pg_dbs.sql to s3://postgresql-database/all_pg_dbs.sql

You can now list all databases on the AWS S3 bucket using the following command:

aws s3 ls "s3://postgresql-database"

You will get the following output:

2022-02-11 11:18:32      14793 all_pg_dbs.sql
2022-02-11 11:18:18        551 testdb.sql

You can also verify all databases by logging in to the Amazon Web Console, search for S3 and click on your bucket name. You should see the following screen: