Restore a Legacy PostGIS Dump with postgis_restore

postgis_restore rescues an old, pre-extension PostGIS dump that would otherwise clash with the objects already sitting in your new database. By the end, you will have converted a compatible custom-format PostgreSQL dump into SQL that can be loaded into a new, PostGIS-enabled database. This is a careful restore workflow, not a general replacement for pg_restore. Allow 10 to 30 minutes for the commands, plus the time needed to copy and check the database.

Before you start

This guide describes postgis_restore from the Ubuntu package postgis, version 3.4.2+dfsg-1ubuntu3. The installed program is /usr/bin/postgis_restore. It is a Perl script that calls pg_dump and pg_restore, so both PostgreSQL client programs must be on your PATH.

• A readable dump made with pg_dump -Fc, such as /srv/backups/olddb.dump.
• Permission to create the destination database, or an administrator who can do that.
• A destination database with the required PostGIS extension already installed.
• Enough free space for the destination database and a small manifest file beside the dump.

Decide whether this utility applies

The useful case is an older PostGIS database whose objects were installed by loading postgis.sql. The script filters known PostGIS objects from the dump while retaining your data and other database objects, so it avoids trying to recreate definitions already present in the new database.

If the source database used CREATE EXTENSION postgis, the manpage says its dump does not contain the PostGIS objects that need filtering. Restore that dump directly with pg_restore instead: mixing the two workflows can hide the real cause of a restore error.

PostGIS 3.4 renamed the older postgis_restore.pl utility to postgis_restore. Use the name installed on this system. The current command prints its accepted syntax with no arguments:

/usr/bin/postgis_restore

Expected output begins like this:

Usage: /usr/bin/postgis_restore [-v] [-L TOC] [-s schema] <dumpfile>
Restore a custom dump (pg_dump -Fc) of a PostGIS-enabled database.

1. Inspect the dump without changing the database

First verify the file is readable and that PostgreSQL can list its table of contents. This does not create a database or load SQL.

dump=/srv/backups/olddb.dump
test -r "$dump" && pg_restore --list "$dump" | sed -n '1,20p'

A successful check prints archive entries such as a format header and object lines. A message saying the input is not a valid archive usually means the file is not a custom-format dump, is incomplete, or is not the file you intended to use. Stop and obtain a valid backup rather than passing it to the restore pipeline.

2. Prepare a separate destination

Never use a production database as the first test destination. The following commands create a new database and enable PostGIS. They need database privileges, but not a shell root login. Replace the names and connection options with values for your environment.

createdb -h db.example.test -U dbadmin newdb
psql -h db.example.test -U dbadmin -d newdb -v ON_ERROR_STOP=1 \
  -c 'CREATE EXTENSION postgis;'

If the source used PostGIS topology or raster, install the corresponding extensions or SQL objects required by that source before loading its data. Do not assume that installing only the core extension recreates every optional component.

dropdb -h db.example.test -U dbadmin newdb

The drop is irreversible for that database. Keep the original dump untouched so you can repeat the test.

3. Run the filtered restore

Pass the dump to postgis_restore and send its SQL output to psql. Use pipefail so a failure in either command makes the shell command fail. ON_ERROR_STOP makes psql stop at the first SQL error instead of continuing through a damaged restore.

set -o pipefail
/usr/bin/postgis_restore "$dump" | \
  psql -h db.example.test -U dbadmin -d newdb -v ON_ERROR_STOP=1

The script emits SQL on standard output and progress only when requested on standard error. It also writes a manifest beside the dump, here /srv/backups/olddb.dump.lst. Keep that file while investigating a failed run. A later invocation normally recreates it; use a copied dump or a controlled backup directory if another process must not see that sidecar file.

For a detailed report, add -v to the first command:

/usr/bin/postgis_restore -v "$dump" | \
  psql -h db.example.test -U dbadmin -d newdb -v ON_ERROR_STOP=1

Do not redirect the script's standard output to a file and then treat that file as a complete, portable dump without checking it. The generated SQL contains restore-specific statements for spatial_ref_sys, and the script may disable topology triggers while loading data.

4. Check the result

Confirm PostGIS is available and inspect the restored schemas and row counts relevant to your application. These checks are read-only:

psql -h db.example.test -U dbadmin -d newdb -v ON_ERROR_STOP=1 <<'SQL'
SELECT PostGIS_Full_Version();
\dn
\dt *.*
SQL

Compare representative tables and application-level queries with the source database. A successful pipeline only proves the SQL was accepted; it does not prove that permissions, ownership, extensions, sequences, application settings, or all expected rows match.

Useful options and traps

• -s schema tells the script that PostGIS was installed in a non-default schema. Use the exact schema name, for example -s gis.
• -L TOC supplies an existing table-of-contents file instead of making one by running pg_restore -l. Create and review one with pg_restore --list "$dump" > /tmp/newdb.toc, then pass that path. The file must be readable by the invoking user.
• -v is verbose reporting to standard error. It does not mean PostgreSQL client verbose mode and does not make the restore safer.
• Wrong schema guess: the script may infer a PostGIS schema from the dump. If that guess is wrong or the destination uses a custom schema, set -s explicitly.

If the pipeline fails, leave the destination database available for inspection, read the first SQL error, and rerun with -v. To retry from a clean state, drop the test database and recreate it with the extension. Remove the generated .lst sidecar only after you no longer need it, then keep the source dump for another attempt.