Export a PostGIS Topology Safely with pgtopo_export

pgtopo_export packs a named PostGIS topology, and usually its related layers, into one compressed archive you can move somewhere safer. Fifteen minutes gets you a clean backup, once you have decided whether layers belong in it.

The examples use the PostGIS 3.4.2 package installed here as postgis 3.4.2+dfsg-1ubuntu3. You need pgtopo_export, psql and, when exporting layers, pg_dump, a database that holds the topology, and permission to read its topology and layer tables. This is an export: it never touches the database, though it can happily overwrite an existing output file if you let it.

1. Check the installed command

Start with a read-only help check, no sudo and no database connection needed:

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

The two positional arguments are the database name followed by the topology name. -f picks an output file; skip it and the archive goes to standard output, handy for redirecting, useless if you run it at an interactive prompt.

2. Confirm the database and topology names

Check the name before you build a command around it. This is a read-only lookup:

$ psql --dbname='GIS_DATABASE' --command="SELECT name FROM topology.topology ORDER BY name;"
    name
------------
+ city_data
(1 row)

Use exactly what the query returns as your TOPOLOGY_NAME. A topology is a named object inside the database, usually exposed through topology.topology, and it is easy to confuse with the database name itself if you are moving fast.

pgtopo_export feeds that name straight into the SQL it runs. Keep it under your control, never build it by concatenating request data from somewhere untrusted. If a topology name arrives from another system, validate it against the query result above before you let the exporter near it.

3. Export the topology and its layers

A normal export includes both the topology primitives and their associated layers. Pick a fresh destination rather than an existing backup file:

$ pgtopo_export -f 'city_data.pgtopo_export' 'GIS_DATABASE' 'city_data'
Exporting topology config...
Exporting layers info...
Exporting node...
Exporting edge...
Exporting face...
Exporting relation...
Archiving...

Those progress lines go to standard error. Getting back to the prompt cleanly means the archive step finished; if layers exist, you get an extra diagnostic line with their count, which will vary by database. Check the result rather than take the progress messages' word for it:

$ test -s city_data.pgtopo_export && echo 'export exists and is non-empty'
export exists and is non-empty
$ file city_data.pgtopo_export
city_data.pgtopo_export: gzip compressed data, from Unix, original size modulo 2^32 ...

This is a custom pgtopo_export archive, not plain SQL. Keep it filed alongside the database name, topology name and PostGIS version in your backup notes.

4. Export only topology primitives when appropriate

Add --skip-layers when you want the topology primitives without the related feature tables:

$ pgtopo_export --skip-layers -f 'city_data-primitives.pgtopo_export' 'GIS_DATABASE' 'city_data'
Exporting topology config...
Exporting layers info...
Exporting node...
Exporting edge...
Exporting face...
Exporting relation...
Archiving...

This narrows what gets exported. It does not compress harder and it does not remove anything from the database. If a later restore will need the feature tables, do not treat this reduced export as the only copy you keep. Check it is non-empty the same way as a full export.

5. Use standard output without accidentally printing binary data

Redirect standard output and you choose the destination yourself:

$ pgtopo_export 'GIS_DATABASE' 'city_data' > 'city_data.pgtopo_export'
$ test -s city_data.pgtopo_export && echo 'redirected export exists and is non-empty'
redirected export exists and is non-empty

Never run this without -f or a redirect at an interactive prompt; the command refuses to spray binary output onto a terminal. Progress still goes to standard error, so it will not corrupt a redirected archive.

6. Protect an existing export

$ cp --preserve=all city_data.pgtopo_export city_data.pgtopo_export.previous
$ pgtopo_export -f city_data.pgtopo_export.new 'GIS_DATABASE' 'city_data'
$ test -s city_data.pgtopo_export.new
$ mv city_data.pgtopo_export.new city_data.pgtopo_export

If the export fails, leave the original alone and go read the error. Only delete the .new file once you have confirmed it is an incomplete artefact you no longer need. Went wrong anyway? Move the bad file aside and restore the preserved copy with mv city_data.pgtopo_export.previous city_data.pgtopo_export.

7. Diagnose a failed export

Connection and permission errors almost always come from PostgreSQL, not the archive step. Verify the same connection on its own:

$ psql --dbname='GIS_DATABASE' --command='SELECT current_database();'
 current_database
------------------
 GIS_DATABASE
(1 row)

Then confirm the topology name exists and your account can read it. A misspelled topology can let the exporter get surprisingly far before it actually fails, so do not read success into the first progress line. A non-zero exit means the file is not a verified export, whatever it looks like.

Only run this as the PostgreSQL account, or as root, when your normal database and filesystem permissions genuinely require it. Elevated privileges will not fix a missing topology, and sudo can quietly change which credentials and home-directory settings get used. Stick with the ordinary account that already works fine with psql.