Dear Regina and Rich,
I tried to follow your instructions by doing the following:
1. I redid the backup of my database excluding the public schema. This
completed with an exit code 0 so I am assuming no problems.
2. I created the ‘new’ database on my laptop and then added the postgis
extension. All functions were there and the extension is in the Public schema.
3. I then ran the following script
ALTER FUNCTION _raster_constraint_pixel_types(raster)
SET search_path=pg_catalog,public,postgis;
ALTER FUNCTION _raster_constraint_info_regular_blocking(name,name,name)
SET search_path=pg_catalog,public,postgis;
ALTER FUNCTION _raster_constraint_nodata_values(raster)
SET search_path=pg_catalog,public,postgis;
ALTER FUNCTION _raster_constraint_out_db(raster)
SET search_path=pg_catalog,public,postgis;
4. I then attempted the restore and unfortunately the same thing happened
again .i.e
pg_restore: processing data for table "dems"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3418; 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: ...(round(nodatavalue::numeric, 10))::numeric[] FROM st_bandmet...
^
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
QUERY: SELECT array_agg(round(nodatavalue::numeric, 10))::numeric[] FROM
st_bandmetadata($1, ARRAY[]::int[]);
CONTEXT: SQL function "_raster_constraint_nodata_values" during inlining
COPY dems, line 1: "1
0100000100000000000000344000000000000034C0E0CCCCCCB1D517418066666692F80C41000000000000000000000000..."
pg_restore: executing SEQUENCE SET dems_rid_seq
Firstly, is this what you expected me to do or did I misunderstand? Secondly,
does this mean that I have to do this for each of the _raster_constraint
functions? (I think there are 16 of them)
Hopefully I am making progress.
Darrel
From: postgis-users [mailto:[email protected]] On Behalf Of
Richard Greenwood
Sent: 11 November 2015 22:41
To: PostGIS Users Discussion <[email protected]>
Subject: Re: [postgis-users] restore problem
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]<mailto:[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 ☺
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]<mailto:[email protected]>]
On Behalf Of Paragon Corporation
Sent: 11 November 2015 21:43
To: 'PostGIS Users Discussion'
<[email protected]<mailto:[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]] On Behalf Of
Darrel Maddy
Sent: Wednesday, November 11, 2015 2:03 PM
To: PostGIS Users Discussion
<[email protected]<mailto:[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]] On Behalf Of
Darrel Maddy
Sent: 10 November, 2015 10:25 PM
To: PostGIS Users Discussion
<[email protected]<mailto:[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]] On Behalf Of
Bborie Park
Sent: 10 November 2015 22:20
To: PostGIS Users Discussion
<[email protected]<mailto:[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]<mailto:[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]<mailto:[email protected]>]
On Behalf Of Bborie Park
Sent: 10 November 2015 21:57
To: PostGIS Users Discussion
<[email protected]<mailto:[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]<mailto:[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 ☹
Anyone care to give me an idiots guide?
Sorry
Darrel
From: postgis-users
[mailto:[email protected]<mailto:[email protected]>]
On Behalf Of Bborie Park
Sent: 10 November 2015 21:22
To: PostGIS Users Discussion
<[email protected]<mailto:[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]<mailto:[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]<mailto:[email protected]>
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]<mailto:[email protected]>
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]<mailto:[email protected]>
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]<mailto:[email protected]>
http://lists.osgeo.org/mailman/listinfo/postgis-users
--
Richard W. Greenwood, PLS
www.greenwoodmap.com<http://www.greenwoodmap.com>
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
