0

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;
1
  • What does v. 12+ mean? What specific version did you try, and how did you install it?
    – jjanes
    Jul 4 at 15:54
1

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.

1
  • Thank you, I begin to understand the underlying behavior, but now that I manually created the postgis extension, I'm facing another issue when restoring a dump file that has been done on the public schema only.
    – s.k
    Jul 4 at 18:55

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.