PostgreSQL backups

There are several options for creating backups of PostgreSQL databases. However, for PostgreSQL databases used to store geodatbases, the recommended method is to use pg_dump to create an archive file, drop the database, re-create the database with the same name, then restore to the re-created database.

One advantage to running pg_dump to create a backup is it only blocks other operations that require an exclusive lock. However, any updates made to the database while pg_dump is running are not in the resultant file.

Archive file formats rebuild the database using pg_restore. Archive file formats can be used on different operating systems.

To create a backup of a geodatabase in PostgreSQL using pg_dump, execute the pg_dump command as a superuser from an MS-DOS or shell command prompt to create an archive file that can be used to re-create a database.

pg_dump –U <superuser_name> –F c <database_name> > <dump_file_name>

For example:

pg_dump –U postgres –F c mypgdb > mypgdb1031.dump

In the previous example, the dump file is saved to the directory from which the command is executed. To designate a different location, specify the –f option with the directory locaton and file name you want. See the PostgreSQL documentation for details.

Note:

The pg_dump command can only create a backup of a single database at a time, and it does not include information about roles or tablespaces.

As with all backup and restore plans, test your procedures to be sure the backup and restore will work.

It is important that you read the PostgreSQL backup and recovery documentation.

Tip:

If you have PostGIS installed and are using Geometry storage, be sure to read PostGIS's documentation about creating backups and restoring databases. This procedure could vary depending on the version of PostGIS you are using.