CODEWITHSUNDEEP
postgresqlpostgispg-dumppg-restorepostgresql-9.6
Daniel Tan
Daniel Tan

Reputation: 143

Is there a way for pg_dump not to use search_path for schema

Question: Is there a way for pg_dump commands not to use search_path to set a schema, and instead define the schema with the table name

My general context is that I'm trying to dump and restore a postgis database which contains many schemas (this is to migrate my database into an Amazon RDS). In each schema I have tables that have a geography column. The generated SQL from the dump is something like:

CREATE SCHEMA aaaa; 
SET search_path = aaaa, pg_catalog;

SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 346 (class 1259 OID 1135099)
-- Name: main; Type: TABLE; Schema: aaaa; Owner: -
--
CREATE TABLE main (
    pk bigint NOT NULL,
    geometry public.geography(Geometry,4283),
    batch bigint NOT NULL,
    json jsonb NOT NULL,
    cache jsonb NOT NULL,
    active bigint DEFAULT date_part('epoch'::text, now()) NOT NULL,
    inactive bigint DEFAULT '8640000000000000'::bigint NOT NULL );

However, when restoring, I get the following error:

pg_restore: [archiver (db)] Error from TOC entry 346; 1259 1135099 TABLE main mapworks pg_restore: [archiver (db)] could not execute query: ERROR: relation "spatial_ref_sys" does not exist LINE 3:
geometry public.geography(Geometry,4283), ^ QUERY: SELECT proj4text FROM spatial_ref_sys WHERE srid = 4283 LIMIT 1 Command was: CREATE TABLE main ( pk bigint NOT NULL, geometry public.geography(Geometry,4283), batch bigint NOT NULL, jso...

Then things just go pear shaped from there.

What I've deduced so far is that because the 'public' schema is not included in the search path, the operation cant find the 'spatial_ref_sys' table. Oddly enough, this works fine if the EPSG code is 4326.

So ... what can I do about this? Is there a way to tell pg_dump not to use search_path?

The server I'm dumping from:

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

POSTGIS="2.3.1 r15264" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="0.11.99" (core procs from "2.3.0 r15146" need upgrade) TOPOLOGY (topology procs from "2.3.0 r15146" need upgrade) RASTER (raster procs from "2.3.0 r15146" need upgrade)

The server I'm restoring to:

PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit

POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.1.1, released 2016/07/07" LIBXML="2.9.1" LIBJSON="0.12" TOPOLOGY RASTER

Upvotes: 5

Views: 2638

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247320

This problem has been fixed in recent PostgreSQL versions. pg_dump now sets the search_path empty and includes the schema name with all objects.

Upvotes: 0

Related Questions