[postgis-users] PostGIS + pgdump

Paul Ramsey pramsey at cleverelephant.ca
Fri May 17 06:11:30 PDT 2019 

Is it possible your old database had PostGIS enabled by using the sql file instead of ‘create extension’? 

select * from pg_extension ;

The errors look like collisions between objects defined in your old database and ones already there in the new. If the old database was postgis-enabled using ‘create extension’ you don’t need to do that in the new one before loading the dump fie: the dump file will itself already contain a ‘create extension’ command. If the old database was postgis-enabled using the postgis.sql file, the dump file will contain all the object and function defintions, and they’ll all point to the old postgis.so library, which is not great, but: you can usually load one of those files directly onto a database that has been postgis-enabled with ‘create extension’ and just ignore the errors, and all the data will load. 

P

> On May 17, 2019, at 2:40 AM, Zwettler Markus (OIZ) <Markus.Zwettler at zuerich.ch> wrote:
> 
> Hi,
> 
> Please find below the information requested.
> 
> Seems the errors are due to PostGIS Version mismatch. Dumpfile seems to use version 2.2. We are using version 2.4. Agreed?
> 
> Markus
> 
> 
> 
> 
> VERSIONS:
> =========
> # SELECT version();
>                                                 version
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
> (1 row)
> 
> Time: 1.324 ms
> # SELECT postgis_full_version();
>                                                                                    postgis_full_version
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> POSTGIS="2.4.4 r16526" PGSQL="96" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER
> (1 row)
> 
> Time: 204.072 ms
> 
> 
> 
> 
> DUMP (custom format):
> =====================
> ...
>    LANGUAGE c IMMUTABLE STRICT
>    AS '$libdir/postgis-2.2', 'box2df_in';
> ...
> 
> 
> 
> 
> ERRORS (snipped):
> =================
> pg_restore -d mydb -U mysuperuser dumpfile.dmp
> ...
> ...
> pg_restore: [archiver (db)] Error from TOC entry 186; 1259 17009 VIEW geography_columns postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "geography_columns" already exists
>    Command was: CREATE VIEW geography_columns AS
> SELECT current_database() AS f_table_catalog,
>    n.nspname AS f_table_schema,
>    c.relna...
> pg_restore: [archiver (db)] Error from TOC entry 187; 1259 17128 VIEW geometry_columns postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "geometry_columns" already exists
>    Command was: CREATE VIEW geometry_columns AS
> SELECT (current_database())::character varying(256) AS f_table_catalog,
>    n.nspname AS f_...
> pg_restore: [archiver (db)] Error from TOC entry 184; 1259 16675 TABLE spatial_ref_sys postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation "spatial_ref_sys" already exists
>    Command was: CREATE TABLE spatial_ref_sys (
>    srid integer NOT NULL,
>    auth_name character varying(256),
>    auth_srid integer,
>    s...
> pg_restore: [archiver (db)] Error from TOC entry 3462; 0 16675 TABLE DATA spatial_ref_sys postgres
> pg_restore: [archiver (db)] COPY failed for table "spatial_ref_sys": ERROR:  duplicate key value violates unique constraint "spatial_ref_sys_pkey"
> DETAIL:  Key (srid)=(3819) already exists.
> CONTEXT:  COPY spatial_ref_sys, line 1
> pg_restore: [archiver (db)] Error from TOC entry 3085; 2606 16683 CONSTRAINT spatial_ref_sys_pkey postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  multiple primary keys for table "spatial_ref_sys" are not allowed
>    Command was: ALTER TABLE ONLY spatial_ref_sys
>    ADD CONSTRAINT spatial_ref_sys_pkey PRIMARY KEY (srid);
> ...
> ...
> 
> 
> 
> 
> -----Ursprüngliche Nachricht-----
> Von: postgis-users <postgis-users-bounces at lists.osgeo.org> Im Auftrag von postgis-users-request at lists.osgeo.org
> Gesendet: Donnerstag, 16. Mai 2019 21:00
> An: postgis-users at lists.osgeo.org
> Betreff: postgis-users Digest, Vol 207, Issue 8
> 
> Send postgis-users mailing list submissions to
> 	postgis-users at lists.osgeo.org
> 
> To subscribe or unsubscribe via the World Wide Web, visit
> 	https://lists.osgeo.org/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> 	postgis-users-request at lists.osgeo.org
> 
> You can reach the person managing the list at
> 	postgis-users-owner at lists.osgeo.org
> 
> When replying, please edit your Subject line so it is more specific than "Re: Contents of postgis-users digest..."
> 
> 
> Today's Topics:
> 
>   1. PostGIS + pgdump (Zwettler Markus (OIZ))
>   2. Re: PostGIS + pgdump (Paul Ramsey)
>   3. Re: PostGIS + pgdump (James Keener)
>   4. Re: PostGIS + pgdump (Sandro Santilli)
> 
> 
> ----------------------------------------------------------------------
> 
> Message: 1
> Date: Thu, 16 May 2019 13:09:54 +0000
> From: "Zwettler Markus (OIZ)" <Markus.Zwettler at zuerich.ch>
> To: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] PostGIS + pgdump
> Message-ID: <4a928fcb137b4be3a60ec896b552f26d at zuerich.ch>
> Content-Type: text/plain; charset="utf-8"
> 
> Hi,
> 
> 
> We did a default PostGIS installation within a PostgreSQL 9.6 database:
> 
> ===
> create extension if not exists postgis;
> create extension if not exists postgis_topology; create extension if not exists ogr_fdw; create extension if not exists pgrouting; ===
> 
> This installed PostGIS within the public schema of the database.
> (SET SCHEMA is not supported since V2.3 anymore)
> 
> Our customer application was also installed within the public schema.
> 
> When we pg_dump + pg_restore the database we got a lot of errors.
> 
> We tried to pg_restore the dump into a database without postgis extension => postgis errors We tried to pg_restore the dump into a database with postgis extension => postgis errors We tried to pg_restore only the public schema into a database with postgis extension => postgis errors
> 
> QUESTION:
> How does the PostGIS extension have to be installed so that pg_dump + pg_restore DO NOT error out?
> 
> 
> Thanks,
> Markus
> 
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190516/e49ecaff/attachment-0001.html>
> 
> ------------------------------
> 
> Message: 2
> Date: Thu, 16 May 2019 08:12:46 -0500
> From: Paul Ramsey <pramsey at cleverelephant.ca>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] PostGIS + pgdump
> Message-ID: <94E1E72B-94BB-46CB-9BAA-EBE278C41253 at cleverelephant.ca>
> Content-Type: text/plain; charset="utf-8"
> 
> You haven’t really provided any clues to your underlying problem. What platform you’re on, how your postgis is installed (packages? source?), what the errors you see actually ARE. We don’t publish broken software and then hide the top secret directions for correct use. So when you see things broken, consider it an exceptional condition and gather all the clues you can to share and aid in the process of getting help, otherwise… you won’t get help.
> 
> P
> 
>> On May 16, 2019, at 8:09 AM, Zwettler Markus (OIZ) <Markus.Zwettler at zuerich.ch> wrote:
>> 
>> Hi,
>> 
>> 
>> We did a default PostGIS installation within a PostgreSQL 9.6 database:
>> 
>> ===
>> create extension if not exists postgis; create extension if not exists 
>> postgis_topology; create extension if not exists ogr_fdw; create 
>> extension if not exists pgrouting; ===
>> 
>> This installed PostGIS within the public schema of the database.
>> (SET SCHEMA is not supported since V2.3 anymore)
>> 
>> Our customer application was also installed within the public schema.
>> 
>> When we pg_dump + pg_restore the database we got a lot of errors.
>> 
>> We tried to pg_restore the dump into a database without postgis 
>> extension => postgis errors We tried to pg_restore the dump into a 
>> database with postgis extension => postgis errors We tried to 
>> pg_restore only the public schema into a database with postgis 
>> extension => postgis errors
>> 
>> QUESTION:
>> How does the PostGIS extension have to be installed so that pg_dump + pg_restore DO NOT error out?
>> 
>> 
>> Thanks,
>> Markus
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>> https://lists.osgeo.org/mailman/listinfo/postgis-users 
>> <https://lists.osgeo.org/mailman/listinfo/postgis-users>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190516/a9246eeb/attachment-0001.html>
> 
> ------------------------------
> 
> Message: 3
> Date: Thu, 16 May 2019 09:12:56 -0400
> From: James Keener <jim at jimkeener.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] PostGIS + pgdump
> Message-ID:
> 	<CAG8g3twa90uc=0w+PJpvP23Vg-6zDG=6JCnD18o25fxE=8OooA at mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
> 
> It'd help if you told us what errors you're getting.
> 
> Jim
> 
> On Thu, May 16, 2019 at 9:10 AM Zwettler Markus (OIZ) < Markus.Zwettler at zuerich.ch> wrote:
> 
>> Hi,
>> 
>> 
>> 
>> 
>> 
>> We did a default PostGIS installation within a PostgreSQL 9.6 database:
>> 
>> 
>> 
>> ===
>> 
>> create extension if not exists postgis;
>> 
>> create extension if not exists postgis_topology;
>> 
>> create extension if not exists ogr_fdw;
>> 
>> create extension if not exists pgrouting;
>> 
>> ===
>> 
>> 
>> 
>> This installed PostGIS within the public schema of the database.
>> 
>> (SET SCHEMA is not supported since V2.3 anymore)
>> 
>> 
>> 
>> Our customer application was also installed within the public schema.
>> 
>> 
>> 
>> When we pg_dump + pg_restore the database we got a lot of errors.
>> 
>> 
>> 
>> We tried to pg_restore the dump into a database without postgis 
>> extension => postgis errors
>> 
>> We tried to pg_restore the dump into a database with postgis extension 
>> => postgis errors
>> 
>> We tried to pg_restore only the public schema into a database with 
>> postgis extension => postgis errors
>> 
>> 
>> 
>> QUESTION:
>> 
>> How does the PostGIS extension have to be installed so that pg_dump + 
>> pg_restore DO NOT error out?
>> 
>> 
>> 
>> 
>> 
>> Thanks,
>> 
>> Markus
>> 
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190516/b95a191c/attachment-0001.html>
> 
> ------------------------------
> 
> Message: 4
> Date: Thu, 16 May 2019 15:38:56 +0200
> From: Sandro Santilli <strk at kbt.io>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] PostGIS + pgdump
> Message-ID: <20190516133856.GE6755 at liz>
> Content-Type: text/plain; charset=us-ascii
> 
> On Thu, May 16, 2019 at 01:09:54PM +0000, Zwettler Markus (OIZ) wrote:
> 
>> We did a default PostGIS installation within a PostgreSQL 9.6 database:
>> 
>> ===
>> create extension if not exists postgis; create extension if not exists 
>> postgis_topology; create extension if not exists ogr_fdw; create 
>> extension if not exists pgrouting; ===
>> 
>> This installed PostGIS within the public schema of the database.
>> (SET SCHEMA is not supported since V2.3 anymore)
>> 
>> Our customer application was also installed within the public schema.
>> 
>> When we pg_dump + pg_restore the database we got a lot of errors.
> 
> At pg_dump or pg_restore time ? What errors did you get ?
> Do you still have access to the working database ?
> If so, show us the output of:
> 
>  SELECT postgis_full_version();
> 
> --strk;
> 
> 
> ------------------------------
> 
> Subject: Digest Footer
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> 
> ------------------------------
> 
> End of postgis-users Digest, Vol 207, Issue 8
> *********************************************
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

More information about the postgis-users mailing list