CODEWITHSUNDEEP
postgresqlpostgispg-dump
spops
spops

Reputation: 676

PostGIS type "geometry" doesn't exist when pg_dump copying database to server

Similar question to many that have already been asked, but I have yet to find a solution.

I'm copying a Postgres db from my local machine to a server using:

pg_dump -C -h localhost -U localuser dbname | psql -h remotehost -U remoteuser dbname

However, whenever I try to execute this, any tables with PostGIS geography columns are skipped and not copied over, and I'm left with the following error:

ERROR:  type "<mydb>.geography" does not exist

Attempted solutions:

On this server, I have successfully installed PostGIS and created the relevant extensions:

mydb=# select postgis_version();
            postgis_version            
---------------------------------------
 2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Additionally, geometry is a recognized data type:

mydb=# \dT *.geometry
                     List of data types
 Schema |   Name   |               Description               
--------+----------+-----------------------------------------
 public | geometry | postgis type: Planar spatial data type.
(1 row)

My search_path includes everything relevant:

mydb=# show search_path;
      search_path       
------------------------
 mydb, public, postgis

and

mydb=# SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'mydb' OR d.datname = 'mydb';

 rolname | datname |      setconfig       
---------+---------+----------------------
         | mydb    | {search_path=public}
(1 row)

My only guess as to what might be causing this headache is that the superuser on my local machine isn't the same name as the remote superuser. I.e., localuser and remoteuser don't match.

Upvotes: 3

Views: 1322

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247280

The rest of the answer assumes that the error you quote is the first error while restoring the dump. If not, it might be the consequence of an earlier error (e.g., failure to CREATE EXTENSION postgis).

If PostGIS is installed on both servers, the message suggests that it is installed in different schemas on both databases.

Examine the result of

\dx postgis

on both databases to check.

Since PostGIS is not a relocatable extension, you have to drop and re-create it to move it to a different schema.

Upvotes: 5

Related Questions