Backup and Restore

 Database backups allow databases to be restored if a disk drive fails, a table is accidentally dropped, or a database file is accidentally deleted. If the databases are idle, a standard file system backup will suffice as a POSTGRESQL backup. If the databases are active, you must use the pg_dumpall  utility to create a reliable backup. This utility outputs a consistent snapshot of all databases into a file that can be included in a file system backup. In fact, once a pg_dumpall  file has been created, you do not need to back up the /data/base database files. A few configuration files in /data, such as data/pg_hba.conf, should be included in a file system backup because they do not appear in the pg_dumpall  file. The pg_dump utility can dump a single POSTGRESQL database.

To restore from a backup using a pg_dumpall  file, POSTGRESQL must be initialized, any manually edited configuration files restored to /data, and the database dump file run by psql. This action will recreate and reload all databases.

Individual databases can be reloaded from pg_dump  files by creating a new database and loading it using psql. For example, Figure  creates an exact copy of the test database.  

        $ pg_dump test > /tmp/test.dump
        $ createdb newtest
        CREATE DATABASE
        $ psql newtest < /tmp/test.dump  

It dumps the contents of the database into the file /tmp/test.dump. A new database called newtest is created, then the dump file is loaded into the new database.

Dump files contain ordinary SQL queries and COPY  commands. Because the files contain database information, they should be created so that only authorized users have permission to read them. See the pg_dump and pg_dumpall manual pages for more information about these commands.