Given that the core problem has to do with the restore process' search_path, no amount of advice will help you get around the restrictions in place with the restore process without jumping into that process.
In pgAdmin, you can check the boxes "Pre-data" and "Data" of the "Restore Options #1" tab. This won't restore the post-data portion of the backup as restoring post-data should be causing your error. You could also try unchecking the box "Exit On Error" of the "Restore Options #2" tab. -bborie On Tue, Nov 10, 2015 at 1:29 PM, Darrel Maddy <darrel.ma...@newcastle.ac.uk> wrote: > Dear Bborie, > > > > Yikes – that makes little sense to me. I am new to both postgres and > postgis and would have hoped that backup and restore would have been simple > push-button exercise! > > > > As I am using PgAdmin I have no idea how to follow your instructions L > > > > Anyone care to give me an idiots guide? > > > > Sorry > > > > Darrel > > > > > > *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On > Behalf Of *Bborie Park > *Sent:* 10 November 2015 21:22 > *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org> > *Subject:* Re: [postgis-users] restore problem > > > > This problem arises because PostgreSQL sets the search path during the > restore process. Unfortunately, the search path is quite restrictive. > > > > You should be able to do your restore in three sections using pg_restore's > --section flag. > > > > The error looks like it has to be the post-data section, which you should > output to a text file, change the "SET search_path" statements and then run > through psql. > > > > -bborie > > > > On Tue, Nov 10, 2015 at 1:11 PM, Darrel Maddy < > darrel.ma...@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? > > > > 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@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > postgis-users@lists.osgeo.org > http://lists.osgeo.org/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/postgis-users