Bhavya Arora

Posted on Aug 31 Originally published at zyvop.com

The Ultimate Guide to Automating Postgres Backups from Docker to Google Drive

#postgres #docker #backups #rclone

If you run a production web application, your database is your most valuable asset. While Docker makes deploying a Postgres database incredibly easy, it also creates a false sense of security. If your entire server crashes, your VPS provider goes down, or you accidentally delete a volume, your data is gone forever.

The gold standard for backups is Off-Site Storage.

In this guide, we will walk through exactly how to completely automate your Postgres database backups. We'll extract the data securely from a Docker container, compress it, upload it directly to your Google Drive, and automatically delete old backups so your hard drive never fills up.

Prerequisites

Before we begin, ensure your server has the following:

A running Docker container running PostgreSQL
rclone installed on your server (sudo apt install rclone)
A Google account with sufficient Drive storage space

Step 1: Configure Rclone for Google Drive

Rclone is often referred to as "The Swiss army knife of cloud storage". It's a command-line program that can sync files to almost any cloud provider, including Google Drive.

If your database is hosted on a remote Ubuntu server, you'll need to use rclone's "headless" authorization, since you don't have a web browser on the server.

On your server, initialize the config:
```
rclone config
```
Type n for New remote and name it gdrive.
Select drive (Google Drive) from the list of cloud providers.
Leave client_id and client_secret blank (press Enter).
For scope, choose the option for Full access all files (usually option 1).
Leave the rest of the options default until it asks: Use auto config?. Type n (No).

Rclone will now give you a long command that looks like this:

rclone authorize "drive" "eyJzY29wZ...

On your local computer (where you do have a web browser), install rclone, open your terminal, and paste that exact command.
Your browser will open. Log into your Google Account and click Allow.
Your local terminal will output a secure token. Copy the entire { "access_token": ... } JSON string.
Back on your server, paste the token into the prompt.

Your Google Drive is now securely linked to your server!

Step 2: The Ultimate Backup Script

Now we need a bash script that handles the heavy lifting. This script will read your database credentials from your .env file, export a compressed snapshot of your Postgres database from inside the Docker container, and upload it to Google Drive.

Create a file named backup-db.sh in your project's root folder:

nano backup-db.sh

Paste the following script inside. Be sure to update the PROJECT_DIR and CONTAINER_NAME variables to match your environment:

#!/bin/bash

# Configuration
PROJECT_DIR="/home/bhavya/apps/my-project"
BACKUP_DIR="$PROJECT_DIR/backups"
ENV_FILE="$PROJECT_DIR/.env"

# Rclone configuration
# Format: "RemoteName:FolderNameInGoogleDrive"
RCLONE_REMOTE="gdrive:database-backups"

# Docker container running Postgres
CONTAINER_NAME="my-project-db-1"

# Automatically load the database variables from your .env file
if [ -f "$ENV_FILE" ]; then
    export $(grep -v '^#' "$ENV_FILE" | xargs)
else
    echo "Error: .env file not found at $ENV_FILE"
    exit 1
fi

mkdir -p "$BACKUP_DIR"

# Generate a timestamped filename
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_DIR/db_backup_$TIMESTAMP.dump"

echo "Starting backup at $TIMESTAMP"

# 1. Export the database from the Docker container
# We use the custom Postgres format (-F c) for maximum compression and reliability
docker exec "$CONTAINER_NAME" pg_dump -U "$POSTGRES_USER" -F c -d "$POSTGRES_DB" > "$BACKUP_FILE"

if [ $? -eq 0 ]; then
    echo "Backup created successfully: $BACKUP_FILE"

    # 2. Upload the encrypted file to Google Drive using rclone
    echo "Uploading to Google Drive..."
    rclone copy "$BACKUP_FILE" "$RCLONE_REMOTE"

    if [ $? -eq 0 ]; then
        echo "Upload successful!"
    else
        echo "Error during rclone upload!"
        exit 1
    fi
else
    echo "Error creating backup!"
    exit 1
fi

# 3. Clean up: Delete local backups older than 7 days so your server disk never fills up
echo "Cleaning up old local backups..."
find "$BACKUP_DIR" -type f -name "db_backup_*.dump" -mtime +7 -exec rm {} \;

echo "Backup process completed."
echo "-----------------------------------"

Save the file and make it executable:

chmod +x backup-db.sh

You can test it immediately by running ./backup-db.sh. If it works, check your Google Drive—you should see a new folder containing your .dump file!

Step 3: Put it on Autopilot with Cron

We don't want to run this script manually every day. Linux has a built-in time-based job scheduler called cron that is perfect for this.

Open your crontab editor:

crontab -e

Scroll to the very bottom and add the following line:

# Run the database backup daily at 2:00 AM
0 2 * * * /home/bhavya/apps/my-project/backup-db.sh >> /home/bhavya/apps/my-project/backups/backup.log 2>&1

Breaking down the cron schedule:

0 2 * * * means the script will run exactly at 2:00 AM every single day.
>> backup.log 2>&1 ensures that all terminal outputs (both successes and errors) are saved to a log file. If you ever need to debug why a backup failed, simply check this file!

Conclusion

And that's it! You have now implemented an enterprise-grade disaster recovery pipeline for your startup or side project.

Every night at 2:00 AM:

Your Docker database is frozen and exported.
The file is seamlessly transferred to Google Drive.
Any backup older than 7 days is automatically deleted from your server to save disk space.

Sleep soundly knowing your users' data is safe!

Originally published on ZyVOP

💡 For more articles like this, subscribe to the ZyVOP newsletter!

DEV Community