The Ultimate PostgreSQL Database Backup Guide

This article is part of “The Ultimate Backup Script” series we are creating to provide you with database backup scripts that not only allow you to create database backups, but also upload the backup dumps to Amazon S3 and automate the process daily.

Why PostgreSQL Backup is Crucial

A backup is a copy of your data that lives somewhere the database does not. That last part is what makes it a backup rather than a second copy of the same risk: a dump sitting on the database host disappears with the host.

PostgreSQL backups protect against the four things that actually take databases out, in roughly the order we see them: human error, data corruption, device failure, and a bad deploy. Only one of those is dramatic, and the boring ones are more common.

How to automate Postgres database Backup

We'll develop a straightforward script to back up your PostgreSQL database and store it in your Amazon S3 bucket. Additionally, we will automate this process using Cron.

Key Points to Understand Before We Begin:

Why Choose Amazon S3? In this tutorial, we've selected Amazon S3 for its widespread use and reliability. If you prefer a different cloud storage provider, feel free to make that choice. The steps will remain largely similar as long as the chosen provider is compatible with S3.

What is Cron? Cron is a time-based job scheduler on Unix-like operating systems. Developers use it to run commands or shell scripts at set intervals, daily, weekly, or any other frequency you need.

Understanding Chmod Chmod, short for ‘change mode’, is a command used to set file handling rules. Through the “chmod” system call, an administrator can alter access permissions of file system objects.

🧑‍💻 Let's get down to coding now. You can automate the creation of postgres database backup and storing it to Amazon S3 following these steps

1. Crafting a script to automate the creation of a PostgreSQL backup directory.
2. Uploading/synchronizing the backups with Amazon S3.
3. Setting up Cron to execute this backup command daily.

1. Create a backup script that dumps the PostgreSQL database

Navigate to Your Home Directory and create a directory for your script:

cd ~
mkdir scripts
cd scripts
nano db_backup.sh

Create the PostgreSQL database backup script:

#!/bin/bash
set -euo pipefail

DIR=$(date +%F)
DEST="$HOME/db_backups/$DIR"
mkdir -p "$DEST"

PGPASSWORD='postgres_password' pg_dump \
  --format=custom \
  --username=postgres_user \
  --host=postgres_host \
  --port=postgres_port \
  postgres_database_name > "$DEST/dbbackup.dump"

Be sure to replace the following variables with your own values: postgres_password, postgres_user, postgres_host, postgres_port, and postgres_database_name.

Four details in that script are doing real work:

• set -euo pipefail makes the script stop on the first error instead of carrying on and exiting 0. Without it, a failed pg_dump still leaves a file behind and cron reports success.
• mkdir -p creates ~/db_backups on the first run. Plain mkdir fails when the parent does not exist yet.
• The dump is written into $DEST, not the current working directory. Cron runs with a different working directory than your shell, so a bare > dbbackup.sql puts the file somewhere you did not intend.
• --format=custom produces a compressed archive that pg_restore can restore selectively. The --inserts --column-inserts combination is far slower to restore and much larger on disk, so use it only when you genuinely need portable INSERT statements.

Putting the password inline works, but anyone able to run ps on that machine can read it while the dump runs. A ~/.pgpass file with chmod 600 keeps it out of the process list and out of your shell history.

Now chmod the backup script to allow it to for execution

chmod +x ~/scripts/db_backup.sh

Now, you possess a fully operational shell script tailored for automating PostgreSQL database backups. Next step is to automate it and to store the postgres backup on Amazon S3.

2. Configure the AWS CLI

We'll use the AWS CLI to sync the backup files with Amazon S3.

Install the AWS CLI (v2)

The v2 installer is a self-contained bundle, so there is no Python or pip dependency to manage:

curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install
aws --version

Set up AWS key & Secret

The quickest route is aws configure, which prompts for the four values and writes both files for you:

aws configure

If you would rather write the credentials file directly:

mkdir -p ~/.aws
nano ~/.aws/credentials

Paste in your access key and secret as shown below:

[default]
aws_access_key_id=AKIAIOSFODNN7EXAMPLE
aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

Those two values are AWS's own documentation placeholders. Replace them with a key belonging to an IAM user scoped to the one bucket you are writing to, and chmod 600 ~/.aws/credentials so other users on the machine cannot read it.

3. Sync the postgres backup to AWS S3

Now, let's build the script that will allow you to sync your PostgreSQL database backups with Amazon S3. This script will be used in conjunction with the script you created in the previous section.

Copy and paste the script into db_sync.sh.

#!/bin/bash
set -euo pipefail

# Set your AWS CLI path (update with your actual path if needed)
AWS_CLI_PATH="/usr/local/bin/aws"

# Set your Amazon S3 bucket name
S3_BUCKET="my-bucket-name"

# Specify the source directory where your backups are stored
SOURCE_DIR="$HOME/db_backups"

# Synchronize the backups with Amazon S3
"$AWS_CLI_PATH" s3 sync "$SOURCE_DIR" "s3://$S3_BUCKET"

Save the file and exit the text editor.

Make the script executable:

chmod +x ~/scripts/db_sync.sh

Now you have a shell script that syncs your Postgres database backups to your Amazon S3 bucket. Replace "my-bucket-name" with your actual bucket name, and update AWS_CLI_PATH if which aws reports a different location.

SOURCE_DIR="~/db_backups" looks right and silently is not: a tilde inside double quotes is never expanded, so the sync targets a literal directory called ~. Use $HOME whenever the path is quoted.

4. Schedule your PostgreSQL database backup with CRON

The last step is to automate the process of PostgreSQL database backup with CRON. To do this, follow these steps:

crontab -e

Paste the below commands at the bottom to automate the process. Use absolute paths: cron does not expand ~ reliably and runs with a minimal PATH.

0 0 * * * /home/youruser/scripts/db_backup.sh >> /home/youruser/backup.log 2>&1
0 2 * * * /home/youruser/scripts/db_sync.sh   >> /home/youruser/backup.log 2>&1

This way the backup script will run and also sync with Amazon S3 daily, and both scripts append their output to one log file you can actually read after a failure.

Conclusion

Hence, by using these scripts you can achieve 3 goals:

1. Creating the database backup via a shell script
2. Uploading the dump to Amazon S3
3. Automating this process using Cron.

A few things this script deliberately leaves out. It dumps in whichever format you pass to pg_dump, and custom vs plain export is worth reading before you settle on one. The upload step is covered in more detail in how to back up Postgres to AWS S3 if you want to go beyond a bare aws s3 sync. And the flags themselves are all in the pg_dump and pg_restore guide. It also assumes pg_dump can reach the database directly, which is not the case when Postgres runs in a container: backing up Postgres in Docker covers the docker exec version of the same commands.

What it does not do is tell you when it stops working. Cron will not email you when the dump fails, the script will not notice that last night's file is zero bytes, and nothing here verifies the dump still restores. Beyond pg_dump and cron covers what closes those gaps, whether you build it yourself or use PostgreSQL backup as a service.

FAQ

---

This article is part of The complete guide to PostgreSQL backup, an honest, practical reference from the team that backs up PostgreSQL every day.