[postgis-users] restore problem

[Darrel Maddy]

OK trying this again without the prior messages to avoid the max limits.

Apologies if my original appears, when I tried to cancel the posting it seemed 
to ask for a ‘cookie’ code I did not have!

Anyhow, thanks to everyone.

D

From: Darrel Maddy
Sent: 12 November 2015 09:51
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: RE: [postgis-users] restore problem

Dear  Regina,

Many thanks again for this detailed explanation – this really does help me 
understand what is happening.  I did not have the clean flag set BUT when I 
tried this again this morning it has worked ☺. I have tried this on two 
databases now and both seem to have restored the raster data. Clearly I would 
not have figured this out without your instruction! It is a shame that this 
process is not as easy as I am sure everyone would like it to be but the 
workaround is simple once you know how and as long as this is documented I 
don’t see this as a deal breaker.

On one of my databases I do get a new (presumably trivial) error:
pg_restore: processing data for table "spatial_ref_sys"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3303; 0 35941 TABLE DATA 
spatial_ref_sys postgres
pg_restore: [archiver (db)] COPY failed for table "spatial_ref_sys": ERROR:  
duplicate key value violates unique constraint "spatial_ref_sys_pkey"
DETAIL:  Key (srid)=(5013) already exists.

This only happened on the second database I tried to restore and it may not be 
a significant issue. Should I ignore this?

Many thanks again from a very relieved

Darrel


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Regina Obe]

Darrel
> On one of my databases I do get a new (presumably trivial) error:
> pg_restore: processing data for table "spatial_ref_sys"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3303; 0 35941 TABLE
DATA > spatial_ref_sys postgres
> pg_restore: [archiver (db)] COPY failed for table "spatial_ref_sys":
> ERROR:  duplicate key value violates unique constraint
> "spatial_ref_sys_pkey"
> DETAIL:  Key (srid)=(5013) already exists.

Yes you can ignore.  It's possible for whatever reason that wasn't marked
as an exclude from backup and the data got saved but is shipped with
PostGIS.  I think pramsey came across that and fixed in 2.2.1.

At anyrate its a harmless error if your backup is stet to ignore errors
which is the default.

Thanks,
Regina



___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Paragon Corporation]

Just a slight clarification on what Rich just said

 

1)  If you installed PostGIS with CREATE EXTENSION  -- postgis functions, 
views, and included tables (except custom entries in spatial_ref_sys) aren't 
backed up anyway regardless what you do.  Only thing that is included in the 
backup is the command

CREATE EXTENSION postgis;  and custom spatial_ref_sys rows if you added any.

 

So it actually always ends up picking whatever the default postgis (that listed 
in share/extensions/postgis.control)   you have in your install.

 

If for whatever reason, you have multiple versions of PostGIS installed in your 
PostgreSQL instance, and you want your restore to not use the default PostGIS 
version (that listed in postgis.control),

 

Then you would need to do 

 

CREATE EXTENSION postgis version "2.1.8";  --for example

 

And then restore your data non-cleanly.

 

2)  This issue is only an issue if  your postgis extension is not in the 
same schema as your data – so best practices screws you in this regard.

The reason why it's not an issue with same schema is that restore always adds 
the schema of the table being restored in search path.

 

3)  This issue only affects data where either constraints or indexes 
defined on the table utilize functions that utilize other PostGIS functions or 
other non-pgcatalog functions.

 

So Darrel to answer your question.  You do not need it for all 16 constraint 
functions, just the 4 I mentioned that call out to other functions.  The reason 
for that is that on a table, the index functions and constraint functions, are 
always fully-qualified, so it's only when that full-qualified function then 
makes an unqualified call to another function that the issue ensues.

 

 

This issue isn't isolated to raster restores, but it affects raster much more 
that geometry/geography proper, since raster utilizes constraint functions that 
call other postgis functions and these get triggered as part of the restore to 
validate the constraints.

 

 

If you were to for example create an index with any postgis function that 
happens to call another function and your data happens not to be in the same 
schema as where postgis is installed, then you will have a restore problem.

In case of indexes it's especially annoying because you don't notice it until 
you notice things are much slower.  E.g. if you have a spatial index that uses 
ST_Transform (that's the common case I run into), this issue crops up.

 

So I also out of habit set the search_path on ST_Transform as well.

 

Thanks,

Regina

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Richard Greenwood
Sent: Wednesday, November 11, 2015 5:41 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
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 <darrel.ma...@newcastle.ac.uk 
<mailto:darrel.ma...@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 :)

 

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-boun...@lists.osgeo.org 
<mailto:postgis-users-boun...@lists.osgeo.org> ] On Behalf Of Paragon 
Corporation
Sent: 11 November 2015 21:43


To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org 
<mailto:postgis-users@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)

Re: [postgis-users] restore problem

[Paragon Corporation]

