There are several options for creating backups of PostgreSQL databases. However, for PostgreSQL databases used to store geodatabases, Esri recommends you use pg_dump to create an archive file, drop the database, re-create the database with the same name, and restore to the re-created database using pg_restore.
When you restore from an archive file, you can specify the order of schema restoration. Geodatabases in PostgreSQL require you restore schemas in a specific order.
To create a backup of a geodatabase in PostgreSQL, execute the pg_dump command as a superuser from an MS-DOS or shell command prompt. This creates an archive file you can use to re-create the 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 location and file name you want.
It is important that you read the PostgreSQL backup and recovery documentation, but keep the following in mind when using the pg_dump command:
- The pg_dump command creates a backup of only one database at a time, and it does not include information about roles or tablespaces.
- While pg_dump runs, only operations that require an exclusive lock will be blocked. As a result, updates made to the database while pg_dump is running are not in the resultant archive file.
- If you have PostGIS installed and use geometry or geography storage, the procedure to backup and restore could vary depending on the version of PostGIS you have. Be sure to read PostGIS documentation about creating backups and restoring databases.
As with all backup and restore plans, test your procedures to be sure the backup and restore will work.