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.