Database Backup/Restore The most critical component of any database maintenance plan is the database backup and restore procedures. Once again, PostgreSQL makes the administrator's job easier by providing command-line tools such as pg_dump , pg_dumpall , and pg_restore . Like commands such as vacuumdb , these are especially suited to be run as cron jobs. By default, pg_dump and pg_dumpall simply dump all output to stdout . However, this can easily be redirected to files using the appropriate UNIX redirection symbols. >pg_dump newriders > nr.backup After the command has redirected its output to a standard OS file, standard backup tools can be used to securely archive it. Here are some factors to consider when trying to evaluate an optimal backup plan. -
Does the entire system need to be backed up or just a specific database? If only one database is needed, the pg_dump command should suffice. If an entire cluster of databases is needed, however, use the pg_dumpall command. The two commands function almost identically; however, pg_dumpall is not capable of authenticating itself to every database it dumps. To run pg_dumpall against back ends that require authentication, set the environmental variable PGPASSWORD to the proper password, and that will be automatically relayed on each attempted connection. -
Will you need to selectively restore database files (that is, specific tables and so on)? Version 7.1 of PostgreSQL made some improvements to the pg_dump , pg_dumpall , and pg_restore commands. These commands allow the database dump to be stored in a special format. This new format provides a great deal of flexibility when it comes time to restore. The database schema, data, functions, or specific tables can be selectively restored. Additionally, this new format stores data in a compressed format that causes fewer problems when dealing with very large databases. For instance, to dump out the newriders database in the special format and then selectively restore only the payroll table: >pg_dump -Fc newriders > nr.backup.cust_fmt >pg_restore -d newriders -t payroll nr.backup.cust_fmt -
What will the resultant size of the dump file be? Many operating systems (like certain versions of Linux) have restrictions on the maximum allowable size of a single file (such as 2GB). Therefore, on a large database system, this could be problematic . There are a number of answers to this problem, ranging from upgrading your PostgreSQL database system to piping output through special tools. As previously mentioned, PostgreSQLVersion 7.1 introduced some new features to the pg_dump and pg_dumpall commands, which include the use of a new custom dump format. These additions include a new command-line option that indicates the compression level desired. For instance, to dump the newriders database at the maximum compression level possible (at the expense of speed): >pg_dump -Fc -z9 newriders > nr.backup.cust_fmt Alternatively, another method for achieving the same effect is to pipe the output of pg_dump through the gzip command. This can be done using any version of PostgreSQL and standard UNIX system commands: >pg_dump newriders gzip > nr.backup.zip And it can be restored by the following: >gzip -c nr.backup.zip psql newriders If the resulting zipped files are still too large, the other option is to use the split command. This example will split the output file into numerous 1GB files. >pg_dump newriders split -b 1024m - nr.backup And it can be restored by the following: >cat nr.backup.* psql newriders -
Are your configuration files, such as pg_options , pg_hba.conf , and pg_pwd , being regularly archived? For complex installations, lost configuration files can be very time consuming to try to re-create by hand. Make sure you have secure, offline copies of all your PostgreSQL configuration files. |
