Back up PostGIS and prove it can be restored
A backup is useful only if it can recover data, extensions, permissions and consumers within the required time.
Editorial review: 2026-09-23
The existence of a backup file does not prove recoverability. Define acceptable data loss and service downtime. Those objectives determine whether a logical dump is sufficient or whether physical backups and point-in-time recovery are needed.
A minimal disposable-database test
Use a gis_lab database containing only synthetic data and a new destination named gis_restore_lab. Configure authentication outside commands. A dump may contain sensitive information even when no rows are printed; store it with restricted permissions.
pg_dump --format=custom --file=gis_lab.dump --dbname=gis_lab
pg_restore --list gis_lab.dump
createdb gis_restore_lab
pg_restore --exit-on-error --no-owner --dbname=gis_restore_lab gis_lab.dump
psql --dbname=gis_restore_lab --command='SELECT PostGIS_Full_Version();'
Compatible extensions must be available on the destination server. --no-owner avoids requiring original owners in this laboratory; it is not a production permission policy. Restore and check owners, privileges and accounts according to your actual design. createdb should fail if the database already exists; do not delete a database to repeat the test without reviewing its contents and authorization.
Check data and consumers
Compare table keys and counts, empty geometries, indexes and representative queries. Open a restored layer in QGIS with a read-only account. When publishing through GeoServer or pygeoapi, change only the test-environment connection and verify responses and permissions. Recovering tables is insufficient if the server catalog or integration credentials are missing.
Roles and other global dependencies need separate treatment from a single-database dump. Recover credentials through your secrets manager rather than including them in public instructions. Document PostgreSQL, PostGIS and related library versions as well.
Daily operations
Monitor disk capacity, connections, long transactions, locks, errors and the age of the latest usable backup. Assign alert ownership and a concrete response. An alert without an owner does not reduce recovery time.
Test restores periodically and before major upgrades. Record backup, transfer, restore and consumer-validation durations separately. Repeat with representative data as volume grows. Restoring ten rows does not establish that the whole system can be recovered within an hour.
Version migrations
Read supported upgrade paths and rehearse on a copy. Maintain a rollback route that includes data compatibility, not just an older binary. Do not promise instant recovery when the new system has already accepted writes unknown to the previous system.
Choose recovery objectives before choosing a backup tool
Ask two operational questions: how much work can be lost, and how long can the service be unavailable? A nightly logical dump may suit a low-change reference dataset but fail a field operation that cannot lose a day's inspections. Shorter recovery points can require continuous archiving and a tested physical-backup strategy. High availability and backups solve different failure modes; a replica can reproduce an unwanted deletion.
Define the scope of recovery as a usable workflow. The database, service configuration, styles, attachment store, application build, identity integration and secrets may have different backup mechanisms. Record their dependencies so a recovered database is not stranded behind a missing map-server configuration.
Rehearse a recovery from beginning to end
- Identify a synthetic reference record and attachment, and record the expected count and a known query result.
- Produce the backup and record when it represents a consistent data state.
- Restore to a new destination with compatible extensions and a clearly different connection name.
- Recreate intended roles and grants without copying unnecessary production privileges into the lab.
- Open the restored layer in QGIS and query it through the test service account.
- Verify the reference attachment, one relation, a spatial filter and an unauthorized edit rejection.
- Record elapsed time for retrieval, restoration, configuration and consumer checks separately.
A restore command exiting successfully is only one step. The person responding to an incident needs to know where the backup is, how to access it when the primary system is unavailable, and which checks establish readiness for users.
Keep backup and publication states consistent
Suppose a new dataset version requires a new style field. Restoring the older database while keeping the newer style may produce a broken map even though every row is intact. Similarly, restoring attachment metadata without the referenced files creates records that look complete in SQL but fail in the fieldwork interface.
Choose consistent recovery points across dependent stores or document reconciliation. Keep a version identifier for the application and configuration associated with the data state. Do not put secrets in a public archive; store their recovery instructions in the organization's existing secure system.
Exercise the failures that backups do not automatically solve
| Failure | What the rehearsal must prove |
|---|---|
| Backup file corrupted or incomplete | Another usable copy exists and can be identified |
| Wrong extension version | Compatible restore environment can be provisioned |
| Accidental valid deletion | Desired recovery point can be selected and reconciled |
| Missing global roles | Role ownership and permissions are reconstructed |
| Attachment store unavailable | Documents recover with their owning records |
| Primary administrator unavailable | Another authorized operator can execute recovery |
Store backups in a failure domain appropriate to the risk. A backup beside the only database disk may disappear in the same incident. Protect retained copies from unauthorized access and deletion, because they can contain more historical information than the active public service.
Use recovery evidence in the cost comparison
Include storage, retention, restore environments and operator time in the open-stack estimate. Compare with the same recovery scope in the existing architecture. A lower hosting bill achieved by dropping recoverability is a different service level, not proven savings. The TCO calculator separates infrastructure and internal labor so those costs remain visible.
Reference: PostgreSQL continuous archiving and point-in-time recovery.
Sources and documentation
Next step
Continue in the Open GIS collection. For a specific project, use the total-cost calculator and request an assessment.