Backups Reorganization pt. 4: PostgreSQL

The first thing that I wanted to do in order to backup the data from various applications was to create an identically named space on the file system in which to dump data. I chose /var/data_backup/. Each client machine is using LVM extensively so I had to create the logical volume and auto-mount it to /var/data_backup/.

For the most part, creating the new LV was not a problem because two of the three servers had ample space. The third server, however, needed to have at least one existing LV reduced in order to have enough free space in the volume group.

Here are the steps I used to reduce the size of an existing LVM partition:

1. locally backup the partition to be reduced using, "rsync --archive"
2. stop the application that was making use of that partition, in this case, apache
3. umount the partition
4. run e2fsck against the partition
5. run resize2fs to reduce the size of the data on the partition
6. reduce the size of the partition using lvreduce
7. mount the partition
8. start the application that uses the partition
   

After that I was able to create the standard /var/data_backup/ partition as normal using LVM since there was now enough free space in the volume group.

Now it was time to actually backup the PostgreSQL database. After some research, it seemed that the preferred way to do this was to use pg_dumpall since it dumps both the application databases as well as the system database containing database roles and permissions. At this point I simply had to create the appropriate user and accesses in PostgreSQL and then setup the cron job on each target machine running PostgreSQL. Along the way, I had to set up automatic login on two of the boxes using a .pgpass file.

I tested the restore of the data on a throw away server back at the office. The idea is to read the dumped data into template1 for v. 7.3.* and postgres for 8.1.* on PostgreSQL. The restore appeared to be fine.

Lastly, of course, I needed to perform the one-time setup of the backup servers to grab the /var/data_backup/ directory on each machine. I also grabbed the /etc/ directory too since there is a lot of application configuration information there including pg_hba.conf file.

Next up will be...Subversion.