Question

PostgreSQL 16 Autobackup Script for CentOS 9

Answer 1

1. Adjust Permissions on /var/log/postgresql_backup.log

If you want to keep the log in /var/log/, you can grant the postgres user permission to write to this file:

su -
sudo touch /var/log/postgresql_backup.log
sudo chown postgres:postgres /var/log/postgresql_backup.log
sudo chmod 644 /var/log/postgresql_backup.log

This will create the log file, change its ownership to the postgres user, and allow it to write logs.

2. Fixing the Backup Directory Permission Error

Ensure that the postgres user has write permissions on the /mnt/backup/ directory:

sudo chown -R postgres:postgres /mnt/backup
sudo chmod 700 /mnt/backup

This will set the appropriate ownership and permissions so the postgres user can write to this directory.

3. Run Script

After fixing the permissions, re-run the script:

su - postgres

./backup.sh

backup script.

#!/bin/bash

# Define Variables
BACKUP_DIR="/mnt/backup"     # Directory where the backups will be stored
LOGFILE="/var/log/postgresql_backup.log"   # Log file location
PGUSER="postgres"                          # PostgreSQL user with backup privileges
PGPASSWORD="admin@123"                     # Password for PostgreSQL user
DATABASE="db_chirags"              	   # Name of the database you want to backup
RETENTION=7                                # Number of days to retain backups

# Date format for the backup file
DATE=$(date +"%Y-%m-%d_%H-%M-%S")

# Backup file name
BACKUP_FILE="${BACKUP_DIR}/${DATABASE}_${DATE}.sql"

# Function to write to log
log() {
    echo "$(date +"%Y-%m-%d %H:%M:%S") - $1" >> $LOGFILE
}

# Create Backup Directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Backup Database
log "Starting backup for database: $DATABASE"
export PGPASSWORD=$PGPASSWORD
pg_dump -U $PGUSER -F c -b -v -f "$BACKUP_FILE" $DATABASE

# Check if the backup was successful
if [ $? -eq 0 ]; then
    log "Backup successful: $BACKUP_FILE"
    
    # Compress the backup file
    tar -czf "${BACKUP_FILE}.tar.gz" -C "$BACKUP_DIR" "$(basename $BACKUP_FILE)"
    rm -f "$BACKUP_FILE"
    log "Backup compressed to: ${BACKUP_FILE}.tar.gz"
else
    log "Backup failed for database: $DATABASE"
    exit 1
fi

# Remove old backups (older than retention period)
log "Cleaning up old backups..."
find $BACKUP_DIR -name "*.tar.gz" -mtime +$RETENTION -exec rm -f {} \;
log "Backup cleanup completed."

log "PostgreSQL backup completed for database: $DATABASE"