We are no longer accepting contributions to Documentation. Please see our post on meta.

postgresql

Backup script for a production DB All Versions

topic discuss
8.4
9.0
9.1
9.2
9.3
9.4
9.5
9.6

This draft deletes the entire topic.

Examples

  • 0
    saveProdDb.sh
    0

    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
    created Nov 22 '16 at 21:12
    discuss
Please consider making a request to improve this example.

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
created Nov 22 '16 at 21:12
Still have a question about Backup script for a production DB? Ask Question

Topic Outline


    We are no longer accepting contributions to Documentation. Drafts cannot be modified.