While trying to restore a PostgreSQL dump that has been made using the -n public option of pg_dump (see: Postgis extension doesn't seem to be taken into account when restoring a PostGIS database: ERROR: type "public.geometry" does not exist) I now begin to understand that it's mandatory for me to create the database and the necessary extension(s) manually before restoring it.

It works almost fine, because now the postgis extension is there (it's no more complaining about it) when this restore begins:

pg_restore \
  --format=c \
  --username=postgres \
  --dbname=mydatabase_dev \
  --role=postgres \
  --no-privileges \
  --no-owner \
  --verbose \

(One thing to note here, is that the database name inside the dump file is mydatabase_prod, so, I have to set up --dbname=mydatabase_dev to match the name I need for development purposes.)

But this also means I cannot use the --clean and --create options of pg_restore because they will obviously cancel what I manually prepared. It's not a big problem because I restore this dump from scratch inside a fresh postgis docker container. But...

But now, I am facing this issue:

db_1   | pg_restore: connecting to database for restore
db_1   | pg_restore: creating SCHEMA "public"
db_1   | 2021-07-04 17:14:28.123 UTC [185] ERROR:  schema "public" already exists
db_1   | 2021-07-04 17:14:28.123 UTC [185] STATEMENT:  CREATE SCHEMA public;    
db_1   | pg_restore: while PROCESSING TOC:
db_1   | pg_restore: from TOC entry 28; 2615 26623 SCHEMA public postgres
db_1   | pg_restore: error: could not execute query: ERROR:  schema "public" already exists
db_1   | Command was: CREATE SCHEMA public;

(...restoring all tables)

db_1   | pg_restore: warning: errors ignored on restore: 1
db_1   | 
db_1   | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1   | 
db_1   | 2021-07-04 17:18:25.043 UTC [1] LOG:  starting PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
db_1   | 2021-07-04 17:18:25.044 UTC [1] LOG:  listening on IPv4 address "", port 5432
db_1   | 2021-07-04 17:18:25.045 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1   | 2021-07-04 17:18:25.051 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1   | 2021-07-04 17:18:25.058 UTC [27] LOG:  database system was interrupted; last known up at 2021-07-04 17:17:10 UTC
db_1   | 2021-07-04 17:18:25.087 UTC [27] LOG:  database system was not properly shut down; automatic recovery in progress
db_1   | 2021-07-04 17:18:25.091 UTC [27] LOG:  redo starts at 0/F720C0B8
db_1   | 2021-07-04 17:18:25.117 UTC [28] LOG:  incomplete startup packet

This didn't seem to be an issue at first glance because plenty of lines descrbing the tables being actually restored are displayed on the console... Until I noticed that every commands following this pg_restore one in my bash script are simply and purely not executed. As if the database "bugged" and restarted at this precise point, prior to all other commands that follow pg_restore. Also, if I add the flag --exit-on-error to pg_restore it simply stops after the first error is met (i.e.: schema "public" already exists).

Therefore, I'm stuck. I have to do with that dump file made using the -n public option, so I have to create the database and install postgis prior to the restore in order to avoid the linked issue, and on the other hand, if I want the public schema not to be there in order to avoid the schema "public" already exists error, it seems I do need to start from a fresh database, either by using --clean --create options when using pg_restore, or by breaking the restore into two commands as explained here; https://dba.stackexchange.com/a/90319/196371. But both solutions would drop the postgis extension in return, which will make me go back to the first error...

I cannot see any issue to this snake-biting-its-tail situation...

One first option would be to tell pg_dump to also dump the extensions, which is currently not possible, or to 'ignore' the fact that a schema does already exist while restoring a dump that has been made with the -n flag on this precise schema (I didn't found such 'ignore schema' option in the doc but that would be great).

Any advice at this point? Did I miss something (I hope so)? Should I look for a full database dump (which I may not get)? ...

  • That should have worked. Are you sure you connected to the right database when checking for the data? Maybe Docker threw away your database and created a new one to replace it? That last bit of the log file doesn't seem right.
    – jjanes
    Jul 5 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.