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
