PostgreSQL-Backup-Automation-Steps

 Environment

Operating System	RHEL 5.5/CentOS 5.5
Database	PostgreSQL 9.1

Assumptions

Details of Database Server Machine

Server IP	192.168.0.1
PostgreSQL Installation (bin) Directory	/var/lib/PostgreSQL/9.1/bin
Data Directory (initdb)	/var/lib/PostgreSQL/9.1/data
Configuration Files Location  (postgresql.conf, pg_hba.conf)	/var/lib/PostgreSQL/9.1/data
PostgreSQL Owner OS User	postgres
Postgres Database Super User	postgres

Details of Backup Server (Server for NFS sharing)

Server IP

192.168.0.2

Sharing/Exporting directory on to another Linux machine

 Server Side Configuration (On NFS Server )

1.       Login as root user

su -

2.       make directory (assumed path /var/lib/PostgreSQL/9.1/)

mkdir db-backup-1

3.       Edit /etc/exports

vi  /etc/exports/

4.       add following line into 'exports’ and save the file

/var/lib/PostgreSQL/db-backup-1 *(rw, sync)

5.       Restart NFS service

/etc/init.d/nfs restart

Note: NFS must be listed as exception in Firewall

Client Side Configuration (On Database Server)

6.       Create a directory to be mount on shared directory at server

cd /mnt/

mkdir db-backup

7.       Mount the directory to the shared directory by executing following command

mount -t nfs 192.168.0.1:/var/lib/PostgreSQL/db-backup-1 /mnt/db-backup/

8.       Add following line into the /etc/fstab file and save it (as root user) to enable auto-mount at system startup

192.168.0.2:/var/lib/PostgreSQL/db-backup-1 /mnt/db-backup nfs defaults 0 0

Creating a cron job to automate daily backup at shared directory at backup server

1.       Create a directory for SQL backup within mounted directory

su - postgres

cd /mnt/db-backup/
mkdir sqlbackup

2.       Create a shell script with following commands and save it as ‘daily_backup.sh’
within data directory (or anywhere where postgres user has appropriate permissions)

vi /var/lib/PostgreSQL/9.1/data/daily_backup.sh

Add following command in the file (To take backup and include Current Date and Time in the dump file name)

/var/lib/PostgreSQL/9.1/bin/pg_dump -U jnk_ee_dba  empexchangedb > /mnt/db-backup/sqlbackup/$(date +"%Y-%m-%d_%H%M")_empexchangedb_jnk_ee_dba_16.sql

Save and exit the file

3.       Set authentication method to trust (without password) for backup user of database

vi /var/lib/PostgreSQL/9.1/data/pg_hba.conf

Add following line in the list authentication file

# TYPE   DATABASE                USER              ADDRESS     METHOD
local    sampledb                dbuser                         trust

Save and exit the file

4.       Reload the Postgres configuration

/var/lib/PostgreSQL/9.1/bin/pg_ctl –D /var/lib/PostgreSQL/9.1/data reload

5.    Make a cron job entry for time of backup as follows 

crontab –e

Add following line

35 5 * * * /var/lib/PostgreSQL/9.1/data/daily_backup.sh

Save and exit the file

Daily Backup Script will be executed at the specified time and backup will be done at shared directory of backup server.