Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

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

Restore a raster dump from PostGIS 2.1.0 to PostGIS 2.3.0

Cedric Duprez
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



--
View this message in context: http://postgis.17.x6.nabble.com/Restore-a-raster-dump-from-PostGIS-2-1-0-to-PostGIS-2-3-0-tp5010627.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
_______________________________________________
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-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
Reply | Threaded
Open this post in threaded view
|

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

Cedric Duprez
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
Reply | Threaded
Open this post in threaded view
|

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

Cedric Duprez
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
Reply | Threaded
Open this post in threaded view
|

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

Cedric Duprez
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
Reply | Threaded
Open this post in threaded view
|

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

Cedric Duprez
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