15

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.

piokuc
  • 25,594
  • 11
  • 72
  • 102

2 Answers2

19

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 3
    My question is: can I get pg_dump to write `CREATE EXTENSION postgis;" to the dump? Perhaps the database that I'm dumping to backup has more extensions. I'd like the dump to have all the extensions in it. – piokuc Jun 09 '17 at 09:18
  • OK, I see what what you mean. I'll check that, it was actually somebody else who created the original database, so I don't know know how postgis was installed there. Thanks! – piokuc Jun 09 '17 at 09:20
  • Apparently the original db was created with a script that has a line: `create extension if not exists postgis;` – piokuc Jun 09 '17 at 09:26
  • Then it should be there. Do you see PostGIS with `\dx` in `psql`? If yes, it will be in the dump. You did the dump as superuser, didn't you? – Laurenz Albe Jun 09 '17 at 11:37
  • `\dx` shows me postgis, among few other things. Is it possible that `CREATE EXTENSION postgis;` wasn't dumped because I limited the dump to only couple of schemas from the database, using the option `-n` of pg_dump? The dump was performed by the admin, indeed, after I found I could not do it myself. – piokuc Jun 09 '17 at 13:28
  • 16
    Yes, if you limit the dump to a schema, it will not dump extensions even if the extension schema is among the dumped schemas. I have extended the answer to cover that possibility. – Laurenz Albe Jun 09 '17 at 14:36
  • @LaurenzAlbe Is there a way to limit the dump to a set of schemas *and* include the extensions? – davemyron Aug 11 '17 at 21:53
  • 2
    No, there is no way to do that. You could try to *exclude* all schemas that you do *not* want. – Laurenz Albe Aug 12 '17 at 13:27
  • Late to the party, but this also applies to `pg_restore` using the `-n` flag. – akahunahi Nov 05 '18 at 17:06
  • 1
    Just did a full dump of all tables and data using Postgresql 11.2, and it still doesn't output "CREATE EXTENSION postgis;" – cs94njw Mar 18 '19 at 11:11
  • @cs94njw you can use `pg_dump -U postgres -d mydb -s > dump.sql` tested on 11.2 – Mihai Tomescu Apr 07 '20 at 12:41
3

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

pg_dump -U postgres -d mydb -s > dump.sql
Mihai Tomescu
  • 1,525
  • 16
  • 21
  • The extensions are dumped this way, albeit not in the proper order. A line of SQL tries to create an index before the extensions are created: `USING gin (address_line_1 public.gin_trgm_ops);`. Fails with: ` ERROR: operator class "public.gin_trgm_ops" does not exist for access method "gin"` in my case. Also PG 11.17. – Rafs Aug 17 '22 at 09:30