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

parameter	details
save_db	The main backup directory
dbProd	The secondary backup directory
DATE	The date of the backup in the specified format
dbprod	The name of the database to be saved
/opt/postgres/9.0/bin/pg_dump	The path to the pg_dump binary
-h	Specifies the host name of the machine on which the server is running, Example : localhost
-p	Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections, Example 5432
-U	User name to connect as.
## Remarks#

It is necessary to empty the backup directory before each launch.

To avoid cluttering the backup tool because the backup of old files is supposed to be done.

To enable this feature please uncomment line N° 3.

rm -R / save_db / *

In the case where the budget does not allow to have a tool of backup, one can always use the tasks planner (cron command).

The following command is used to edit the cron table for the current user.

crontab -e

Schedule the launch of the script with the calendar at 11pm.

0 23 * * * /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