Hi, The old database and the dumpfile is from external. No clue how this had been done.
We tried to pg_restore this dump into a database with and without pre-created postgis extension. We got postgis errors in any case. Version mismatch? Markus -----Ursprüngliche Nachricht----- Von: Zwettler Markus (OIZ) Gesendet: Freitag, 17. Mai 2019 09:40 An: [email protected] Betreff: Re: [postgis-users] PostGIS + pgdump 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 <[email protected]> Im Auftrag von [email protected] Gesendet: Donnerstag, 16. Mai 2019 21:00 An: [email protected] Betreff: postgis-users Digest, Vol 207, Issue 8 Send postgis-users mailing list submissions to [email protected] 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 [email protected] You can reach the person managing the list at [email protected] 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)" <[email protected]> To: "[email protected]" <[email protected]> Subject: [postgis-users] PostGIS + pgdump Message-ID: <[email protected]> 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 <[email protected]> To: PostGIS Users Discussion <[email protected]> Subject: Re: [postgis-users] PostGIS + pgdump Message-ID: <[email protected]> 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) > <[email protected]> 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 > [email protected] <mailto:[email protected]> > 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 <[email protected]> To: PostGIS Users Discussion <[email protected]> Subject: Re: [postgis-users] PostGIS + pgdump Message-ID: <CAG8g3twa90uc=0w+PJpvP23Vg-6zDG=6JCnD18o25fxE=8o...@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) < [email protected]> 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 > [email protected] > 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 <[email protected]> To: PostGIS Users Discussion <[email protected]> Subject: Re: [postgis-users] PostGIS + pgdump Message-ID: <20190516133856.GE6755@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 [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users ------------------------------ End of postgis-users Digest, Vol 207, Issue 8 ********************************************* _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
