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
Parameters
| 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
- If there is a backup tool such as HDPS, or Symantec Backup, ... 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
