"interesting" issue with restore from a pg_dump with a database-wide search_path

From: Larry Rosenman <ler(at)lerctr(dot)org>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: "interesting" issue with restore from a pg_dump with a database-wide search_path
Date: 2018-07-06 18:27:33
Message-ID: 20180706182733.kg5ogzycgotrv3fo@ler-imac.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have the following:
----
\set DB `echo $DB`
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS topology;
CREATE SCHEMA IF NOT EXISTS tiger;
SET search_path=public,postgis,tiger,topology;
ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
\c wm_test
CREATE EXTENSION fuzzystrmatch schema postgis;
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis schema postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology schema topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
CREATE EXTENSION postgis_sfcgal schema postgis;
-- rule based standardizer
CREATE EXTENSION address_standardizer schema postgis;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us schema postgis;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder schema tiger;
-- routing functionality
CREATE EXTENSION pgrouting schema postgis;
-- spatial foreign data wrappers
CREATE EXTENSION ogr_fdw schema postgis;
-- LIDAR support
CREATE EXTENSION pointcloud schema postgis;
-- LIDAR Point cloud patches to geometry type cases
CREATE EXTENSION pointcloud_postgis schema postgis;
-----

when I pg_dump -Fc the database and then try to restore it after a
create database, I get errors. To get a clean restare I need to do:

---
\set DB `echo ${DB}`
CREATE SCHEMA IF NOT EXISTS postgis;
CREATE SCHEMA IF NOT EXISTS topology;
CREATE SCHEMA IF NOT EXISTS tiger;
SET search_path=public,postgis,tiger,topology;
ALTER DATABASE :DB SET search_path=public,postgis,tiger,topology;
\c
CREATE EXTENSION fuzzystrmatch schema postgis;
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis schema postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology schema topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
CREATE EXTENSION postgis_sfcgal schema postgis;
-- rule based standardizer
CREATE EXTENSION address_standardizer schema postgis;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us schema postgis;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder schema tiger;
-- routing functionality
CREATE EXTENSION pgrouting schema postgis;
-- spatial foreign data wrappers
CREATE EXTENSION ogr_fdw schema postgis;
-- LIDAR support
CREATE EXTENSION pointcloud schema postgis;
-- LIDAR Point cloud patches to geometry type cases
CREATE EXTENSION pointcloud_postgis schema postgis;
----
Is the need to do this expected?

This is 10.4 on FreeBSD.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 E-Mail: ler(at)lerctr(dot)org
US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2018-07-06 18:35:41 Re: "interesting" issue with restore from a pg_dump with a database-wide search_path
Previous Message Andres Freund 2018-07-06 18:11:48 Re: pg_recvlogical use of SIGHUP