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

On Fri, Jul 06, 2018 at 11:35:41AM -0700, Joshua D. Drake wrote:
> On 07/06/2018 11:27 AM, Larry Rosenman wrote:
> > 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:
>
> Knowing the errors would be helpful.
>
> jD
ler=# drop database wm_common;create database wm_common
DROP DATABASE
ler-# ;
CREATE DATABASE
ler=# \q
borg.lerctr.org /home/ler $ pg_restore -d wm_common wm_t
borg.lerctr.org /home/ler $ cd WM
borg.lerctr.org /home/ler/WM $ pg_restore -d wm_common wm_test.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 12; 3079 887963 EXTENSION postgis_tiger_geocoder
pg_restore: [archiver (db)] could not execute query: ERROR: function soundex(character varying) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Command was: CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder WITH SCHEMA tiger;

pg_restore: [archiver (db)] Error from TOC entry 5400; 0 0 COMMENT EXTENSION postgis_tiger_geocoder
pg_restore: [archiver (db)] could not execute query: ERROR: extension "postgis_tiger_geocoder" does not exist
Command was: COMMENT ON EXTENSION postgis_tiger_geocoder IS 'PostGIS tiger geocoder and reverse geocoder';

pg_restore: [archiver (db)] Error from TOC entry 11; 3079 887754 EXTENSION postgis_topology
pg_restore: [archiver (db)] could not execute query: ERROR: type "geometry" does not exist
Command was: CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;

pg_restore: [archiver (db)] Error from TOC entry 5401; 0 0 COMMENT EXTENSION postgis_topology
pg_restore: [archiver (db)] could not execute query: ERROR: extension "postgis_topology" does not exist
Command was: COMMENT ON EXTENSION postgis_topology IS 'PostGIS topology spatial types and functions';

>
>
> > ---
> > \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.
> >
> >
> >
>
> --
> Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
> *** A fault and talent of mine is to tell it exactly how it is. ***
> PostgreSQL centered full stack support, consulting and development.
> Advocate: @amplifypostgres || Learn: https://postgresconf.org
> ***** Unless otherwise stated, opinions are my own. *****
>

--
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