[postgis-users] restore problem

Wed Nov 11 14:40:59 PST 2015

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 <darrel.maddy at newcastle.ac.uk>
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:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Paragon Corporation
> *Sent:* 11 November 2015 21:43
>
> *To:* 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
> *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:postgis-users-bounces at lists.osgeo.org
> <postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Darrel Maddy
> *Sent:* Wednesday, November 11, 2015 2:03 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *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:postgis-users-bounces at lists.osgeo.org
> <postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Darrel Maddy
> *Sent:* 10 November, 2015 10:25 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *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:postgis-users-bounces at lists.osgeo.org
> <postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Bborie Park
> *Sent:* 10 November 2015 22:20
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *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 <
> darrel.maddy at newcastle.ac.uk> 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:postgis-users-bounces at lists.osgeo.org] *On
> Behalf Of *Bborie Park
> *Sent:* 10 November 2015 21:57
>
>
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *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 <
> darrel.maddy at 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-bounces at lists.osgeo.org] *On
> Behalf Of *Bborie Park
> *Sent:* 10 November 2015 21:22
> *To:* PostGIS Users Discussion <postgis-users at 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.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?
>
>
>
> 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
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>

-- 
Richard W. Greenwood, PLS
www.greenwoodmap.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151111/645a73ef/attachment.html>