Using pg_dump with PostGIS Topology
Using the pg_dump utility is a standard
method for backing up a PostgreSQL database. Furthermore, limiting a
pg_dump export to a particular schema increases the portability of your geospatial data across different versions of PostGIS.
Since the PostGIS function signatures themselves are not copied in the data backup, data can be
backed up from a database under one version of PostGIS and restored into a new database of a differing version. Boundless has a great intro
guide to backing up and restoring PostGIS databases.
Working with PostGIS Topologies however, requires a special tweak. A typical PostGIS topology contains data spread across three schemas:
- Topology 'Metadata' schema, containing registered topologies and their layers.
Created by executing the statement
CREATE EXTENSION postgis_topology;.
- Schema containing topology primitives. This is created as the result of a call to CreateTopology()
- Schema containing topogeometry columns. These would be created by a call to AddTopoGeometryColumn
As an example, we'll have a PostGIS database called
parcels_postgis with the following:
- Topology extension enabled
- Topology primitives are stored in the
- Topogeometry columns are stored in the
Under this example, a call to
pg_dump looks like the following:
pg_dump --schema=topology --schema=parcels --schema=parcel_topology --file=parcel_data.sql parcels_postgis
Restoring to a new db however, produces the following error:
psql:test_data.sql:644: ERROR: insert or update on table "layer" violates foreign key constraint "layer_topology_id_fkey" DETAIL: Key (topology_id)=(1) is not present in table "topology".
This is because the
pg_dump utility defaults to INSERT'ing records into the
layer table before INSERT'ing records into the
topology table. Since
layer table has a foreign key constraint with the
topology table, this can never work properly.
One solution therefore, is to manually edit your
pg_dump output file to run
COPY commands onto
topology.topology table first, then the
topology.layer table second. A modified script with
the highlighted changes can be seen here.