Postgresql daily backup script.

pgsql_backup.sh

#!/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 '{}' ';'

Hellow

I need a script to windows,

help me please

@wduvan: https://github.com/CodMonk/PostgreSQL.Monk

This backups all the database exist on the server. This may help you.

Batch script for Windows PostgreSQL Backup

@echo off
SET day=%date:~0,2%
SET month=%date:~3,2%
SET year=%date:~6,4%
SET hh=%time:~0,2%
SET mm=%time:~3,2%

SET BACKUPDIR=D:\PGDB_Backup
SET datestr=%day%-%month%-%year%_%hh%-%mm%
SET dir=%day%-%month%-%year%
mkdir D:\PGDB_Backup%dir%

#Provide database name here
SET db1=dbname1
SET db2=dbname2

echo datestr is %datestr%

SET BACKUP_FILE1=D:\PGDB_Backup%dir%%db1%%datestr%.sql
SET FILENAME1=%db1%%datestr%.sql

SET BACKUP_FILE2=D:\PGDB_Backup%dir%%db2%%datestr%.sql
SET FILENAME2=%db2%%datestr%.sql

ECHO Backup file name is %FILENAME1% %FILENAME2%

SET PGPASSWORD=
echo on

#Execute Backup for database
D:\PGBIN\pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE1% %db1%
D:\PGBIN\pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE2% %db2%
echo Backup Completed

#Maintain Backup for 4 days
FORFILES /p %BACKUPDIR% /S /D -4 /C "cmd /c IF @isdir == TRUE RMDIR /S /Q @path"
echo Directory older than 4 days are deleted

Do you have the scripts which helps me password runtime

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

#!/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