Restore a PostGIS Topology Safely with pgtopo_import

pgtopo_import turns a pgtopo_export archive back into SQL, so you can read exactly what it is about to do before it touches a live database. That review step is the whole point.

Allow about twenty minutes, plus time to actually read the generated SQL rather than skim it. The examples use pgtopo_import from PostGIS package version 3.4.2+dfsg-1ubuntu3, installed here on Linux. You need a readable export made by pgtopo_export, the PostGIS utilities package, a target database, and credentials with enough permission to create the topology and any layers you choose to include. The importer only reads an archive and writes SQL; the later psql step is the one that actually changes the database, so keep the export around as a recovery source until that step is done.

1. Check the installed contract

Confirm which executable you are running and note the package version. Both of these are read-only:

$ command -v pgtopo_import
/usr/bin/pgtopo_import
$ dpkg-query -W -f='${Package} ${Version}\n' postgis
postgis 3.4.2+dfsg-1ubuntu3
$ pgtopo_import </dev/null
Usage: pgtopo_import [ --skip-layers | --only-layers ] [ -f <dumpfile> ] <toponame>

That last command exits non-zero because you fed it nothing usable, but the usage line is exactly what you wanted from it. The local manual lists -h as a supported option, yet this packaged build still tried to read empty input when tested with -h anyway. Trust the installed manpage and the usage line above as the real contract for this host, not what another release's help text promises.

2. Preserve the export before you import it

Keep the original export wherever your recovery process expects it. Do not edit it in place, and do not delete it after a successful restore. If it arrived under an unclear name, inspect it before doing anything else:

$ ls -lh /path/to/city_data.pgtopo_export
$ file /path/to/city_data.pgtopo_export

The exact file description depends on the archive and the tools you have installed. Only a matching PostGIS export utility can produce a valid one; a random gzip or tar archive will not do, since the importer expects the PostGIS export format specifically. If the file looks truncated, stop and get a complete export rather than trying to patch the archive by hand.

Do not run the generated SQL against production as your first test. Pick a disposable database or a proper maintenance window, and confirm the target database, host and user before the next command. Authentication and the permissions the SQL needs are outside pgtopo_import's remit entirely.

3. Generate SQL without applying it

Use -f when the export is a named file, and pick a new output name so an older, already-reviewed script does not get quietly replaced:

$ pgtopo_import -f /path/to/city_data.pgtopo_export city_data > city_data.sql
$ test -s city_data.sql && echo 'SQL file created'
SQL file created

city_data here is the name the recreated topology will get. It does not have to match the name it had when it was exported. The command normally prints no progress at all, because the SQL is its standard output, and a non-empty file only tells you it produced something, not that it will succeed against the destination.

It reads from standard input too, which lets you chain exporter and importer directly:

$ pgtopo_export source_db city_data | pgtopo_import city_data > city_data.sql

Even piped like that, it still only produces an SQL file; it never touches the destination database until you invoke psql yourself. Keep the source and destination database names explicit in your notes, mixing those two up is a fast way to restore into the wrong place.

4. Decide whether layers belong in this restore

By default the generated SQL includes the topology and its layers. Layers are the part most likely to collide with tables already sitting in the target database, so make this call before you apply anything.

Topology schema only, no layers:

$ pgtopo_import --skip-layers -f /path/to/city_data.pgtopo_export city_data > city_data-topology.sql
$ test -s city_data-topology.sql && echo 'topology-only SQL created'
topology-only SQL created

Layer restoration and linking only, no topology schema:

$ pgtopo_import --only-layers -f /path/to/city_data.pgtopo_export city_data > city_data-layers.sql
$ test -s city_data-layers.sql && echo 'layers-only SQL created'
layers-only SQL created

--skip-layers and --only-layers are mutually exclusive, and layers-only SQL cannot work before the named topology already exists. If the target already has tables using the exported layer names, generate topology-only SQL first and sort out the naming plan separately, before you go anywhere near the layers.

5. Apply the reviewed SQL

$ psql -d staging_db -f city_data.sql

Add your normal connection options such as -h, -p and -U for a remote database. Never put a password directly in a shell command or in a file other users can read. If psql reports an error, keep the message and stop rather than rerunning it against a different database out of frustration. Read the failing statement and the target state first.

There is no general undo here. Recovery means restoring the target from its backup, or carefully removing whatever the reviewed SQL created according to your own change procedure. Keep both the original export and the exact SQL file used, so the whole operation can be audited or repeated later.

6. Verify the recreated topology

Query the topology catalogue in the target database, a read-only check for whether the name you expect is actually registered:

$ psql -d staging_db -c "SELECT topology_id, name, srid, precision, hasz FROM topology.topology WHERE name = 'city_data';"
 topology_id |   name   | srid | precision | hasz
-------------+----------+------+-----------+------
          7  | city_data | 4326 |         0 | f
(1 row)

Identifier, spatial reference, precision and the 3D flag are all export-specific, so your own values will differ. What matters is getting exactly one row back with the name you expect. No row means you probably connected to the wrong database; check the psql output before trying the import again.

If layers were included, check the layer catalogue and the destination tables too:

$ psql -d staging_db -c "SELECT * FROM topology.layer WHERE topology_id = (SELECT topology_id FROM topology.topology WHERE name = 'city_data');"
$ psql -d staging_db -c '\dt city_data.*'

The layer rows and table list should match the subset you deliberately chose to restore. A topology row on its own does not prove every layer table or TopoGeometry reference survived the trip.

7. Handle common failures without guessing

An archive error such as an unexpected end of input points at the export file, not at PostgreSQL. Check its size, permissions and where it actually came from, then get a fresh export. A missing-file error from -f means the importer never had anything to read; fix the path, do not reach for sudo instead.

A SQL error about an existing schema or table usually means the destination already has part of the topology or a layer with that name. Stop and compare the generated SQL against the target catalogue rather than papering over a naming collision with unverified options. The PostGIS manual for newer development versions documents a --drop-topology option added since 3.6.0, but it is not in this installed 3.4.2 build and it is not safe to copy into this workflow just because it sounds convenient.

Need to retry? Use a clean test database, or restore the target through its own backup procedure, generate a fresh reviewed SQL file, and run the verification query again. Retrying the same file against a partially changed database usually just produces a more confusing failure.