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