Automating PostgreSQL Database Backups on Linux: A Practical Guide

As a database administrator, safeguarding critical data is a top priority. Regular backups ensure business continuity and minimize data loss in the event of system failures or cyber threats. In this article, we will walk you through the process of automating PostgreSQL database backups on a Linux (CentOS) system using built-in PostgreSQL tools, pg_dump for logical backups, and  cron for scheduling.

Why Automate Backups?

Manual backups are prone to human error and can easily be overlooked. Automating the backup process provides:

• Reliability: Consistent and timely backups.
• Data Protection: Safeguards data against accidental deletions, hardware failures, and cyber threats.
• Operational Efficiency: Frees up DBA resources for other tasks.
• Disaster Recovery Readiness: Quick recovery options when needed.

Note: The provided scripts are compatible with PostgreSQL versions 9.0 and above, as pg_dump and pg_basebackup have been standard utilities since those versions. Checking specific version compatibility for any new PostgreSQL features or options used in the scripts is recommended.

Step 1: Performing Manual Backups with pg_dump

pg_dump is a PostgreSQL utility used to create a logical backup of a single database. It outputs SQL statements or archive files that can restore the database.

Prerequisites:

Ensure the following before running backups:

1. PostgreSQL is installed and running.
2. You have valid database credentials (username and password).
3. You have a designated directory for storing backup files.

Basic pg_dump Command:

bash

pg_dump -h localhost -U postgres -d my_database -p 5432 >
/backups/my_database_backup.sql

Explanation:

• -h localhost: Specifies the database server.
• -U postgres: The username used to connect to PostgreSQL.
• -d my_database: The target database.
• -p 5432: PostgreSQL’s default port.
• >: Redirects the output to a backup file.

Enhancing the Backup:

To compress the backup and save storage, use:

bash

pg_dump -h localhost -U postgres -d my_database -F c | gzip >
/backups/my_database_backup.gz

Step 2: Writing a Backup Script

To streamline the process, let’s create a shell script that performs the backup and stores it with a timestamp for easy tracking.

Sample backup.sh Script:

bash

#!/bin/bash

# Configuration
DB_NAME="my_database"
DB_USER="postgres"
DB_HOST="localhost"
BACKUP_DIR="/backups"
TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$TIMESTAMP.sql.gz"

# Perform the backup

PGPASSWORD="your_password" pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME -F c | gzip > $BACKUP_FILE

# Logging the backup

echo "Backup created: $BACKUP_FILE" >> /var/log/pg_backup.log

Steps to Implement the Script:

1. Save the script as backup.sh.
2. Make it executable:

• bash

• chmod +x backup.sh

1. Run the script:

• bash

• ./backup.sh

If the script runs successfully, you should see a compressed backup file in the designated directory.

Step 2.1: Other example – Backup Script

Below is a practical example of a more complex backup script that automates the process, compresses the backup, sends email notifications, and handles old backup cleanup.

bash

#!/bin/bash

# Database name

DB_NAME="my_database"

# PostgreSQL credentials

DB_USER="postgres"
DB_HOST="localhost"
DB_PORT="5432"

# Backup storage directory

BACKUP_DIR="/var/backups/postgresql"

# Email notification settings

EMAIL_RECIPIENT="admin@example.com"

# Retention period (in days)

RETENTION_DAYS=30

# Timestamp for backup filename

TIMESTAMP=$(date +"%Y-%m-%d_%H-%M-%S")

# Backup file paths

SQL_FILE="$BACKUP_DIR/${DB_NAME}_$TIMESTAMP.sql"
COMPRESSED_FILE="$SQL_FILE.gz"

# Ensure the backup directory exists

mkdir -p $BACKUP_DIR

# Perform the database backup

export PGPASSWORD="your_password"

if pg_dump -h $DB_HOST -U $DB_USER -p $DB_PORT -F c $DB_NAME > $SQL_FILE; then

echo "Database backup created: $SQL_FILE"

else

echo "pg_dump failed for $DB_NAME" | mailx -s "PostgreSQL Backup Failed" $EMAIL_RECIPIENT

exit 1

fi

# Compress the backup file

if gzip -c $SQL_FILE > $COMPRESSED_FILE; then

echo "Backup successfully compressed: $COMPRESSED_FILE"

else

echo "Backup compression failed" | mailx -s "PostgreSQL Backup Compression Failed" $EMAIL_RECIPIENT

exit 1

fi

 
# Remove the uncompressed backup

rm $SQL_FILE
 

# Send success notification

echo "Backup completed successfully: $COMPRESSED_FILE" | mailx -s "PostgreSQL Backup Success" $EMAIL_RECIPIENT
 

