PostgreSQL backups

There are several options for creating backups of PostgreSQL databases. However, for PostgreSQL databases used with ArcSDE, 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 an ArcSDE geodatabase for 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 at http://www.postgresql.org/docs/9.0/static/app-pgdump.html 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 backup and recovery documentation available for PostgreSQL.

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.