Skip to content

Instantly share code, notes, and snippets.

@sirbrillig
Forked from dangerousbeans/pgsql_backup.sh
Last active June 11, 2024 05:41
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 '{}' ';'
@abhishekginani-tm
Copy link

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

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

@pranab1003
Copy link

pranab1003 commented Nov 26, 2020

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

@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.

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