Darrel,

 

That all looks right but it seems the alter function part is being wiped out

 

If this statement is working:

 

ALTER FUNCTION _raster_constraint_nodata_values(raster)

 SET search_path=pg_catalog,public,postgis;

 

Then that error shouldn't be happening.  

 

This would happen if you are doing clean restore, as I think the restore would 
drop the postgis extension and then recreate thus losing the alter function 
settings you just put in.

 

 

Are you doing a clean restore – if you are uncheck that (if you are in pgAdmin) 
 

 

 

If you are doing with via command-line, make sure to leave out the 

 

-c or  --clean 

 

Switches.

 

 

I have to test this out.  It's been a while since I restored the database I had 
this issue with.  So I may have missed something else.

 

 

Thanks,

Regina

 

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Wednesday, November 11, 2015 2:03 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] restore problem

 

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  
010100344034C0E0CCB1D51741806692F80C41..."

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

 

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Darrel Maddy]

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  
010100344034C0E0CCB1D51741806692F80C41..."
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:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Richard Greenwood
Sent: 11 November 2015 22:41
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
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 
<darrel.ma...@newcastle.ac.uk<mailto:darrel.ma...@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 ☺

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-boun...@lists.osgeo.org<mailto:postgis-users-boun...@lists.osgeo.org>]
 On Behalf Of Paragon Corporation
Sent: 11 November 2015 21:43

To: 'PostGIS Users Discussion' 
<postgis-users@lists.osgeo.org<mailto:postgis-users@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-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: W

Re: [postgis-users] restore problem

[Darrel Maddy]

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:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Paragon Corporation
Sent: 11 November 2015 21:43
To: 'PostGIS Users Discussion' <postgis-users@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-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Wednesday, November 11, 2015 2:03 PM
To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@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-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: 10 November, 2015 10:25 PM
To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@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-boun...@lists.osgeo.org] On Behalf Of 
Bborie Park
Sent: 10 November 2015 22:20
To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@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.ma...@newcastle.ac.uk<mailto:darrel.ma...@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-boun...@lists.osgeo.org<mailto:postgis-users-boun...@lists.osgeo.org>]
 On Behalf Of Bborie Park
Sent: 10 November 2015 21:57

To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@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

Re: [postgis-users] restore problem

[Richard Greenwood]

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.ma...@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-boun...@lists.osgeo.org] *On
> Behalf Of *Paragon Corporation
> *Sent:* 11 November 2015 21:43
>
> *To:* 'PostGIS Users Discussion' <postgis-users@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-boun...@lists.osgeo.org
> <postgis-users-boun...@lists.osgeo.org>] *On Behalf Of *Darrel Maddy
> *Sent:* Wednesday, November 11, 2015 2:03 PM
> *To:* PostGIS Users Discussion <postgis-users@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-boun...@lists.osgeo.org
> <postgis-users-boun...@lists.osgeo.org>] *On Behalf Of *Darrel Maddy
> *Sent:* 10 November, 2015 10:25 PM
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> The default appears to be custom. I did not change a

Re: [postgis-users] restore problem

[Darrel Maddy]

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: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<mailto: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  
010100344034C0E0CCB1D51741806692F80C41..."


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<mailto: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

Re: [postgis-users] restore problem

[Tim Sutton]

Hi

> On 10 Nov 2015, at 23:26, Darrel Maddy <darrel.ma...@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-boun...@lists.osgeo.org 
> <mailto:postgis-users-boun...@lists.osgeo.org>] On Behalf Of Tim Sutton
> Sent: 10 November 2015 21:15
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org 
> <mailto:postgis-users@lists.osgeo.org>>
> Subject: Re: [postgis-users] restore problem
> 
> Hi
> On 10 Nov 2015, at 23:11, Darrel Maddy <darrel.ma...@newcastle.ac.uk 
> <mailto: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  
> 010100344034C0E0CCB1D51741806692F80C41..."
> 
> 
> 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@lists.osgeo.org <mailto:postgis-users@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 <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@lists.osgeo.org <mailto:postgis-users@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



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Tim Sutton]

Hi

> On 10 Nov 2015, at 23:33, Darrel Maddy <darrel.ma...@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-boun...@lists.osgeo.org 
> <mailto:postgis-users-boun...@lists.osgeo.org>] On Behalf Of Tim Sutton
> Sent: 10 November 2015 21:30
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org 
> <mailto:postgis-users@lists.osgeo.org>>
> Subject: Re: [postgis-users] restore problem
> 
> Hi
> 
> On 10 Nov 2015, at 23:26, Darrel Maddy <darrel.ma...@newcastle.ac.uk 
> <mailto:darrel.ma...@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-boun...@lists.osgeo.org 
> <mailto:postgis-users-boun...@lists.osgeo.org>] On Behalf Of Tim Sutton
> Sent: 10 November 2015 21:15
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org 
> <mailto:postgis-users@lists.osgeo.org>>
> Subject: Re: [postgis-users] restore problem
> 
> Hi
> On 10 Nov 2015, at 23:11, Darrel Maddy <darrel.ma...@newcastle.ac.uk 
> <mailto: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  
> 010100344034C0E0CCB1D51741806692F80C41..."
> 
> 
> 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 t

