postgreSQL Backup script for a production DB
Syntax
•The script allows you to create a backup directory for each execution with the following syntax : Name of database backup directory + date and time of execution
• Example : prodDir22-11-2016-19h55
•After it's created, it creates two backup files with the following syntax : Name of database + date and time of execution
• Example :
• dbprod22-11-2016-19h55.backup (dump file)
• dbprod22-11-2016-19h55.sql (sql file)
• At the end of one execution at 22-11-2016 @ 19h55, we get :
• /save_bd/prodDir22-11-2016-19h55/dbprod22-11-2016-19h55.backup
• /save_bd/prodDir22-11-2016-19h55/dbprod22-11-2016-19h55.sql
Examples
saveProdDb.sh
In general, we tend to back up the DB with the pgAdmin client. The following is a sh script used to save the database (under linux) in two formats:
• SQL file: for a possible resume of data on any version of PostgreSQL.
• Dump file: for a higher version than the current version.
#!/bin/sh
cd /save_db
#rm -R /save_db/*
DATE=$(date +%d-%m-%Y-%Hh%M)
echo -e "Sauvegarde de la base du ${DATE}"
mkdir prodDir${DATE}
cd prodDir${DATE}
#dump file
/opt/postgres/9.0/bin/pg_dump -i -h localhost -p 5432 -U postgres -F c -b -w -v -f
"dbprod${DATE}.backup" dbprod
#SQL file
/opt/postgres/9.0/bin/pg_dump -i -h localhost -p 5432 -U postgres --format plain --verbose -f
"dbprod${DATE}.sql" dbprod