Restore a PostGIS Topology Safely with pgtopo_import

You will turn a PostGIS topology export into an SQL file, inspect what will be restored, and apply it to a PostgreSQL database with a deliberate choice about layer tables. The examples use pgtopo_import from PostGIS package version 3.4.2+dfsg-1ubuntu3, installed here on Linux.

Allow about twenty minutes, plus the time needed to review the generated SQL. You need a readable export created by pgtopo_export, the PostGIS utilities package, a target PostgreSQL database, and credentials with enough permission to create the topology and any selected layers. The importer reads an archive and writes SQL. The later psql step changes database state, so do that only after checking the destination and keeping the export as a recovery source.

1. Check the installed contract

Confirm which executable is being used and record the package version. These are ordinary, read-only checks:

$ 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>

The final command exits non-zero because no topology name or usable export was supplied. Its useful result is the installed usage line. The local manual lists -h as a supported option, but this packaged build still attempted to read the empty input when tested with -h. Use the installed manpage and the usage line above as the contract for scripts on this host rather than assuming that a help option behaves like one from another release.

Checkpoint: the input is a pgtopo_export file, the topology name is a separate argument, and the importer writes SQL to standard output unless stdout is redirected.

2. Preserve the export before you import it

Keep the original export somewhere that your database recovery process includes. Do not edit it in place or delete it after a successful restore. If you received it under an unclear name, inspect it without applying anything:

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

The exact file description depends on the archive and installed tools. A valid export is produced by the matching PostGIS export utility. A random gzip or tar archive is not enough: the importer expects the PostGIS export format. If the file is truncated, stop here and obtain a complete export instead of trying to repair the archive manually.

Do not run the generated SQL against production as a first test. Choose a disposable database or a maintenance window, and confirm the target database name, host and user before the command that follows. PostgreSQL authentication and the permissions required by the SQL are outside pgtopo_import.

3. Generate SQL without applying it

Use -f when the export is a named file. Choose a new output file so an older reviewed script cannot be silently 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 is the name to give the recreated topology. It does not have to match the name used when the export was made. The command normally produces no progress message because the SQL is its standard output. A non-empty file is only a first check: it does not prove that the SQL will succeed in the destination.

The same operation can read from standard input. This is useful when the exporter and importer are run together:

$ pgtopo_export source_db city_data | pgtopo_import city_data > city_data.sql

That pipeline still only creates an SQL file. It does not connect to the destination database until you invoke psql. Keep the source database and destination database explicit in operational notes; confusing those two names is an easy way to restore to 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 present in the target database, so decide this before applying the file.

To generate only the topology schema, omit 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

To generate only the layer restoration and linking statements, omit the 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. Do not combine them, and do not assume that layers-only SQL can work before the named topology exists. If target tables already use the exported layer names, generate topology-only SQL first and resolve the table naming plan separately.

Checkpoint: open the generated file as text and review its first and last sections. Search for the topology name and any layer table names you expect. This is a review step, not a substitute for a database backup.

5. Apply the reviewed SQL

State-changing step: the next command can create schemas, metadata and layer tables in the target database. Confirm the database connection and take the backup required by your recovery plan before running it.

Apply the reviewed file with psql:

$ psql -d staging_db -f city_data.sql

Use your normal PostgreSQL connection options when the database is remote, such as -h, -p and -U. Do not put a password directly in a shell command or in a file that other users can read. If psql reports an error, preserve the message and stop before rerunning against another database. Review the failing statement and the target state first.

There is no general undo command supplied by pgtopo_import. Recovery means restoring the target database from its backup, or carefully removing the objects created by the reviewed SQL according to your database change procedure. Keep both the original export and the exact SQL file used so the operation can be audited or repeated.

6. Verify the recreated topology

Query the PostGIS topology catalogue in the target database. This read-only check asks whether the requested topology name is 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)

The identifier, spatial reference, precision and three-dimensional flag are export-specific, so your values will differ. The important result is one row with the expected name. If there is no row, verify that you connected to the database you intended and inspect the psql output before trying the import again.

When layers were included, also check the layer catalogue and the destination tables:

$ 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 restored. A topology row alone does not prove that every layer table or TopoGeometry reference was restored correctly.

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 provenance, then obtain a fresh export. A missing-file error from -f means the importer never had an input; fix the path rather than switching to sudo.

A SQL error about an existing schema or table usually means the destination already contains part of the topology or a layer name. Stop and compare the generated SQL with the target catalogue. Do not solve a naming collision by adding unverified options. The PostGIS manual for newer development versions documents a --drop-topology option introduced since 3.6.0, but it is not in this installed 3.4.2 command's interface and is not safe to copy into this workflow.

If you need to retry, use a clean test database or restore the target according to its backup procedure, generate a fresh reviewed SQL file, and repeat the verification query. Retrying the same file against a partially changed database can create a more confusing failure.