pg_dump does not write "CREATE EXTENSION postgis;"

11
votes

We have dumped a database that uses the postgis extension using pg_dump. After manually creating the database on a different machine an attempt to load the dump into it using psql failed. It turned out the dump did not contain the necessary statement

CREATE EXTENSION postgis;

The postgis extension was installed in the target instance of Postgres but only after manual execution of the CREATE EXTENSION loading of the dump succeeded. This doesn't seem like a big deal, but the question is: is it possible to force pg_dump to write all required CREATE EXTENSION foo; statements into the dump so I don't have to do it manually? I don't see any option for that in pg_dump online documentation.

2
postgresqlpostgis

2 Answers

14
votes

Several possibilities:

  1. This database has been upgraded from an old version where CREATE EXTENSION did not yet exist.

  2. Somebody mistakenly installed PostGIS by executing the SQL script rather than running CREATE EXTENSION.

  3. You don't dump the whole database, but restrict the dump to a schema using the -n option.

1
votes

You can use this to dump schema and extensions (tested on 11.2):

pg_dump -U postgres -d mydb -s > dump.sql