Postgresql – Postgis extension doesn’t seem to be taken into account when restoring a PostGIS database: ERROR: type “public.geometry” does not exist

pg-dumppg-restorepostgispostgresql

I am struggling trying to restore a PostgreSQL dump that has been made using pg_dump (v. 12+) out of a production database having PostGIS installed, the very first issue is this one when restoring the dump:

db_1   | pg_restore: creating TABLE "public.global_position"
db_1   | [189] ERROR:  type "public.geometry" does not exist at character 94

to me, this roughly means that the postgis extension was not successfully restored.

But when I log on the original database in order to list the installed extensions, here's what I can read:

You are connected to database "productiondb" as user "postgres" on host "localhost" (address "127.0.0.1") at port "5432".
productiondb=# \dx
                                         List of installed extensions
       Name       | Version |   Schema   |                             Description                             
------------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch    | 1.1     | public     | determine similarities and distance between strings
 plpgsql          | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis          | 2.5.2   | public     | PostGIS geometry, geography, and raster spatial types and functions
 postgis_topology | 2.5.2   | topology   | PostGIS topology spatial types and functions
(4 rows)

so postgis is actually there, inside the public schema.

Therefore, why is the postgis extension not restored properly? Is there something special about it?

Here are some more details:

productiondb=# select version();
                                        version                                        
---------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.2.0) 8.2.0, 64-bit
(1 row)

productiondb=# select postgis_version();
            postgis_version            
---------------------------------------
 2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

And the dump was made using:

pg_dump -Fc -d "postgresql://postgres:****@localhost:5432/productiondb" \
  --disable-triggers \
  --role=postgres \
  --no-owner \
  --no-acl \
  --verbose \
  -n "public" \
  -f "file.dump"

And restored as follows (inside a local docker container):

pg_restore --format=c \
  -U postgres \
  -d postgres
  --verbose \
  --clean \
  --create \
  --no-privileges \
  --no-owner \
  file.dump;

Best Answer

While extensions can be associated with a schema, they are not actually in the schema. For example, you can't create the same extension associated with different schemas in the same database at the same time; which you could if they were actually in a schema.

So specifying -n with pg_dump means the extension create statements do not get dumped, so pg_restore doesn't replay the extension creation.

You could create the extension manually.