Darrel, A couple people have already pointed out that creating the postgis extension in the target database before you restore is advisable. Going along with that I also recommend excluding postgis from your backup. Postgis is put in the "public" schema so I avoid putting anything else in the "public" schema and then exclude it from my backups. The backups are a little smaller, there are fewer notices when you restore, and upgrading the postgis versions is a breeze.
Best of luck, Rich On Wed, Nov 11, 2015 at 2:56 PM, Darrel Maddy <[email protected]> wrote: > Dear Regina, > > > > Many thanks for this. A least now it looks like this is nothing I have > done (my first assumption with all problems is that I did something wrong – > this assumption is usually correct). > > > > I had seen that ticket (this was the chatter I referred to) but assumed > with the Milestone set at 2.0.8 I was hoping this had been fixed by 2.2 – > clearly I do not understand this terminology! > > > > I will attempt to use this workaround tomorrow – hopefully it will resolve > the restore issue and I will be back tomorrow full of praise J > > > > Santa is bringing me a copy of your “PostrgreSQL: Up and Running book”, so > hopefully once I am good to go with the basics I can really start to see > what this combo can do. > > > > Best wishes > > > > Darrel > > > > > > > > *From:* postgis-users [mailto:[email protected]] *On > Behalf Of *Paragon Corporation > *Sent:* 11 November 2015 21:43 > > *To:* 'PostGIS Users Discussion' <[email protected]> > *Subject:* Re: [postgis-users] restore problem > > > > Darrel, > > > > Sadly you are not the only one. > > > > I have the issue documented here: > > > > https://trac.osgeo.org/postgis/ticket/2485 > > > > and I haven't come up with a generic enough fix I feel comfortable > including in PostGIS code. > > > > The work-around is to do this > > > > 1) Create your database and do CREATE EXTENSION postgis; > > 2) Then add search paths to the functions used in check constraints > > raster_constraint_info_regular_blocking > > _raster_constraint_nodata_values > > _raster_constraint_out_db > > > > _raster_constraint_pixel_types > > e.g. > > > > ALTER FUNCTION _raster_constraint_pixel_types(raster) > > SET search_path=pg_catalog,public,postgis; > > > > > > 3) Then restore your data. > > I'll try to write a full script up in the ticket > > > > Hope that helps, > > Regina > > > > > > *From:* postgis-users [mailto:[email protected] > <[email protected]>] *On Behalf Of *Darrel Maddy > *Sent:* Wednesday, November 11, 2015 2:03 PM > *To:* PostGIS Users Discussion <[email protected]> > *Subject:* Re: [postgis-users] restore problem > > > > OK can I come at this from a different direction? Are there any backup > options I should be selecting to eliminate this problem? > > > > I am really struggling to understand why I cannot backup and restore a > raster table – surely others have had this issue? At present while my > databases are small (i.e. <13GB) I can live with rebuilding if things fall > over but these tables will grow to TB sizes soon (if I deploy this for > real) and I cannot contemplate having no reliable backup. > > > > I have been selecting only the default options for backup – should I be > doing something differently? > > > > Could this be a problem with the raster tables themselves? They seem to > work with my SQL queries (including putting the tiles together and viewing > in QGIS) but is there anything I should be doing to them before I select > backup? If the backup does not report errors does this mean there are no > errors in the backup? > > > > I realise I have much to learn using postgres but I see little point in > putting in that much effort if backup/restore does not work for me. > > > > I really do appreciate the helpful suggestions that have been made so far > but are there any further suggestions? > > > > Darrel > > > > > > > > > > > > > > *From:* postgis-users [mailto:[email protected] > <[email protected]>] *On Behalf Of *Darrel Maddy > *Sent:* 10 November, 2015 10:25 PM > *To:* PostGIS Users Discussion <[email protected]> > *Subject:* Re: [postgis-users] restore problem > > > > The default appears to be custom. I did not change anything when I did > this. I just accepted the defaults. > > > > Darrel > > > > *From:* postgis-users [mailto:[email protected] > <[email protected]>] *On Behalf Of *Bborie Park > *Sent:* 10 November 2015 22:20 > *To:* PostGIS Users Discussion <[email protected]> > *Subject:* Re: [postgis-users] restore problem > > > > Odd. Looking at your error message again, it looks like the constraint is > already in place by the time the data is getting loaded... > > > > What are you restoring from? Tar, custom or text? > > > > On Tue, Nov 10, 2015 at 2:16 PM, Darrel Maddy < > [email protected]> wrote: > > Dear Bborie, > > > > Apologies, I was obviously not clear – I was not asking about the problem > itself, I can accept this is a difficult problem to resolve. I was simply > asking for instruction on how to use your work around. This you kindly > provided. Alas, however, even with the boxes checked, I get the same error > and the raster table (dems) is empty. > > > > Darrel > > > > *From:* postgis-users [mailto:[email protected]] *On > Behalf Of *Bborie Park > *Sent:* 10 November 2015 21:57 > > > *To:* PostGIS Users Discussion <[email protected]> > *Subject:* Re: [postgis-users] restore problem > > > > 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 < > [email protected]> 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:[email protected]] *On > Behalf Of *Bborie Park > *Sent:* 10 November 2015 21:22 > *To:* PostGIS Users Discussion <[email protected]> > *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 < > [email protected]> 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 > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users > > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users > -- Richard W. Greenwood, PLS www.greenwoodmap.com
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
