Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Database Maintenance
The most critical component of any database maintenance plan is the database backup and restore procedures. PostgreSQL makes the administrator's job easier by providing command-line tools, such as pg_dump, pg_dumpall, and pg_restore.
By default, pg_dump and pg_dumpall simply dump all output to stdout. However, this can easily be redirected by using the appropriate UNIX redirection symbols, as shown in the following code:
$pg_dump newriders > nr.backupAfter the command has redirected its output to a standard OS file, standard backup tools (such as tar and dump) can be used to securely archive it.
Some factors to consider when evaluating an optimal backup plan are as follows:
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; however, if an entire cluster of databases is needed, use the pg_dumpall command. The two commands function almost identically, but pg_dumpall is not capable of authenticating itself to every database it dumps.
Will you need to selectively restore database files (such as specific tables)? Version 7.1 of PostgreSQL made some improvements to the pg_dump, pg_dumpall, and pg_restore commands. These commands enable the database dump to be stored in a special format. This new format allows a great deal of flexibility when it comes time to restore. The database schema, data, functions, and specific tables can be selectively restored.
What will the size of the dump file be? Many operating systems (such as certain versions of Linux) have restrictions on the maximum allowable size (such as 2GB) of a single file. Therefore, on large database systems, this could be problematic. However, by using standard UNIX commands, such as split or gzip, you can overcome many of these obstacles even on older systems. For instance, to dump the database authors using gzip and to later restore it, perform the following:
$pg_dump authors | gzip > authors.backup.gz $gzip -c authors.backup.gz | psql authors