Backup of the PostgreSQL Database

At the end of the project I had to set up a simple but reliable backup for the PostgreSQL database. If you have some interest, I did describe the environment in a previous article "Ubuntu Server as Inexpensive Platform for J2EE". Here I describe the backup on that platform. With less changes the scripts works also on other UNIXes. The principle may be also used on MS Windows, but the implementation will be different.



I'm working on a server called canistracci.oil with the account petrol. I did create a database called drilling with the user offshore and the user backup.

The users

---
--- file name: 01_create_user.sql
---
--- please execute as postgres only, 
--- and be sure to be connected to the postgres database
---
--- The database owner
---
CREATE ROLE offshore LOGIN ENCRYPTED PASSWORD '99!cents'
   VALID UNTIL 'infinity';
COMMENT ON ROLE offshore IS 'drilling read / write user';
---
--- The backup user
---
CREATE ROLE backup LOGIN ENCRYPTED PASSWORD '50!cents'
   VALID UNTIL 'infinity';
COMMENT ON ROLE drilling IS 'drilling backup user';

The Tablespace

The folder /data/postgresql/drilling have to be created in advance using the commands: mkdir -p /data/postgresql/drilling and chown postgres:postgres as root at the UNIX console (or shell).

---
--- file name: 02_create_tablespace.sql
---
--- please execute as postgres only, 
--- and be sure to be connected to the postgres database
---
CREATE TABLESPACE drilling OWNER drilling LOCATION '/data/postgresql/drilling';

COMMENT ON TABLESPACE drilling IS 'drilling database tablespace';

The Database

---
--- file name: 03_create_database.sql
---
--- please execute as postgres only, 
--- and be sure to be connected to the postgres database
---
CREATE DATABASE drilling
  WITH ENCODING='UTF8'
       OWNER=offshore
       TEMPLATE=template1
       LC_COLLATE='en_US.UTF-8'
       LC_CTYPE='en_US.UTF-8'
       CONNECTION LIMIT=-1
       TABLESPACE=drilling;
COMMENT ON DATABASE drilling IS 'drilling database';

The Tables

The tables of the database drilling are: equipment, location, tenant. The details are out of scope.

The Grants
The user offshore is the owner of the database and doesn't need special grants but backup is a read-only technical user which needs at least the SELECT grant on every object we wants to backup.

---
--- file name: 05_grants.sql
---
--- please execute as postgres only, 
--- and be sure to be connected to the drilling database
---
GRANT CONNECT ON DATABASE drilling TO backup;
GRANT USAGE ON SCHEMA public TO backup;
---
--- PostgreSQL < 9
---
--- GRANTS ON TABLES generated with
---
--- SELECT 'GRANT SELECT ON ' || tablename || ' TO backup;' FROM pg_tables WHERE schemaname = 'public';
--- psql -qAt -c "select 'GRANT SELECT ON ' || tablename || ' TO backup;' FROM pg_tables WHERE schemaname = 'public'" | psql
---
GRANT SELECT ON equipment TO backup;
GRANT SELECT ON location TO backup;
GRANT SELECT ON tenant TO backup;
---
--- Introduced by PostgreSQL 9
---
--- GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
--- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
---
--- GRANTS ON SEQUENCES generated with
---
--- SELECT 'GRANT SELECT ON ' || relname || ' TO backup;' FROM pg_statio_user_sequences WHERE schemaname = 'public';
---
GRANT SELECT ON equipment_id_seq TO backup;
GRANT SELECT ON location_id_seq TO backup;
GRANT SELECT ON tenant_id_seq TO backup;

Trusting Backup
I add a local trust to the database configuration for the user backup editing the file /etc/postgresql/8.4/main/pg_hba.conf

petrol@canistracci.oil $ sudo nano /etc/postgresql/8.4/main/pg_hba.conf
. . .
local   all         postgres                          md5
local   drilling    backup                            trust
. . .
petrol@canistracci.oil $ sudo /etc/init.d/postgresql restart

Now, using pg_dump I may dump the content of the database. The command line is the following: sudo pg_dump -E UTF-8 -U backup -w drilling.

The Backup Script
The script exports the database as very long PG-SQL script which may be used to restore the database. After the export the PG-SQL script is compressed to the binary ZIP format using the best possible compression.

