Skip to content

Instantly share code, notes, and snippets.

@sirbrillig
Forked from dangerousbeans/pgsql_backup.sh
Last active July 23, 2024 11:54
Show Gist options
  • Save sirbrillig/4624937 to your computer and use it in GitHub Desktop.
Save sirbrillig/4624937 to your computer and use it in GitHub Desktop.
Postgresql daily backup script.
#!/bin/bash
#
# Backup a Postgresql database into a daily file.
#
BACKUP_DIR=/pg_backup
DAYS_TO_KEEP=14
FILE_SUFFIX=_pg_backup.sql
DATABASE=
USER=postgres
FILE=`date +"%Y%m%d%H%M"`${FILE_SUFFIX}
OUTPUT_FILE=${BACKUP_DIR}/${FILE}
# do the database backup (dump)
# use this command for a database server on localhost. add other options if need be.
pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}
# gzip the mysql database dump file
gzip $OUTPUT_FILE
# show the user the result
echo "${OUTPUT_FILE}.gz was created:"
ls -l ${OUTPUT_FILE}.gz
# prune old backups
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'
@email2ssk247
Copy link

Do you have the scripts which helps me password runtime

@anindya-biswas
Copy link

Thanks for the script I was looking for this. Let's see what the compliance people have to say about it.

@Hostraid
Copy link

Hostraid commented Jul 23, 2024

#!/bin/bash
#
# Backup a Postgresql database into a daily file.
#

BACKUP_DIR=/backup_psql
LOG_FILE=${BACKUP_DIR}/backup.log
DAYS_TO_KEEP=5
FILE_SUFFIX=_pg_backup.sql
DATABASE=database_name
USER=postgres
PGPASSWORD=new_password

# Set working directory to BACKUP_DIR
cd $BACKUP_DIR || exit

FILE=$(date +"%Y%m%d%H%M")${FILE_SUFFIX}
OUTPUT_FILE=${BACKUP_DIR}/${FILE}

{
  echo "Starting backup: $(date)"

  # do the database backup (dump)
  pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}
  if [ $? -eq 0 ]; then
    echo "Database backup successful"
  else
    echo "Database backup failed"
  fi

  # gzip the postgres database dump file
  gzip $OUTPUT_FILE
  if [ $? -eq 0 ]; then
    echo "Gzip successful"
  else
    echo "Gzip failed"
  fi

  # show the user the result
  echo "${OUTPUT_FILE}.gz was created:"
  ls -l ${OUTPUT_FILE}.gz

  # prune old backups
  find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'
  if [ $? -eq 0 ]; then
    echo "Old backups pruned successfully"
  else
    echo "Failed to prune old backups"
  fi

  echo "Backup completed: $(date)"
} >> ${LOG_FILE} 2>&1
sudo mkdir -p /backup_psql
sudo chown postgres:postgres /backup_psql
sudo chmod 700 /backup_psql
sudo crontab -u postgres -e
0 2 * * * /path/to/backup.sh

checking work script

sudo crontab -u postgres -l
sudo -u postgres /path/to/backup.sh
cat /backup_psql/backup.log

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment