Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

classic Classic list List threaded Threaded
11 messages Options
ced
Reply | Threaded
Open this post in threaded view
|

Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

ced
Hi all,

I am upgrading PostgreSQL and PostGIS from 9.3/2.1 to 9.5/2.3.

I perform the backup on the old server (9.3/2.1) like this using pg_dump.

Then, on the new server (9.5/2.3), I restore the backup using pg_restore.

Everything is correctly restored, except just one raster table, a DEM, which produces the following error message (sorry, it is in french...):
 
COPY échoué pour la table « mnt » : ERREUR:  l'opérateur n'existe pas : public.geometry @ public.geometry
LIGNE 1 : SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)
                    ^
ASTUCE : Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
REQUÊTE : SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)
CONTEXTE : fonction SQL « st_coveredby » durant « inlining »
COPY mnt, ligne 1 : « 1 0100000100000000000000394000000000000039C000000000B76B29410000002035325941000000000000000000000000... »

The new table is finally empty.
The problem seems to come from a cast problem in _ST_CoveredBy function in PostGIS 2.3.
Has it changed between 2.1 and 2.3?

How can I restore data?

Thanks in advance for your help.

Regards,
Cedric
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

Giuseppe Broccolo
Hi Cedric,

2016-11-18 14:21 GMT+01:00 ced <[hidden email]>:
Hi all,

I am upgrading PostgreSQL and PostGIS from 9.3/2.1 to 9.5/2.3.

I perform the backup on the old server (9.3/2.1) like this using pg_dump.

Then, on the new server (9.5/2.3), I restore the backup using pg_restore.

Everything is correctly restored, except just one raster table, a DEM, which
produces the following error message (sorry, it is in french...):

COPY échoué pour la table « mnt » : ERREUR:  l'opérateur n'existe pas :
public.geometry @ public.geometry
LIGNE 1 : SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)

The problem could be reasonably due to the schema where the operator @ is defined:
could you attach here the output of the following psql meta-commands, once you are connected
to the 9.5 database (i.e. where the dump is restored)?

1) \do @

2) SHOW search_path;

PostGIS 2.3 now fully-qualify operators and functions with the schema where they are defined
(see for instance public._ST_CoveredBy). @ looks to be not fully-qualified. My first thought here
is that @ is not defined in any schema visible through the search_path (probably in the "postgis"
schema?).

Regards,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
[hidden email] | www.2ndQuadrant.it

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
ced
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

ced
Hi Giuseppe,

Thanks for your answer and your help.
Here is the output of \do @:

    Schéma   | Nom | Type de l'arg. gauche | Type de l'arg. droit | Type
du résultat |        Description
------------+-----+-----------------------+----------------------+------------------+----------------------------
  pg_catalog | @   | box                   | box                  |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | circle                | circle               |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | lseg                  | box                  |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | lseg                  | line                 |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | point                 | box                  |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | point                 | circle               |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | point                 | line                 |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | point                 | lseg                 |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | point                 | path                 |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | point                 | polygon              |
boolean          | deprecated, use <@ instead
  pg_catalog | @   | polygon               | polygon              |
boolean          | deprecated, use <@ instead
  pg_catalog | @   |                       | bigint               |
bigint           | absolute value
  pg_catalog | @   |                       | double precision     |
double precision | absolute value
  pg_catalog | @   |                       | integer              |
integer          | absolute value
  pg_catalog | @   |                       | numeric              |
numeric          | absolute value
  pg_catalog | @   |                       | real                 |
real             | absolute value
  pg_catalog | @   |                       | smallint             |
smallint         | absolute value
  public     | @   | geometry              | geometry             |
boolean          |
  public     | @   | geometry              | raster               |
boolean          |
  public     | @   | raster                | geometry             |
boolean          |
  public     | @   | raster                | raster               |
boolean          |

The @ operator for raster type is in the public schema.
Weird, isn't it?
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

Giuseppe Broccolo
Hi Cedric,

2016-11-21 9:26 GMT+01:00 Cedric Duprez <[hidden email]>:
Hi Giuseppe,

Thanks for your answer and your help.
Here is the output of \do @:

The operator signs related to PostGIS datatypes are the following ones:
 
    Schéma   | Nom | Type de l'arg. gauche | Type de l'arg. droit | Type
du résultat |        Description
------------+-----+-----------------------+----------------------+------------------+----------------------------
  public     | @   | geometry              | geometry             |
boolean          |
  public     | @   | geometry              | raster               |
boolean          |
  public     | @   | raster                | geometry             |
boolean          |
  public     | @   | raster                | raster               |
boolean          |

The @ operator for raster type is in the public schema.
Weird, isn't it?

This is due to how the dump has been restored in the target instance (PostgreSQL 9.5 + PostGIS 2.3).
Functions and operators have been imported in the public schema in the session opened by pg_restore.

PostGIS 2.1 does not fully-qualify functions and operators, so are not recreated in the original schemas
when they are restored from a logical backup.

Which user is used to connect to the database when pg_restore is executed?

Regards,
Giuseppe.

--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL & PostGIS Training, Services and Support
[hidden email] | www.2ndQuadrant.it

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
ced
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

ced
That's right... On the target instance, the user is not exactly the same
as on the original instance.
This is due to a global settings backup problem I am trying to solve.

I will try again with the same users and tell you if it works.

Thanks for your help.
Regards,

Cedric

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
ced
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

ced
Hi,

Still same issue with two servers having the same configuration.

Regards,

Cedric


Le 21/11/2016 à 11:16, Cedric Duprez a écrit :
That's right... On the target instance, the user is not exactly the same 
as on the original instance.
This is due to a global settings backup problem I am trying to solve.

I will try again with the same users and tell you if it works.

Thanks for your help.
Regards,

Cedric

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
ced
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

ced
Sorry, I thought I had installed PostGIS 2.1 on my backup server, but it is 2.3 that has been finally installed.

I don't understand how that could be.

The server is on Debian Jessie, and I did the following packages installation:

 apt-get update && apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 && \
    apt-get install -y gdal-bin libgdal-dev && \
    apt-get install -y postgresql-9.3-postgis-2.1

Why do I get the 2.3 version of PostGIS installed? How can I downgrade to 2.1?

Thanks for you help.
Regards,

Cedric

Hi,

Still same issue with two servers having the same configuration.

Regards,

Cedric





_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

Shane StClair-2
Was a solution to this problem ever discovered? I'm running into exactly the same problem (backup from PostGIS 2.1 with raster tables can't be restored to PostGIS 2.3).

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)
CONTEXT:  SQL function "st_coveredby" during inlining
COPY coei_166_3857, line 1: "1  010000010091C9D81F26AAE94091C9D81F26AAE9C082B6A532F81B73C103DAD0FE58846041000000000000000000000000..."
pg_restore: [archiver (db)] Error from TOC entry 187538; 0 223203 TABLE DATA coei_196_3857 asdf
pg_restore: [archiver (db)] COPY failed for table "coei_196_3857": ERROR:  operator does not exist: public.geometry @ public.geometry
LINE 1: SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)


On Fri, Nov 25, 2016 at 6:48 AM Cedric Duprez <[hidden email]> wrote:
Sorry, I thought I had installed PostGIS 2.1 on my backup server, but it is 2.3 that has been finally installed.

I don't understand how that could be.

The server is on Debian Jessie, and I did the following packages installation:

 apt-get update && apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 && \
    apt-get install -y gdal-bin libgdal-dev && \
    apt-get install -y postgresql-9.3-postgis-2.1

Why do I get the 2.3 version of PostGIS installed? How can I downgrade to 2.1?

Thanks for you help.
Regards,

Cedric


Hi,

Still same issue with two servers having the same configuration.

Regards,

Cedric




_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

Regina Obe-2

Shane,

 

I've ticketed this issue here: https://trac.osgeo.org/postgis/ticket/3750

Most of these issues related to restore were fixed in PostGIS 2.3, but looks like you found a missed spot.

 

To fix, you should have a folder in your postgresql install share/extension with a file called postgis.sql

 

Search for $1 @ $2

 

Should be around line 4057

 

Change that to:

 

$1 OPERATOR(@extschema@.@) $2

 

 

And then try to reinstall your backup in a clean database.

 

Let me know if you still run into issues.

 

Thanks,

Regina

PostGIS PSC Member

http://www.postgis.us

http://postgis.net

 

 

 

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Shane StClair
Sent: Tuesday, May 02, 2017 3:55 PM
To: [hidden email]
Subject: Re: [postgis-users] Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

 

