PostgreSQL Backup Script

  • Post author:

This shell script is designed to facilitate the backup of a PostgreSQL database. It allows users to easily configure and schedule daily backups, providing flexibility and control over the backup process. The script utilizes a configuration file to set various parameters, making it adaptable to different PostgreSQL setups.

Git Reposiroty

https://github.com/prashant-raghava/postgres-backup

Configuration Flexibility

  • Create Config File: Create a configuration file named postgres_backup.config. Use the following template and adjust the values based on your requirements.
# postgres_backup.config

# Directory to store backups
BACKUP_DIR="/postgres/pgsql/13/BACKUP/"

# PostgreSQL username
USERNAME="postgres"

# Number of days to keep daily backups
DAYS_TO_KEEP_DAILY_BACKUPS=7

# Number of weeks to keep weekly backups
WEEKS_TO_KEEP_WEEKLY_BACKUPS=4

# Enable plain backups (yes/no)
ENABLE_PLAIN_BACKUPS="yes"

Adjust the values of BACKUP_DIR, USERNAME, DAYS_TO_KEEP_DAILY_BACKUPS, WEEKS_TO_KEEP_WEEKLY_BACKUPS, and ENABLE_PLAIN_BACKUPS according to your preferences.

Daily Backup Scheduling

The script allows users to schedule daily backups effortlessly. By integrating with tools like cron, users can automate the execution of the script at specified intervals, ensuring regular and reliable database backups.

Efficient Backup Management

With configurable retention periods for daily and weekly backups, users can optimize storage usage while ensuring access to historical database snapshots. The script manages backup storage efficiently, helping users strike a balance between data preservation and resource utilization.

Prerequisites

To use the PostgreSQL Backup Script, ensure the following:

  • PostgreSQL Installation: PostgreSQL and related tools (e.g., pg_dump) must be installed on the system.
  • Executable Permissions: The script should have executable permissions (chmod +x postgres_backup.sh) to run successfully.

Running the Script

  • Make Script Executable: Ensure that the script is executable by running the following command:
chmod +x postgres_backup.sh
  • Execute the Script: Run the script by executing the following command:
./postgres_backup.sh

The script will read the configuration from the postgres_backup.config file and initiate the backup process.

Scheduling Daily Backups

To schedule daily backups, you can use tools like cron on Unix-based systems. Here’s an example of scheduling the script to run every day at 2:00 AM:

  1. Open the crontab file for editing:
crontab -e
  1. Add the following line to schedule the script:
0 2 * * * /path/to/postgres_backup.sh

This example runs the script every day at 2:00 AM. Adjust the timing according to your preferred schedule.

Now, your PostgreSQL database will be backed up daily based on the configuration provided in the postgres_backup.config file. Adjust the settings as needed to meet your specific backup requirements.

Tags: ,

Leave a Reply