Export a PostGIS topology safely with pgtopo_export

You will finish with a compressed pgtopo_export archive containing a named PostGIS topology, with a deliberate choice about related layers. The examples use the PostGIS 3.4.2 package installed here as postgis 3.4.2+dfsg-1ubuntu3.

Allow about fifteen minutes, plus the time needed to authenticate to PostgreSQL and read the topology. You need the pgtopo_export, psql and, when exporting layers, pg_dump commands, a database containing the topology, and permission to read its topology and layer tables. This is an export operation: it does not alter the database, but it can overwrite an existing output file.

1. Check the installed command

Start with a read-only help check. It does not need sudo or a database connection:

$ 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. The -f option selects an output file. Without it, the archive is written to standard output, which is useful for redirection but not for an interactive terminal.

2. Confirm the database and topology names

Replace the placeholders in the next command with values from your own PostgreSQL setup. This query is only a name check; it does not change data:

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

Use the exact value returned for TOPOLOGY_NAME. Do not confuse the database name with the topology name. A topology is a named object inside the database, commonly exposed through the topology.topology table.

pgtopo_export passes the selected name into the SQL it runs. Keep the name under your control and do not build it by concatenating untrusted request data. If you need a topology name supplied by another system, validate it against the list returned by the query before calling the exporter.

3. Export the topology and its layers

The normal export includes the topology primitives and associated layers. Choose a new 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...

The progress messages are written to the diagnostic stream. A successful return to the prompt means the archive command completed. If layers exist, an additional diagnostic line reports their count; that count depends on the database. Check the result before treating it as a backup:

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

The file is a custom pgtopo_export archive, not a plain SQL script. Keep it with the database name, topology name and PostGIS version recorded in your change or backup notes.

4. Export only topology primitives when appropriate

Use --skip-layers when you need the topology primitives but do not want the related feature tables included in the export:

$ 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 option changes the scope of the export. It is not a compression setting and it does not remove layers from the database. If a later restore needs the feature tables, do not use this reduced export as the only artefact. Check that the chosen file is non-empty in the same way as the full export.

5. Use standard output without accidentally printing binary data

When standard output is redirected, the default output mode lets you choose the destination in the shell:

$ 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

Do not run the command without -f or redirection at an interactive prompt. The installed command refuses to export binary output to a terminal. Its progress messages go to standard error, so they do 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 in place and inspect the error. Remove the .new file only after checking that it is an incomplete artefact you no longer need. If the replacement is wrong, restore the preserved copy with mv city_data.pgtopo_export.previous city_data.pgtopo_export after moving the bad file out of the way.

7. Diagnose a failed export

A connection or permission error usually comes from PostgreSQL rather than the archive step. First verify the same database connection independently:

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

Then check that the topology name is present and that the account can read it. A misspelled topology can cause the exporter to reach a later query before failing, so do not infer success from the first progress line. A non-zero exit status means the resulting file is not a verified export.

Run the exporter as the PostgreSQL account or as root only when your normal database and filesystem permissions require it. Elevated privileges do not repair a missing topology, and using sudo can change which PostgreSQL credentials and home-directory settings are used. Prefer the ordinary account that already works with psql.