/code/pgsql-backup
This script is a hacked version of the automysql backup script (link on the left) to work with PostgreSQL databases -- a superior DBMS over MySQL IMHO.
Please report any problems via a comment on the AUR page for this software.
Configuration
By default, the script looks for it's configuration file in /etc/pgsql-backup/options.conf so if you've put it somewhere else, you'll need to edit the script and point it in the right direction. The script checks for the file at startup and handles it sanely if it can't find the file.
You should create a dedicated user in PostgreSQL to perform the database dumps such as:
CREATE ROLE dumper LOGIN SUPERUSER INHERIT;
The following configuration variables are available:
| Variable Name | Description | Type | Default |
USERNAME |
The username to connect to the PostgreSQL daemon as. This is the PostgreSQL username, not the system username. |
String | 'postgres' |
DB_PASS |
The password required for the username specified above. | String | |
DBHOST |
The hostname of the machine running the PostgreSQL daemon you wish to backup. If the script is running on the same machine as the daemon, use 'localhost' | String | 'localhost' |
CONNECT_DB |
The dump process needs an initial database to connect to when dumping. You can generally leave this as 'postgres', 'template1' or 'template0' unless you have deleted those databases. Any database that exists and the user specified in USERNAME can connect to is OK. |
String | 'postgres' |
DBNAMES |
A list of databases to include in the backup. Special keryword 'all' selects all existing databases at run time and backs them all up to individual files. |
String | 'all' |
BACKUPDIR |
The destination for your backup (ie, where to store your backups). Use an absolute path to avoid issues with environment changes, especially when using cron to automate your backups. Do not include a trailing slash. |
String | None |
BACKUP_USER |
The username to login to the remote host with. Ignored if USE_SSH is 0. |
String | None |
MAILCONTENT |
How to handle the output of the script: |
'log' or 'stdout' or 'quiet' |
'stdout' |
MAXATTSIZE |
Maximum size of log files to attach to e-mail in bytes. Don't forget the account for the overhead of the rest of the e-mail if you have maximum limits on e-mail sizes. This is only the attachment limit. |
String | 4000 |
MAILADDR |
Email address to send reports to according to MAILCONTENT | String | None |
MDBNAMES |
List of databases to backup monthly only. This list is in addition to the existing list of databases. It does not alter the existing list at all, so if you have a database listed here and in DBNAMES then it will be backed up everytime the script is run. Don't forget to leave $DBNAMES appended unless you want ONLY theses databases backed up during the monthly run. |
String |
'postgres template1' |
DBEXCLUDE |
List of databases to always exclude from the backup. Useful if you are using the 'all' setting for DBNAMES but want to exclude some specific databases. | String | None |
CREATE_DATABASE |
Include a CREATE DATABASE statement in the dumped output. |
'yes' or 'no' |
'yes' |
DOWEEKLY |
Which day to create a weekly archive of the dumps. Use '0' to not do weekly archives, 1 to 7 to indicate which day to create the weekly archive on, with 1 being Monday. |
0 to 7 |
'1' |
COMP |
Compression type to use for the backup. Database dumps can take a lot of room, but usually tend to compress very well. |
'gzip' or 'bzip2' |
'bzip2' |
LATEST |
If set to '1' the script will create a 'latest' directory with the 'BACKUPDIR' path and hard-link to the latest dump of each database. Makes it easy to locate / check when the last backup occured, as well as providing a static path to the latest backup for scripting restores, copies, Nagios file check etc. Note that not all filesystems support hard-links, so if you are using a filesystem that doesn't such as NTFS or FAT, you will need to disable this option. |
'1' or '0' |
'1' |
SOCKET |
If you need to connect to the database via a Unix socket, specify it here. If this is non-empty, it will override the DBHOST setting. | String |
None |
DUMPFORMAT |
Format of the database dump to pass to the 'pg_dump' utility. 'tar' makes the backup more flexible for restoration, but 'plain' is simpler. |
'tar' or 'plain' |
'tar' |
PREBACKUP |
Optional. A command to execute before the backup starts. eg, a database cleanup script. |
String | None |
POSTBACKUP |
Optional. A command to execute after the backup is complete. eg, a scipt to copy the output files to another server. |
String | None |