Re: [postgis-users] restore problem

[Bborie Park]

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 
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
> 010100344034C0E0CCB1D51741806692F80C41..."
>
>
>
>
>
> 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

Re: [postgis-users] restore problem

[Tim Sutton]

Hi
> On 10 Nov 2015, at 23:11, Darrel Maddy  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  
> 010100344034C0E0CCB1D51741806692F80C41..."
> 
> 
> 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@lists.osgeo.org 
> http://lists.osgeo.org/mailman/listinfo/postgis-users 
> 
—





Tim Sutton

Visit 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



signature.asc
Description: Message signed with OpenPGP using GPGMail
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Darrel Maddy]

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.

Darrel




From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Tim Sutton
Sent: 10 November 2015 21:15
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] restore problem

Hi
On 10 Nov 2015, at 23:11, Darrel Maddy 
<darrel.ma...@newcastle.ac.uk<mailto: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  
010100344034C0E0CCB1D51741806692F80C41..."


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@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/postgis-users

—


[cid:image001.png@01D11BFE.66AE30F0]

Tim Sutton

Visit 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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Darrel Maddy]

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?

Darrel

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Tim Sutton
Sent: 10 November 2015 21:30
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] restore problem

Hi

On 10 Nov 2015, at 23:26, Darrel Maddy 
<darrel.ma...@newcastle.ac.uk<mailto:darrel.ma...@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-boun...@lists.osgeo.org] On Behalf Of 
Tim Sutton
Sent: 10 November 2015 21:15
To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>>
Subject: Re: [postgis-users] restore problem

Hi
On 10 Nov 2015, at 23:11, Darrel Maddy 
<darrel.ma...@newcastle.ac.uk<mailto: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  
010100344034C0E0CCB1D51741806692F80C41..."


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@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
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<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@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/postgis-users

—


[cid:image001.png@01D11BFF.7AE714F0]

Tim Sutton

Visit 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] restore problem

[Darrel Maddy]

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  
010100344034C0E0CCB1D51741806692F80C41..."


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

Re: [postgis-users] restore problem

[Darrel Maddy]

Hi Tim,

Yes, when I create the extension in an empty database  
st_bandmetadata(raster,integer) is listed as a function in the public schema.

Darrel

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Tim Sutton
Sent: 10 November 2015 21:44
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] restore problem

Hi

On 10 Nov 2015, at 23:33, Darrel Maddy 
<darrel.ma...@newcastle.ac.uk<mailto:darrel.ma...@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-boun...@lists.osgeo.org] On Behalf Of 
Tim Sutton
Sent: 10 November 2015 21:30
To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>>
Subject: Re: [postgis-users] restore problem

Hi

On 10 Nov 2015, at 23:26, Darrel Maddy 
<darrel.ma...@newcastle.ac.uk<mailto:darrel.ma...@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-boun...@lists.osgeo.org] On Behalf Of 
Tim Sutton
Sent: 10 November 2015 21:15
To: PostGIS Users Discussion 
<postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>>
Subject: Re: [postgis-users] restore problem

Hi
On 10 Nov 2015, at 23:11, Darrel Maddy 
<darrel.ma...@newcastle.ac.uk<mailto: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  
010100344034C0E0CCB1D51741806692F80C41..."


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

Re: [postgis-users] restore problem

[Bborie Park]

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
> 010100344034C0E0CCB1D51741806692F80C41..."
>
>
>
>
>
> 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

Re: [postgis-users] restore problem

[Bborie Park]

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.ma...@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-boun...@lists.osgeo.org] *On
> Behalf Of *Bborie Park
> *Sent:* 10 November 2015 21:57
>
> *To:* PostGIS Users Discussion <postgis-users@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.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
> 010100344034C0E0CCB1D51741806692F80C41..."
>
>
>
>
>
> 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 

Re: [postgis-users] restore problem

[Darrel Maddy]

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-boun...@lists.osgeo.org] On Behalf Of 
Bborie Park
Sent: 10 November 2015 21:57
To: PostGIS Users Discussion <postgis-users@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.ma...@newcastle.ac.uk<mailto: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 ☹

Anyone care to give me an idiots guide?

Sorry

Darrel


From: postgis-users 
[mailto:postgis-users-boun...@lists.osgeo.org<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<mailto: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<mailto: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  
010100344034C0E0CCB1D51741806692F80C41..."


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<mailto:postgis-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto: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