Skip to main content

PostgreSQL Database Backup Guide

This guide explains how to set up automated daily PostgreSQL database backups to Google Drive using the provided backup_db.sh script from the repository.

Prerequisites​

Before setting up the backup system, ensure you have:

  • A running PostgreSQL database in Docker (see PostgreSQL Management)
  • rclone installed on your server
  • Access to a Google Drive account where backups will be stored

Setup Process​

1. Install Required Tools​

First, install rclone if you haven't already:

sudo apt update
sudo apt install -y rclone

2. Configure rclone for Google Drive​

Set up rclone to connect to your Google Drive account:

rclone config

Follow the interactive setup:

  1. Select n for "New remote"
  2. Enter a name for your remote (e.g., gdrive - this must match the value in your .env file)
  3. Select Google Drive (usually option number 18, but check the list)
  4. For client ID and secret, check here the guide
    • Using team.ledges we already have api key created, go here
  5. Select 3 for "read / write only rclone created files"
  6. Select n for "Edit advanced configs"
  7. Select n for "Use auto config"
  8. It will give a command to copy to the local machine (eg: rclone authorize "drive" "eyJjbGllbnRfaWQ...)
  9. Paste the command and execute it, login with the admin account of the gdrive
  10. For team drive or shared drive, select n
  11. Verify settings and select y to confirm

3. Set Up Environment Variables​

Create your environment file based on the provided template:

nano .env

Complete the environment variables in the .env file:

DB_NAME=your_database_name
DB_USER=your_database_user
DB_PASSWORD=your_database_password

RCLONE_REMOTE_NAME=gdrive  # Must match the name used in rclone config
RCLONE_REMOTE_PATH=<GDRIVE_FOLDER>/DB/Backups/
VPS_ENV_NAME=<VPS_ENV_NAME>
BACKUP_DIR=/home/ubuntu/<folder-path>/db/backups

4. Set Up Cron Job for Automated Backups​

There's already the backup_db.sh in Flo sources. Use that.

Schedule the backup script to run automatically using cron:

crontab -e

Add the following line to run backups daily at 2:00 AM:

0 */6 * * * cd /home/ubuntu/Flo && ./backup_db.sh >> ./db/logs/backup.log 2>&1

“At minute 0 past every 6th hour.”

Make sure the logs directory exists:

mkdir -p /home/ubuntu/Flo/db/logs

5. Test the Backup Process​

Verify that your backup system works by running the script manually:

cd ~/Flo/
./backup_db.sh

Check that:

  1. A backup file was created in your BACKUP_DIR
  2. The file was successfully uploaded to Google Drive

Troubleshooting​

If you encounter issues with the backup process:

Backup Creation Failures​

  • Check PostgreSQL container is running: docker ps
  • Verify database credentials in .env
  • Ensure directory permissions are correct for backup directory

rclone Connection Issues​

  • Verify rclone configuration: rclone config show
  • Test rclone connection: rclone lsd gdrive:
  • Check if remote path exists: rclone mkdir gdrive:<GDRIVE_FOLDER>/DB/Backups

Cron Job Not Running​

  • Check cron logs: grep CRON /var/log/syslog
  • Verify script permissions: chmod +x backup_db.sh
  • Test cron environment: env > ~/cronenv in your crontab