[postgis-users] PostGIS + pgdump
Zwettler Markus (OIZ)
Markus.Zwettler at zuerich.ch
Mon May 20 00:16:15 PDT 2019
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: postgis-users at lists.osgeo.org
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 <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
*********************************************
More information about the postgis-users
mailing list