PostgreSQL backups
PostgreSQL backups
|
Release 9.3 |
|
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, 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 super user to create an archive file that can be used to re-create a database.
pg_dump –Fc <database_name> > <dump_file_name> –U <superuser_name>
For example,
pg_dump –Fc mypgdb > mypgdb1031.dump –U pgsde
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.