DevTut

# Backup script for a production DB

# 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

    # 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](https://www.hds.com/en-us/products-solutions/data-protection/data-protection-suite.html), or [Symantec Backup](https://www.symantec.com/fr/fr/page.jsp?id=introducing-backup-exec-15), ... 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 (opens new window)).

    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

    ← Accessing Data Programmatically →