Was a solution to this problem ever discovered? I'm running into exactly the same problem (backup from PostGIS 2.1 with raster tables can't be restored to PostGIS 2.3).

 

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

QUERY:  SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)

CONTEXT:  SQL function "st_coveredby" during inlining

COPY coei_166_3857, line 1: "1  010000010091C9D81F26AAE94091C9D81F26AAE9C082B6A532F81B73C103DAD0FE58846041000000000000000000000000..."

pg_restore: [archiver (db)] Error from TOC entry 187538; 0 223203 TABLE DATA coei_196_3857 asdf

pg_restore: [archiver (db)] COPY failed for table "coei_196_3857": ERROR:  operator does not exist: public.geometry @ public.geometry

LINE 1: SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)

 

 

On Fri, Nov 25, 2016 at 6:48 AM Cedric Duprez <[hidden email]> wrote:

Sorry, I thought I had installed PostGIS 2.1 on my backup server, but it is 2.3 that has been finally installed.

I don't understand how that could be.

The server is on Debian Jessie, and I did the following packages installation:

 apt-get update && apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 && \
    apt-get install -y gdal-bin libgdal-dev && \
    apt-get install -y postgresql-9.3-postgis-2.1

Why do I get the 2.3 version of PostGIS installed? How can I downgrade to 2.1?

Thanks for you help.
Regards,

Cedric




Hi,

Still same issue with two servers having the same configuration.

Regards,

Cedric

 

 

 

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

Shane StClair-2
Hi Regina,

Thanks so much for the quick response, that did the trick! 

-Shane

On Tue, May 2, 2017 at 1:53 PM Regina Obe <[hidden email]> wrote:

Shane,

 

I've ticketed this issue here: https://trac.osgeo.org/postgis/ticket/3750

Most of these issues related to restore were fixed in PostGIS 2.3, but looks like you found a missed spot.

 

To fix, you should have a folder in your postgresql install share/extension with a file called postgis.sql

 

Search for $1 @ $2

 

Should be around line 4057

 

Change that to:

 

$1 OPERATOR(@extschema@.@) $2

 

 

And then try to reinstall your backup in a clean database.

 

Let me know if you still run into issues.

 

Thanks,

Regina

PostGIS PSC Member

http://www.postgis.us

http://postgis.net

 

 

 

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Shane StClair
Sent: Tuesday, May 02, 2017 3:55 PM
To: [hidden email]
Subject: Re: [postgis-users] Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

 

Was a solution to this problem ever discovered? I'm running into exactly the same problem (backup from PostGIS 2.1 with raster tables can't be restored to PostGIS 2.3).

 

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

QUERY:  SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)

CONTEXT:  SQL function "st_coveredby" during inlining

COPY coei_166_3857, line 1: "1  010000010091C9D81F26AAE94091C9D81F26AAE9C082B6A532F81B73C103DAD0FE58846041000000000000000000000000..."

pg_restore: [archiver (db)] Error from TOC entry 187538; 0 223203 TABLE DATA coei_196_3857 asdf

pg_restore: [archiver (db)] COPY failed for table "coei_196_3857": ERROR:  operator does not exist: public.geometry @ public.geometry

LINE 1: SELECT $1 @ $2 AND public._ST_CoveredBy($1,$2)

 

 

On Fri, Nov 25, 2016 at 6:48 AM Cedric Duprez <[hidden email]> wrote:

Sorry, I thought I had installed PostGIS 2.1 on my backup server, but it is 2.3 that has been finally installed.

I don't understand how that could be.

The server is on Debian Jessie, and I did the following packages installation:

 apt-get update && apt-get install -y postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 && \
    apt-get install -y gdal-bin libgdal-dev && \
    apt-get install -y postgresql-9.3-postgis-2.1

Why do I get the 2.3 version of PostGIS installed? How can I downgrade to 2.1?

Thanks for you help.
Regards,

Cedric




Hi,

Still same issue with two servers having the same configuration.

Regards,

Cedric

 

 

 

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
ced
Reply | Threaded
Open this post in threaded view
|

Re: Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

ced
Hi Regina,

Thanks a lot for your help and for the bug report.
The temporary fix worked also for me.

Regards,

Cedric



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users