CODEWITHSUNDEEP
postgresqlpostgis
piokuc
piokuc

Reputation: 26204

pg_dump does not write "CREATE EXTENSION postgis;"

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.

Upvotes: 17

Views: 12332

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247950

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 (if you limit the dump to a schema, it will not dump extensions even if the extension schema is among the dumped schemas).

Upvotes: 21

Mihai Tomescu
Mihai Tomescu

Reputation: 1607

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

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

Upvotes: 4

Related Questions