Skip to main content

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:

As an example, we'll have a PostGIS database called parcels_postgis with the following:

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 topology schema's layer table before INSERT'ing records into the topology schema's topology table. Since the 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 the topology.topology table first, then the topology.layer table second. A modified script with the highlighted changes can be seen here.