pgsql-backup - basic automated backup script for PostgreSQL
pgsql-backup [/path/to/pgsql-backup.conf]
Without options, the script will attempt to locate a configuration file in several locations:
~/.pgsql-backup(user-specific)/etc/pgsql-backup.conf(system-wide config)/etc/pgsql-backup/options.conf(legacy)
The last location above is a legacy location and should not be used. Existing users should relocate /etc/pgsql-backup/options.conf to the new path /etc/pgsql-backup.conf
This script is designed to be ran daily, but can be run more often. If running multiple times per day, only the most-recent backup for the day will be kept on disk. Rolling daily, weekly and monthly backups are created in the specified location for each database requested to be backed up. Backups can be optionally compressed to save diskspace. Encryption of the backup files is also available. Backups can also be optionally emailed as attachments.
Some of the configuration options in the rc file may be overridden on the command line at run time. An alternative/specific rc file may also be passed using the -c option.
- -c /path/to/rcfile
-
Use the specified rc configuration file.
- -d /path/to/output
-
Write the backup output to the specified path.
- -D dbname
-
Backup only the specified database name.
- -h
-
Help
The following configuration options exist in pgsql-backup.conf. The configuration file must have permissions rw------- (600) or less to protect any sensitive information (eg, usernames, passwords, encryption keys etc)
The username to connect to the PostgreSQL daemon as. This is the PostgreSQL username, not the system username.
Type: String
Default: postgres
The password required for the username specified above.
Type: String
Default:
The hostname or IP Address 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
Type: String
Default: localhost
The TCP port of the machine running the PostgreSQL daemon you wish to backup.
Type: Unsigned Integer
Default: 5432
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 PGUSER can connect to is OK. No changes will be made to the database you use
Type: String
Default: postgres
Space-separated list of databases to include in the backup. Special keyword all selects all databases the exist at run time. Each database will be backed up to it's own file.
Type: String
Default: all
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. The path specified here must exist before the first backup is run. Any missing subdirectories will be created as required.
Type: String
Default:
How to handle the output of the script:
log= always email the log file to the address in MAILADDRstdout= print the log to stdout. This may still get emailed to you if you are running the script from cron.quiet= only email if there is a problem.
Type: log or stdout or quiet
Default: stdout
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.
Type: Unsigned Integer
Default: 4000
Email address to send reports to according to MAILCONTENT
Type: String
Default: root
Space-separated 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.
Type: String
Default:
Include a CREATE DATABASE statement in the dumped output. If set to yes then pg_dump is called with with --create flag.
Type: yes or no
Default: yes
Make a dump of the PostgreSQL globals (ie login roles). If set to yes then pg_dumpall (NOT pg_dump) is called with with --globals-only flag and output is saved to a distinct file in the CONFIG_BACKUPDIR path.
Type: yes or no
Default: yes
Which day to create a weekly archive of the dumps. Use '0' to not do weekly archives. Valid values are 1 to 7 to indicate which day to create the weekly archive on, with 1 being Monday.
Type: 0 to 7
Default: 1
Compression type to use for the backup. Database dumps can take a lot of room, but usually tend to compress very well. The default is none because the "CONFIG_DUMPFORMAT" default is custom which uses in-built compression within the pg_dump utility so we don't need to perform our own compression. Set your preferred compression option here if you are using a "CONFIG_DUMPFORMAT" other than custom
Type: gzip or bzip2 or xz or none
Default: none
Whether to encrypt the output backup files. If enabled, you must also specify CONFIG_ENCRYPT_PASSPHRASE which is the passphrase to use for encryption. The encryption process is handled by openssl and uses the AES-256-CBC cipher. The appropriate command to decrypt the archives will be included in the backup log of each run (excluding the passphrase of course) for reference.
Type: yes or no
Default: no
The passphrase to be used when CONFIG_ENCRYPT is set to yes. Make sure this passphrase is stored securely to be able to decrypt in the event you need to restore backups. Without the passphrase, you will NOT be able to restore your backups. Wrap the value in quotes if it includes any whitespace (normal bash string quoting rules apply).
Type: String
Default:
If set to '1' the script will create a directory in BACKUPDIR called latest and hard-link to the latest dump of each database. This provides 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 then you will need to disable this option.
Type: 1 or 0
Default: 1
If you need to connect to the database via a Unix socket, specify it here. If this option is set non-empty, it will override the CONFIG_PGHOST setting.
Type: String
Default:
Format of the database dump to pass to the 'pg_dump' utility. Using custom is the most flexible format according to PostgreSQL developers.
NOTE: using the tar format requires sufficient temporary disk space (usually in /tmp) to dump an intermediatary copy of each database as part of the conversion to tar.
Refer to pg_dump(1) for more information on the 3 options.
Type: custom or tar or plain
Default: custom
umask controls the permissions of created directories and files. The default is fairly restrictive (700 for directories, 600 for files). You may like/need to relax for your environment. Refer to umask(1) for further information.
Type: Octal
Default: 0077
Optional. A command to execute before the backup starts. eg, a database cleanup script. See "HOOKS" below.
Type: String
Default:
Optional. A command to execute after the backup is complete. eg, a scipt to copy the output files to another server. See "HOOKS" below.
Type: String
Default:
The exit code of the script can be used to determine failure reasons.
- 0 = OK
- 1 = Unspecified Error
- 2 = Configuration File Error
- 3 = Permission Denied
- 4 = Dependency Error
The PostgreSQL user that is doing the backup (not the system user) should ideally be a dedicated user:
In a shell: $ createuser -D -i -l -P -R -s dumper
SQL Commands: CREATE ROLE dumper LOGIN SUPERUSER INHERIT; ALTER ROLE dumper WITH PASSWORD SuperSecretPassword;
Creating a dedicated backup user is beneficial for a couple of reasons:
- 1. Distribution of the credentials is limited to the location(s) that need to handle backups.
- 2. If the credentials are compromised, they can be revoked without affecting things other than backups.
The script has 2 hooks available for you to incorporate custom actions: PREBACKUP and POSTBACKUP
The command specified in POSTBACKUP will be executed after the dumps are complete, with the names of the dumps as arguments.
Your POSTBACKUP script should print the new names of altered files on stdout if it alters the names.
A sample hook is included: hook-postbackup.sh
Database dumps created by this script are unencrypted by default; you should take into consideration the following (incomplete list) of implications, especially if your database(s) store sensitive information such as usernames, passwords, credit card details or other personal information (especially those of your customers).
When writing to disk, ensure the backup directory has restictive permissions to prevent unauthorized users gaining access to the backups.
Enable encryption of the output files. See "CONFIG_ENCRYPT".
When emailing backup dumps, avoid the emails being trasmitted over untrusted networks (eg, the internet). Internal mail systems only. Be aware of mail systems that archive emails may retain copies of your backups.
If you don't administer the email server, please respect your email admin and don't email 2gb database backups to yourself each night ;)
Email bug reports to fukawi2@gmail.com
Technically not a bug, but PostgreSQL configuration files (eg, postgres.conf and pg_hba.conf) are NOT backed up by this utility. These files should be backup up separately. They are not handled by this script because the script is designed to be able to backup a remote server by connecting directly to the PostgreSQL server as a client. When connected in this manner, it is impossible to ask the server to send it's configuration files.
This script is a fork of "MySQL Backup Script" version 2.5 Copyright (c) 2002-2003 wipe_out@lycos.co.u kavailable from: http://sourceforge.net/projects/automysqlbackup/
Copyright 2010-2014 Phillip Smith
Made available under the conditions of the GPLv2. This is free software; refer to the LICENSE file for details.
https://github.com/fukawi2/pgsql-backup
Phillip Smith aka fukawi2