Postgresql Periodic Snapshots

backup_script.sh

#!/bin/bash

mkdir -p /home/hrms/infra_deploy/database_backups/backups

while IFS=: read -r host port database user password; do
  [ -z "$host" ] && continue
  backup_dir="/home/hrms/infra_deploy/database_backups/backups/$database"
  mkdir -p "$backup_dir"
  backup_file="$backup_dir/${database}_$(date +%F_%H-%M-%S).sql"

  PGPASSWORD="$password" pg_dump -U "$user" -h "$host" -p "$port" "$database" > "$backup_file"
  if [ $? -eq 0 ]; then
    echo "Backed up $database successfully"
  else
    echo "Backup failed for $database"
  fi
done < ~/.pgpass

Step1: Create pgpass and make it 600 | touch ~/.pgpass & chmod 600 ~/.pgpass
Step2: write credentials into ~/.pgpass | vi ~/.pgpass

localhost:5432:test_db1:testuser1:testpwd1
localhost:5432:test_db2:testuser2:testpwd2

Step3: install pgclient

sudo apt install curl ca-certificates gnupg
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update
sudo apt install postgresql-client-17

Step4: test the script

chmod +x ./backup_script.sh
./backup_script.sh

Step5: Put it into cronjob and make it backup of every 4 hour | crontab -e

0 */4 * * * /bin/bash ~/infra_deploy/database_backups/backup_script_remastered.sh