Configure backup system to preserve database integrity

---

The backup system copy every file in the server, but database files during backup can change and final result is not consistent: the batabase was corrupt.

To resolv problem you can use PostgreSQL WAL archire:

• http://www.postgresql.org/docs/current/static/continuous-archiving.html

In this guide we explain to configure PostgreSQL and BackupPC to backup batabase in the correct way.
The version of PostgreSQL is 9.5, but it work with previous versions >= 9.1 .

Server with MasonSQL application

• Copy in /root/.ssh/authorized_keys the key of user backuppc from backup server
• Install rsync package

apt-get install rsync

PostgreSQL

You must activate WAL archive, then PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory.
The log records every change made to the database's data files.

• Create dirs to collect WAL

mkdir /var/lib/postgresql/9.5/archive
chown postgres.postgres /var/lib/postgresql/9.5/archive
chmod 700 /var/lib/postgresql/9.5/archive

• In PostgreSQL file configuration /etc/postgresql/9.5/main/postgresql.conf you activate WAL archive and scripts to move files in

wal_level = archive
archive_command = 'test ! -f /var/lib/postgresql/9.5/backup_in_progress || (test ! -f /var/lib/postgresql/9.5/archive/%f && cp %p /var/lib/postgresql/9.5/archive/%f)'
archive_mode = on

• Is necessary to restart PostgreSQL to activate WAL archive

/etc/init.d/postgresql restart

BackupPC

BackupPC is a backup application very efficient that use rsync to transfer data over the net.

• http://backuppc.sourceforge.net/ BackupPC Home Page

To backup an application with MasonSQL you can use this configuration:

$Conf{BackupFilesExclude} = {  
   '/var/lib/postgresql/$PSQL_VER/main' => [  
     '/pg_xlog/',  
     '/postmaster.opts',  
     '/postmaster.pid'  
   ],  
   '/' => [  
     '/var/lib/postgresql/$PSQL_VER/'  
   ]  
 };  
 $Conf{RsyncShareName} = [  
   '/',  
   '/var/lib/postgresql/$PSQL_VER/main',  
   '/var/lib/postgresql/$PSQL_VER/archive'  
 ];  
 $Conf{DumpPreShareCmd} = '$sshPath -q -x -l root $host /opt/masonsql/utility/sql/backuppc_cmd $PSQL_VER Pre $share';  
 $Conf{DumpPostShareCmd} = '$sshPath -q -x -l root $host /opt/masonsql/utility/sql/backuppc_cmd $PSQL_VER Post $share';  

Remember to test connection from BackupPC server with user backuppc calling root@server_to_backup!

Script file backuppc_cmd

File /opt/masonsql/utility/sql/backuppc_cmd:

!/bin/bash
DATE="$(date +%Y-%m-%d)"
set -e
PSQL_VER="$1"
CMD="$2"
if [ "$CMD" != "Pre" -a "$CMD" !=  "Post" ]
then
  echo "ERROR: Command '$CMD' is not 'Pre' or 'Post'." >&2
  exit 2
fi
PSQL_DIR="/var/lib/postgresql/$PSQL_VER"
if [ ! -d "$PSQL_DIR" ] 
then
  echo "ERROR: Directory $PSQL_DIR inexistent." >&2
  exit 1
fi
if [ ! -d "$PSQL_DIR/archive" ]
then
  mkdir "$PSQL_DIR/archive"
  chown postgres.postgres "$PSQL_DIR/archive"
  chmod 700 "$PSQL_DIR/archive"
fi
SHARE="${3%/}"
if [ "$SHARE" = "$PSQL_DIR/main" ]
then
  cd "$PSQL_DIR"
  if [ "$CMD" = "Pre" ]
  then
    touch $PSQL_DIR/backup_in_progress
    echo "SELECT pg_start_backup('BackupPC_${DATE}');" | su -c 'psql postgres' postgres
  fi
  if [ "$CMD" = "Post" ]
  then
    rm $PSQL_DIR/backup_in_progress
    echo "SELECT pg_stop_backup();" | su -c 'psql postgres' postgres
  fi
fi
if [ "$SHARE" = "$PSQL_DIR/archive" -a "$CMD" = "Post" -a ! -f $PSQL_DIR/backup_in_progress ]
then
  rm $PSQL_DIR/archive/*
fi

Restoring

Before to restore files from backup ( if you have recent files in filesystem and you want to recovery recent database modifications ):

• Copy file from /var/lib/postgresql/5.1/main/pg_xlog/* to /var/lib/postgresql/9.5/archive_before/ (if necessary, create dir)

After retrieving the files with BackupPC:

• Remove any from /var/lib/postgresql/5.1/main/pg_xlog/ dir
• Verify if there is the file /var/lib/postgresql/9.5/backup_in_progress
  

if it exists means that the last backup had problems, then check the logs in backuppc! You are an incomplete backup!

If there are no problems (the file backup_in_progres not exists):

• You can skip this step if there is not files in dir or if you can recovery only file from backup
  • Copy the files from /var/lib/postgresql/9.5/archive_before/* to /var/lib/postgresql/9,5/main/pg_xlog/
• Modify pg_hba.conf to prevent access to users
• Copy /usr/share/postgresql/9.5/recovery.conf.sample in /var/lib/postgresql/9.5/main/recovery.conf
• Change the line:
  • restore_command = 'cp /var/lib/postgresql/9.5/archive/%f %p'
• Launch postgresql and wait for recovery (the recovery.conf files will renamed to recovery.done)
• Check the contents of the database
• Restore access in pg_hba.conf and relaunch the server
• Remove dir /var/lib/postgresql/9.5/archive_before/
• Remove file in /var/lib/postgresql/9.5/archive/*