[postgis-users] restore problem

Tue Nov 10 13:44:20 PST 2015

Hi

> On 10 Nov 2015, at 23:33, Darrel Maddy <darrel.maddy at newcastle.ac.uk> wrote:
> 
> Hi Tim,
> 
> I am on windows and using the latest version 2.2 of postgis (and postgresql 9.4). My original databases do not have pg_raster as an extension – I am assuming that is not required now?
> 

I think it is anyway lumped in with postgis, but it certainly seems that the raster based functions are missing:

pg_restore: [archiver (db)] COPY failed for table "dems": ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist
LINE 1:  SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1

If you create an empty db, ensure that postgis extension is enabled can you check that it has st_bandmetadata present before doing the restore?

My cheat of loading postgis before doing the restore is a simpler workaround for Bborie’s process since the st_* functions from your backup (with their possibly incorrect lib paths) will be rejected if the functions are already installed, but the rest of the data in your dump *should* restore.

Regards

Tim

> Darrel
> 
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org>] On Behalf Of Tim Sutton
> Sent: 10 November 2015 21:30
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>>
> Subject: Re: [postgis-users] restore problem
> 
> Hi
> 
> On 10 Nov 2015, at 23:26, Darrel Maddy <darrel.maddy at newcastle.ac.uk <mailto:darrel.maddy at newcastle.ac.uk>> wrote:
> 
> Dear Tim,
> 
> Thanks for this suggestion.  I had assumed that the extensions were part of the backup as they appear on the manifest and indeed they appear in the restored database.
> 
> Alas this made no difference – I just tried it, creating the extension first before restore and I get exactly the same error.
> 
> And is pg_raster also installed in the target db before doing the restore? I don’t know if it is considered best practice by the postgis guru’s, but I usually install any extensions before doing a restore to deal with version differences between source and target systems gracefully...
> 
> Regards
> 
> Tim
> 
> 
> 
> 
> Darrel
> 
> 
> 
> 
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org>] On Behalf Of Tim Sutton
> Sent: 10 November 2015 21:15
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>>
> Subject: Re: [postgis-users] restore problem
> 
> Hi
> On 10 Nov 2015, at 23:11, Darrel Maddy <darrel.maddy at newcastle.ac.uk <mailto:darrel.maddy at newcastle.ac.uk>> wrote:
> 
> Dear all,
> 
> Like many here I suspect I want to keep a copy of my main databases held on my workstation, on my laptop. I used PgAdmin to backup the databases concerned (the backup files look about the size I was expecting)  but restore on my laptop did not successfully complete.  All my shp file tables restored without issues but my raster tables (these are tiled rasters) would not. Looking at the error in the restore window of PgAdmin this appears to be a problem with a function/constraint.
> 
> pg_restore: processing data for table "dems"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3459; 0 94054 TABLE DATA dems postgres
> pg_restore: [archiver (db)] COPY failed for table "dems": ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist
> LINE 1:  SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...
>                                                   ^
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY:   SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1, ARRAY[]::int[]);
> CONTEXT:  SQL function "_raster_constraint_pixel_types" during inlining
> COPY dems, line 1: "1              0100000100000000000000344000000000000034C0E0CCCCCCB1D517418066666692F80C41000000000000000000000000..."
> 
> 
> I did a quick search and found some old chatter on this issue but from the messages I read I would have expected this issue to have been cleared up in postgis 2 .
> 
> Obviously I cannot contemplate having mission critical data in a database which does not backup/restore correctly so I am assuming there is a fix which avoids this issue or there is another way to make a copy of the database for transfer elsewhere?
> 
> 
> Have you enabled the PostGIS extension on the destination database before attempting to restore?
> 
> Regards
> 
> Tim
> 
> 
> 
> Once again any help would be gratefully received.
> 
> Best wishes
> 
> Darrel
> 
> ps.   Apologies for the string of questions I have asked lately – I will go silent once more shortly as I must move on to other things.
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/mailman/listinfo/postgis-users <http://lists.osgeo.org/mailman/listinfo/postgis-users>
> 
> —
> 
> 
> <image001.png>
> 
> 
> Tim Sutton
> 
> Visit http://kartoza.com <http://kartoza.com/> to find out about open source:
> 
> * Desktop GIS programming services
> * Geospatial web development
> * GIS Training
> * Consulting Services
> 
> Skype: timlinux Irc: timlinux on #qgis at freenode.net <http://freenode.net/>
> Tim is a member of the QGIS Project Steering Committee
> 
> Kartoza is a merger between Linfiniti and Afrispatial
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/mailman/listinfo/postgis-users <http://lists.osgeo.org/mailman/listinfo/postgis-users>
> 
> —
> 
> 
> <image001.png>
> 
> 
> Tim Sutton
> 
> Visit http://kartoza.com <http://kartoza.com/> to find out about open source:
> 
> * Desktop GIS programming services
> * Geospatial web development
> * GIS Training
> * Consulting Services
> 
> Skype: timlinux Irc: timlinux on #qgis at freenode.net <http://freenode.net/>
> Tim is a member of the QGIS Project Steering Committee
> 
> Kartoza is a merger between Linfiniti and Afrispatial
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://lists.osgeo.org/mailman/listinfo/postgis-users <http://lists.osgeo.org/mailman/listinfo/postgis-users>
—

Tim Sutton

Visit http://kartoza.com <http://kartoza.com/> to find out about open source:

* Desktop GIS programming services
* Geospatial web development
* GIS Training
* Consulting Services

Skype: timlinux Irc: timlinux on #qgis at freenode.net
Tim is a member of the QGIS Project Steering Committee

Kartoza is a merger between Linfiniti and Afrispatial

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151110/fa37a313/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: KartozaLogo160x66.png
Type: image/png
Size: 9324 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151110/fa37a313/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 455 bytes
Desc: Message signed with OpenPGP using GPGMail
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151110/fa37a313/attachment.sig>