Backup and Restore Scripts for PostgreSQL

Here are scripts to back up and restore a PostgreSQL database. These scripts can be run from the command line.

Backup Script

1. Full Database Backup

This script backs up the entire PostgreSQL database to a file.

#!/bin/bash

# Variables
PG_USER="your_postgres_user"
PG_DB="your_database_name"
BACKUP_DIR="/path/to/backup/directory"
BACKUP_FILE="$BACKUP_DIR/$(date +'%Y%m%d_%H%M%S')_${PG_DB}_backup.sql"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Perform the backup
pg_dump -U $PG_USER -F c -b -v -f $BACKUP_FILE $PG_DB

# Check if the backup was successful
if [ $? -eq 0 ]; then
  echo "Backup successful: $BACKUP_FILE"
else
  echo "Backup failed"
fi

Restore Script

1. Full Database Restore

This script restores the entire PostgreSQL database from a backup file.

#!/bin/bash

# Variables
PG_USER="your_postgres_user"
PG_DB="your_database_name"
BACKUP_FILE="/path/to/backup/directory/your_backup_file.sql"

# Drop the existing database
dropdb -U $PG_USER $PG_DB

# Create a new database
createdb -U $PG_USER $PG_DB

# Restore the backup
pg_restore -U $PG_USER -d $PG_DB -v $BACKUP_FILE

# Check if the restore was successful
if [ $? -eq 0 ]; then
  echo "Restore successful"
else
  echo "Restore failed"
fi

Explanation

• Backup Script

  • pg_dump: Command used to perform the database backup.
  • -U: Specifies the PostgreSQL user.
  • -F c: Specifies the format of the output file (c stands for custom format).
  • -b: Includes large objects in the dump.
  • -v: Provides verbose output.
  • -f: Specifies the output file.

• Restore Script

  • dropdb: Command to drop the existing database.
  • createdb: Command to create a new database.
  • pg_restore: Command used to restore the database from a backup file.
  • -U: Specifies the PostgreSQL user.
  • -d: Specifies the database to restore into.
  • -v: Provides verbose output.

Running the Scripts

1. Make the scripts executable:

   chmod +x backup.sh restore.sh
   

2. Run the backup script:

   ./backup.sh
   

3. Run the restore script:

   ./restore.sh