PostgreSQL database backups and restore.

Here is a way to simplify backup snapshots of PostgreSQL databases on a Linux server.

Some assumptions made are:

• Ormandy is the user performing these snapshots.
• Ormandy has root access to the server.
• Ormandy is controlling file access.

Steps:

1. Create a folder in ormandy's home directory for the backup files, give the postgres group ownership, and give this group full access to the folder:

mkdir /home/ormandy/dbbackups
chown :postgres /home/ormandy/dbbackups
chmod 770 /home/ormandy/dbbackups

2. Create the shell script in our home folder to perform the backup:

nano /home/ormandy/backupdb.sh

Here are the contents with blank lines added for clarity:

#!/bin/bash

if [ "$1" == "" ]
then
  echo "Database name required as first parameter."
  echo "No backup performed."
  exit 1
fi

suffix=$(date +%F_%H%M%S)
filename="/home/ormandy/dbbackups/"$1.$suffix

pg_dump -Fc -O -f $filename $1

exit 0

3. Set permissions on the script similar to the backup folder:

chown :postgres /home/ormandy/backupdb.sh
chmod 770 /home/ormandy/backupdb.sh

4. Now all you have to do is use the script. To work around db user names and passwords we will impersonate the postgres account. The first su will require the root password, the second will not require a password, and the third line invokes our script to back up a database named flrc:

su
su postgres
/home/ormandy/backupdb.sh flrc

You can confirm the new file created with a name like this that includes date and time: flrc.2013-11-26_125133 and the ormandy user should have full access to it.
To return to being ormandy after the two su commands above you will need to exit (or Ctrl-D) twice.

How to restore a backup:

Restoring a backup uses the pg_restore command. Here is an example command line to restore a backup to the flfl database, assuming that flfl already exists on the server. You must change the file name to your specific source file:

pg_restore -c -d flfl /home/ormandy/DbBackups/flrc.2015-04-16_155259