Re: "interesting" issue with restore from a pg_dump with adatabase-wide search_path
От | Larry Rosenman |
---|---|
Тема | Re: "interesting" issue with restore from a pg_dump with adatabase-wide search_path |
Дата | |
Msg-id | 20180706183747.nmneynmwnpocjami@ler-imac.local обсуждение исходный текст |
Ответ на | Re: "interesting" issue with restore from a pg_dump with adatabase-wide search_path ("Joshua D. Drake" <jd@commandprompt.com>) |
Ответы |
Re: "interesting" issue with restore from a pg_dump with a database-wide search_path
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
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@lerctr.org US Mail: 5708 Sabbia Drive, Round Rock, TX 78665-2106
Вложения
В списке pgsql-hackers по дате отправления: