CODEWITHSUNDEEP
postgresqlpostgis
Green Root
Green Root

Reputation: 694

pg_dump of PostGIS: schema is empty

I need to move PostGIS installation from database A into the new database B. I have PostGIS 2.1.7 on the A and 2.1.8 on the B. Both databases are the same - version of PostgreSQL is 9.4.4.

Configuration of PostGIS extension on A is following:

                                      List of installed extensions
Name    | Version |   Schema   |                             Description
------------+---------+------------+---------------------------------------------------------------------
 btree_gist | 1.0     | edrive     | support for indexing common datatypes in GiST
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis    | 2.1.7   | postgis    | PostGIS geometry, geography, and raster spatial types and functions
(3 rows)

The same is on B (except of postgis 2.1.8).

Command I use for export:

pg_dump -Fc -b A -p 5433 -U postgres > A.dmp

for restore:

pg_restore -Fc -d B -p 5432 A.dmp

Here is a part of log:

pg_restore: [archiver (db)] could not execute query: ERROR:  type "postgis.geometry" does not exist
LINE 9:     location postgis.geometry(Point,4326),
                     ^

Now lets have a look inside of the A.dmp - spatial_ref_sys table is empty, just a COPY command, no data:

COPY spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) FROM stdin;
.....postgis..........postgres.....false.....347.............................0.....17220.....roles.
...TABLE DATA................N...COPY roles (id, name, createdate, updatedate, description, value) FROM stdin;
.....security..........services...

The question is - why pg_dump is not dumping content of PostGIS schema?

UPD1:

content of general text-format dump: data for spatial_ref_sys:

COPY spatial_ref_sys (srid, auth_name, auth_srid, srtext, proj4text) FROM stdin;
\.

But we can see 3911 records there:

edrivep1=# select count(*) from postgis.spatial_ref_sys;
 count
-------
  3911
(1 row)

and

-bash-4.1$ pg_restore -l edrivep1.dmp | grep spatial
4932; 0 79804 TABLE DATA postgis spatial_ref_sys postgres

Upvotes: 4

Views: 1317

Answers (1)

Green Root
Green Root

Reputation: 694

The problem is resolved. There was a wrong search_path for postgis schema on the second database B - when I altered postgis' schema to public and search_path set to 'edrive,public,postgis' on the BOTH databases then everything went fine.

Upvotes: 1

Related Questions