In a production environment, your data is your most valuable asset. Whether you are running a logistics system, an accounting app, or a personal blog, a reliable backup strategy is non-negotiable.

This guide will show you how to automate PostgreSQL backups on Ubuntu 24.04 using a Shell script and Cron jobs, including timezone optimization for WIB (UTC+7).

Prerequisites

• Ubuntu 24.04 Server installed.
• PostgreSQL 16, 17 or 18 installed and running.
• Sudo privileges.

Step 1: Configure Passwordless Authentication

For a script to run automatically, it cannot stop to ask for a password. We use a .pgpass file to store credentials securely.

1. Create the file in your home directory:

   nano ~/.pgpass
   

2. Add your credentials in this format: hostname:port:database:username:password. Use * as a wildcard for the database name to cover all databases:

   localhost:5432:*:postgres:your_secure_password
   

3. Set strict permissions (PostgreSQL will ignore the file if it's readable by others):

   chmod 0600 ~/.pgpass
   

Step 2: Set the System Timezone to UTC+7

To ensure your backups run at the correct local time (e.g., late at night in Indonesia), set your server's timezone:

sudo timedatectl set-timezone Asia/Jakarta

Step 3: Create the Backup Script

Now, we will create a script that handles the backup process and cleans up old files to save disk space.

1. Create the script file:

   nano ~/backup_postgres.sh
   

2. Paste the following code:

   #!/bin/bash
   
   # Configuration
   DB_NAME="your_database_name"
   DB_USER="postgres"
   BACKUP_DIR="/home/$(whoami)/backups"
   # Format: YYYY-MM-DD_HHMMSS (using local time)
   DATE=$(date +"%Y-%m-%d_%H%M%S")
   
   # Ensure backup directory exists
   mkdir -p $BACKUP_DIR
   
   # Execute pg_dump
   # -F c: Custom format (compressed and flexible for pgAdmin)
   pg_dump -U $DB_USER -F c $DB_NAME > $BACKUP_DIR/backup_${DB_NAME}_${DATE}.dump
   
   # Housekeeping: Delete backups older than 7 days
   find $BACKUP_DIR -type f -mtime +7 -name "*.dump" -delete
   

3. Make the script executable:

   chmod +x ~/backup_postgres.sh
   

Step 4: Schedule with Cron

We will use the Crontab to trigger this script every day at 02:00 AM.

1. Open the crontab editor:

   crontab -e
   

2. Add this line at the very bottom of the file:

   0 2 * * * /home/your_username/backup_postgres.sh
   

   This translates to: 0 minutes, 2 hours (AM), every day, every month, every weekday.

Step 5: Verify the Setup

You can test the script manually to ensure it generates the file correctly:

./backup_postgres.sh
ls -lh ~/backups

Summary of Benefits

• Automation: No manual intervention required.
• Timezone Aware: Backups occur during low-traffic hours in your local time.
• Disk Management: Old backups are automatically purged after 7 days.
• Security: Password is kept out of the main script via .pgpass.

Pro Tip: For critical applications, always consider syncing these backup files to a remote cloud storage (like Google Drive or S3) using tools like rclone.