postgresql Backup script for a production DB

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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

parameterdetails
save_dbThe main backup directory
dbProdThe secondary backup directory
DATEThe date of the backup in the specified format
dbprodThe name of the database to be saved
/opt/postgres/9.0/bin/pg_dumpThe path to the pg_dump binary
-hSpecifies the host name of the machine on which the server is running, Example : localhost
-pSpecifies the TCP port or local Unix domain socket file extension on which the server is listening for connections, Example 5432
-UUser name to connect as.

Remarks

  1. 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 / *
  1. 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


Got any postgresql Question?