# Delete backups older than retention period

find $BACKUP_DIR -name "${DB_NAME}_*.gz" -mtime +$RETENTION_DAYS -exec rm {} \; 

echo "Old backups older than $RETENTION_DAYS days have been deleted."

Variables

Database Configuration:

1. DB_NAME: The name of the database to back up.
2. DB_USER: PostgreSQL user performing the backup.
3. DB_HOST: The database server hostname or IP.
4. DB_PORT: PostgreSQL port (default: 5432).

Backup Settings:

1. BACKUP_DIR: Directory where backups will be stored.
2. EMAIL_RECIPIENT: Email address to send backup notifications.
3. RETENTION_DAYS: Number of days to keep backups before deletion.
4. TIMESTAMP: Generates a unique timestamp for the backup filename.
5. SQL_FILE: Defines the output filename for the backup.
6. COMPRESSED_FILE: The compressed output filename.

Backup Process:

1. mkdir -p $BACKUP_DIR: Ensures the backup directory exists.
2. pg_dump is executed to create a backup file.
3. If the backup fails, an email is sent, and the script exits.
4. The backup is compressed using gzip.
5. If compression fails, an error email is sent.
6. The uncompressed SQL file is deleted after compression.

Notification:

1. Email notifications are sent after a successful backup.

Old Backup Cleanup:

1. The script deletes backups older than the retention period using find and rm.

By automating this process, database administrators can ensure regular backups, quick recovery, and effective storage management.

 

Step 3: Automating Backups with cron

Linux provides cron as a task scheduler to automate recurring jobs.

Scheduling Backups:

1. Open the crontab configuration:

• bash
• crontab -e

1. Add the following line to schedule daily backups at midnight:

• bash

• 0 0 * * * /path/to/backup.sh >> /var/log/pg_backup.log 2>&1

Explanation:

• 0 0 * * * — Schedule at midnight daily.
• >> — Appends output to the log file.
• 2>&1 — Captures errors in the log.

To confirm the scheduled job:

bash
crontab -l

Step 4: Transferring Backups to a Remote Server

Storing backups on the same server poses a risk if the server fails. To mitigate this, we can transfer backups to a remote location using scp (secure copy).

Steps to Transfer Backups Securely:

1. Install sshpass to allow password-based SSH automation:

• • bash

  • sudo dnf install -y epel-release

  • sudo dnf install -y sshpass

2. Modify the backup.sh script to include file transfer:

• bash

• REMOTE_USER="remote_user"

• REMOTE_HOST="192.168.1.100"

• REMOTE_DIR="/remote_backups"

• sshpass -p 'remote_password' scp $BACKUP_FILE $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR

Security Tip: Avoid hardcoding passwords in scripts by using SSH key-based authentication instead of sshpass.

Step 5: Restoring from Backup

To restore a PostgreSQL backup from the generated file, run:

bash

gunzip -c /backups/my_database_backup.gz | psql -U postgres -d my_database

If restoring a full dump:

bash

psql -U postgres -d my_database < /backups/my_database_backup.sql

Step 6: Best Practices for PostgreSQL Backups

To ensure the effectiveness of your backup strategy:

1. Verify Backups: Regularly test backups by restoring them to a test environment.
2. Automate Cleanup: Delete older backups to free up storage:

• bash

• find /backups -mtime +30 -delete

1. Encrypt Backups: Use tools like gpg to encrypt backups for enhanced security.
2. Monitor Backups: Set up email alerts for backup failures using mail or monitoring tools.

Ensuring Reliable PostgreSQL Backups: Key Takeaways

Automating PostgreSQL database backups using pg_dump and cron is an essential practice for database administrators. It ensures data security, consistency, and quick recovery in case of unexpected failures. By following the steps outlined in this guide, you can create a robust backup strategy tailored to your organization’s needs.

Security Considerations

To ensure the security of sensitive data in the scripts used for PostgreSQL backups, it’s important to follow best practices for managing access and data transfer.

For instance, database passwords should never be hardcoded directly into scripts. Instead, you can use a .pgpass file, which securely stores the necessary credentials and is automatically recognized by PostgreSQL. Similarly, SSH transfers should not rely on passwords but should always use SSH public keys for authentication, providing a more secure and password-free method. While the examples in this blog demonstrate functional approaches using passwords directly, this is not considered best practice and can expose your system to unnecessary security risks. Following these standards protects your data and minimizes vulnerabilities to potential cyber threats.

Future-Proof Your Data

Looking for expert support in database automation? Reach out to ROPARDO—your trusted ally in innovation, quality, and timely delivery.