/postgresql-backup-script

Backup script for Postgresql Database

Primary LanguageShell

Database Backup Script

This script is designed to perform backups of a PostgreSQL database. It utilizes pg_dump to create a physical backup of the specified database.

Setup

1. Configure PostgreSQL Connection Parameters

Before using the script, make sure to update the following variables in the script with your PostgreSQL connection details:

  • DB_HOST: The hostname or IP address of your PostgreSQL database server.
  • DB_PORT: The port on which your PostgreSQL database server is running.
  • DB_NAME: The name of the database you want to back up.
  • DB_USER: The username used to connect to the database.
  • DB_PASSWORD: The password used to connect to the database.

2. Specify Backup Directory

Set the BACKUP_DIR variable to the path where you want to store the backup files.

3. Define Date Format

The DATE_FORMAT variable determines the format of the timestamp appended to the backup directory name. By default, it uses the format YYYYMMDDHHMMSS.

4. Configure Log Directory

Set the LOG_DIR variable to the directory where you want to store log files. The script will create log files in this directory to track backup operations.

Usage

Running the Backup Script

Execute the script using Bash:

./backup_script.sh

The script will connect to the specified PostgreSQL database and create a backup in the designated directory. Log messages will be written to the specified log file.

Automating Backups with CronTab

To schedule regular backups using CronTab, follow these steps:

1. Open your terminal and type:

crontab -e

2. Add a new line to the CronTab file with the following format:

Example: Run the backup script every day at 1:00 AM

0 1 * * * /path/to/backup_script.sh

Replace /path/to/backup_script.sh with the actual path to your backup script.

3. Save and exit the CronTab file. Cron will now execute the backup script at the specified interval.

Notes

  • Ensure that the PostgreSQL client tools, particularly pg_dump, are installed and accessible from the system environment where the script will run.
  • It's recommended to periodically review the log files generated by the script to ensure backups are running smoothly.

For more information, please refer to the PostgreSQL documentation.