#!/bin/sh
# backup.sh    This script stores all valuable parts of the database.
#
# description : 1) Performs the backup of the drilling database
# process name: backup.sh
#
# Version 1.0 Daniele Denti

LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH

BACKUP_DIR=/data/backup/drilling

NOW=`date +'%Y_%m_%d'`
TIMESTAMP=${1:-$NOW}

BACKUP_LOG=/var/tmp/drilling.backup.$TIMESTAMP.log
DB_DUMP=${BACKUP_DIR}/drilling.$TIMESTAMP.sql
PG_ZIP_FILE=$BACKUP_DIR/drilling_$TIMESTAMP.zip

CURDIR=`pwd`

echo [`date +'%d.%m.%Y %T'` INFO $0 ] started backup.sh on `uname -n`, executed by `id`, process id = $$ >$BACKUP_LOG

pg_dump --column-inserts -E UTF-8 -f ${DB_DUMP} -U backup -v -w drilling >>$BACKUP_LOG 2>&1

cd ${BACKUP_DIR}
zip -9rv $PG_ZIP_FILE ${DB_DUMP} >>$BACKUP_LOG 2>&1

echo [`date +'%d.%m.%Y %T'` INFO $0 ] backup.sh completed >>$BACKUP_LOG

cd $CURDIR

The Clean Up Script
The backup produces new files every day, so it is necessary to regularly delete the older ones.

#!/bin/sh
# cleanup.sh   This is the daily script for cleaning the drilling environment
#
# description : - Performs the removal of old (at least 14 days) SQL and ZIP files.
#               - Performs the removal of old (at leas 7 days) log files.
# process name: cleanup.sh
#
# Version 1.0 Daniele Denti

BACKUP_DIR=/data/backup/drilling

FILE_LIST="*.log *.sql *.zip"
for FILE_TYPE in ${FILE_LIST}; do
  find ${BACKUP_DIR} -name ${FILE_TYPE} -atime +14 -exec rm {} \; >/dev/null 2>&1
done

find /var/tmp -atime +7 -exec rm -f {} \; >/dev/null 2>&1

The Cron Table
On a freshly installed Ubuntu server execute as root echo ALL >>/etc/cron.deny and echo petrol >>/etc/cron.allow.
In order to edit the cron table execute export EDITOR=vi; crontab -l


# m h  dom mon dow   command
0 22 * * * /home/petrol/bin/backup.sh 2>&1
0  5 * * * /home/petrol/bin/cleanup.sh 2>&1

The Remote Script
Now the backup works but the server is into the DMZ and the backup files are confined on the data disk. If something goes wrong the data may be lost. So I copy the data into the Intranet on a second Ubuntu Server named pipeline.oil using the account grease.

The authorization of grease@pipeline.oil on petrol@canistracci.oil is described in the previous article "Key-Based SSH Logins".

#!/bin/sh
# remote.sh    This is the daily script to get the drilling backup on the
#              Intranet central backup server.
#
# description : Gets the last backup from remote.
# process name: remote.sh

# Version 1.0 Daniele Denti

BACKUP_DIR=/data/backup/drilling
BACKUP_REMOTE=/data/backup/drilling

NOW=`date +'%Y_%m_%d'`
TIMESTAMP=${1:-$NOW}

PG_ZIP_FILE=drilling_$TIMESTAMP.zip
REMOTE_LOG=/var/tmp/drilling.remote.$TIMESTAMP.log

OIL_USER=petrol
OIL_SERVER=canistracci.oil

echo [`date +'%d.%m.%Y %T'` INFO $0 ] started remote.sh on `uname -n`, executed by `id`, process id = $$ >$REMOTE_LOG

if [ -f ${BACKUP_REMOTE}/${PG_ZIP_FILE} ]; then
        rm -f ${BACKUP_REMOTE}/${PG_ZIP_FILE}
fi

scp ${OIL_USER}@${OIL_SERVER}:${BACKUP_REMOTE}/${PG_ZIP_FILE} ${BACKUP_DIR}/${PG_ZIP_FILE} >> $REMOTE_LOG 2>&1

echo [`date +'%d.%m.%Y %T'` INFO $0 ] remote.sh completed >>$REMOTE_LOG