pg_maint PostgreSQL Backup Script

A nightly backup and maintenance script for PostgreSQL servers.

Copyright © 2014 by Daniel G. Delaney, Fluid Mind Software
This script is free and open source. You may copy and modify it in any way you wish as long as you keep the copyright information at the top of the script.

This is a fairly simple script that reads the list of databases in your PostgreSQL instance, and runs pg_dump for them, placing the output files into separate folders in the backup directory and naming each file with the database name and the date of backup. It then deletes all backup files older than the specified number of days and finally runs vacuumdb and reindexdb—either daily, weekly, or monthly—to clean the old crud out of the tables. Optionally, it can also rsync the entire backup directory to another server.

Installation and Usage

NOTE: DO NOT RUN THIS SCRIPT AS ROOT! It is not designed to run as root and could be very dangerous. This script is designed to be run by the postgres user.

Download the perl script either as a text file or as a gzipped file (you might have to right-click those links and choose "Save as"). Place this script anywhere on your server, it doesn't have to be in a directory in the PATH. You might put it in /usr/local/sbin, or possibly in a "bin" directory in the postgres user's home directory. Just make sure the postgres user has access to execute it.
Create a directory where backups will be stored. Set the owner and group to the user that your postmaster daemon runs under (usually 'postgres'). Set it's permissions to 2770. For example:
```
mkdir /srv/backup/hostname/pgsql
chown postgres:postgres /srv/backup/hostname/pgsql
chmod 2770 /srv/backup/hostname/pgsql
```
Edit the options in the OPTIONS area at the beginning of the script. Options include:
- $backupDir – The Directory where backup files should be stored. Could be something like '/srv/backup/pgsql'
- $format – Format of the backup file: 'p' = plain, 'c' = custom, 'd' = directory, 't' = tar. See man page for pg_dump(1) for details on those formats. The default is 'c', which can be used with pg_restore(1).
- $vacuum – How often to VACUUM all databases at the end of the backup, '' for none, 'D' for daily, 'W' for weekly, 'M' for monthly
- $reindex – How often to reindex all databases at the end of the backup, '' for none, 'D' for daily, 'W' for weekly, 'M' for monthly
- $remote – You can optionally have this script use rsync through SSH to copy your backup files to a remote location. Specify the location the way you would with the rsync command. For example: 'postgres@example.com:/srv/backup/hostname/pgsql'.
- $daysToKeep – How many days to keep old backup files. The script will automatically remove backup files older than this number of days.
- $excluded – List of databases NOT to backup. Note the format of this list, it is exacly what goes in an SQL "IN" predicate. The default is "'template0','template1'".
To run the script every night at 1:00 AM, just login as root, type 'su - postgres' to become the postres user, type 'crontab -e' and add the following line to the file (using your path to the script):
```
0   1   *   *   *   /usr/local/sbin/pg_maint -noprompt -silent
```
To rsync files to a remote server, the postgres user will have to have an SSH key (type 'ssh-keygen -t rsa' as the postgres user to generate one) and the public key will have to be placed into the "authorized_keys" file in the home directory of the user you're logging in as on the remote server (in that example, the "postgres" user). Just type "cat ~/.ssh/id_rsa.pub" and copy the whole thing, then paste it into the ~/.ssh/authorized_keys file on the remote server. (Note that sshd on some servers defaults to not using the authorized_keys file. In the /etc/ssh/sshd_config file on the remote server, the following options will need to be uncommented: RSAAuthentication, PubkeyAuthentication, AuthorizedKeysFile.)