PostGIS raster data restore issue

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

PostGIS raster data restore issue

kberger

Hi,

 

We want to test our PostGIS Version 2.2.2.1/PostgreSQL 9.5.2.1 restore. We have raster data loaded into the database.

 

The backup command we are using is: pg_dump.exe -c -C (We specify -C as we want the SQL for creating databases to be in the dump file - we want to simplify the restore operation by having the database automatically created as opposed to having to create an empty database then restore the data into it). The dump file is 32 GB so modifying the dump file in Notepad++ is not an option.

 

The restore command we are using is: psql -U postgres -f <dumpfile>. However, we get several restore errors such as:

 

ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist

 

I am thinking that the only thing we can do is to create an empty database then restore the data into it (which as I mentioned adds more steps to restore). Is my rationale correct?

 

NOTE: the restore does work fine on a database that does not contain raster data.

 

Kevin


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS raster data restore issue

Regina Obe

Unfortunately you have to create the database first.  I'm working on fixing this for PostGIS 2.3 so you can do without creating db first, but for PostGIS 2.2.2 steps would be

 

 

1)      Do your backup with pg_dump.exe (custom is preferred over plain text)

2)      Create new database, install postgis extension

3)      Run the share/contrib/postgis-2.2/ rtpostgis_proc_set_search_path.sql  (note this script is there for PostGIS 2.2.2+)

4)      Restore your data as usual with pg_restore (if custom backup)  or psql if plain text.

 

Hope that helps,

Regina

http://www.postgis.us

http://postgis.net

 

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Kevin Berger
Sent: Monday, July 25, 2016 1:06 PM
To: [hidden email]
Subject: [postgis-users] PostGIS raster data restore issue

 

Hi,

 

We want to test our PostGIS Version 2.2.2.1/PostgreSQL 9.5.2.1 restore. We have raster data loaded into the database.

 

The backup command we are using is: pg_dump.exe -c -C (We specify -C as we want the SQL for creating databases to be in the dump file - we want to simplify the restore operation by having the database automatically created as opposed to having to create an empty database then restore the data into it). The dump file is 32 GB so modifying the dump file in Notepad++ is not an option.

 

The restore command we are using is: psql -U postgres -f <dumpfile>. However, we get several restore errors such as:

 

ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist

 

I am thinking that the only thing we can do is to create an empty database then restore the data into it (which as I mentioned adds more steps to restore). Is my rationale correct?

 

NOTE: the restore does work fine on a database that does not contain raster data.

 

Kevin


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS raster data restore issue

kberger

Thanks Regina, that helps.

1.       When will PostGIS 2.3 be available?

2.       FYI – although custom might be preferred, we had problems restoring custom backup. We were able to restore the backup in plain text format without issue. Our database is approximately 30 GB.

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Regina Obe
Sent: July 25, 2016 15:42
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] PostGIS raster data restore issue

 

Unfortunately you have to create the database first.  I'm working on fixing this for PostGIS 2.3 so you can do without creating db first, but for PostGIS 2.2.2 steps would be

 

 

1)      Do your backup with pg_dump.exe (custom is preferred over plain text)

2)      Create new database, install postgis extension

3)      Run the share/contrib/postgis-2.2/ rtpostgis_proc_set_search_path.sql  (note this script is there for PostGIS 2.2.2+)

4)      Restore your data as usual with pg_restore (if custom backup)  or psql if plain text.

 

Hope that helps,

Regina

http://www.postgis.us

http://postgis.net

 

 

 

From: postgis-users [[hidden email]] On Behalf Of Kevin Berger
Sent: Monday, July 25, 2016 1:06 PM
To:
[hidden email]
Subject: [postgis-users] PostGIS raster data restore issue

 

Hi,

 

We want to test our PostGIS Version 2.2.2.1/PostgreSQL 9.5.2.1 restore. We have raster data loaded into the database.

 

The backup command we are using is: pg_dump.exe -c -C (We specify -C as we want the SQL for creating databases to be in the dump file - we want to simplify the restore operation by having the database automatically created as opposed to having to create an empty database then restore the data into it). The dump file is 32 GB so modifying the dump file in Notepad++ is not an option.

 

The restore command we are using is: psql -U postgres -f <dumpfile>. However, we get several restore errors such as:

 

ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist

 

I am thinking that the only thing we can do is to create an empty database then restore the data into it (which as I mentioned adds more steps to restore). Is my rationale correct?

 

NOTE: the restore does work fine on a database that does not contain raster data.

 

Kevin


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: PostGIS raster data restore issue

Regina Obe

1.       I'd like it to be in September or some time before PostgreSQL 9,6, but we'll see – http://lists.osgeo.org/pipermail/postgis-devel/2016-July/025872.html  (still some loose ends to button up)

2.       What sort of problems did you have?  It should work if you create extension first, run the set path script and restore the data.  That's the way I do it.

If you are in a rush you could hack your share/extensions/postgis-2.2.2.sql file

 

Basically just copy the contents of share/contrib/postgis-2.2/ rtpostgis_proc_set_search_path.sql  to the end of the extension postgis-2.2.2.sql  and postgis.sql extension scripts.

 

That way when the restore runs CREATE EXTENSION postgis, it will automatically fix the paths of functions so the constraints don't fail when they are called.

 

Then your create database and load data in one call should work.

 

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Kevin Berger
Sent: Wednesday, July 27, 2016 10:18 AM
To: PostGIS Users Discussion <[hidden email]>
Subject: Re: [postgis-users] PostGIS raster data restore issue

 

Thanks Regina, that helps.

1.       When will PostGIS 2.3 be available?

2.       FYI – although custom might be preferred, we had problems restoring custom backup. We were able to restore the backup in plain text format without issue. Our database is approximately 30 GB.

 

From: postgis-users [[hidden email]] On Behalf Of Regina Obe
Sent: July 25, 2016 15:42
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] PostGIS raster data restore issue

 

Unfortunately you have to create the database first.  I'm working on fixing this for PostGIS 2.3 so you can do without creating db first, but for PostGIS 2.2.2 steps would be

 

 

1)      Do your backup with pg_dump.exe (custom is preferred over plain text)

2)      Create new database, install postgis extension

3)      Run the share/contrib/postgis-2.2/ rtpostgis_proc_set_search_path.sql  (note this script is there for PostGIS 2.2.2+)

4)      Restore your data as usual with pg_restore (if custom backup)  or psql if plain text.

 

Hope that helps,

Regina

http://www.postgis.us

http://postgis.net

 

 

 

From: postgis-users [[hidden email]] On Behalf Of Kevin Berger
Sent: Monday, July 25, 2016 1:06 PM
To: [hidden email]
Subject: [postgis-users] PostGIS raster data restore issue

 

Hi,

 

We want to test our PostGIS Version 2.2.2.1/PostgreSQL 9.5.2.1 restore. We have raster data loaded into the database.

 

The backup command we are using is: pg_dump.exe -c -C (We specify -C as we want the SQL for creating databases to be in the dump file - we want to simplify the restore operation by having the database automatically created as opposed to having to create an empty database then restore the data into it). The dump file is 32 GB so modifying the dump file in Notepad++ is not an option.

 

The restore command we are using is: psql -U postgres -f <dumpfile>. However, we get several restore errors such as:

 

ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist

 

I am thinking that the only thing we can do is to create an empty database then restore the data into it (which as I mentioned adds more steps to restore). Is my rationale correct?

 

NOTE: the restore does work fine on a database that does not contain raster data.

 

Kevin


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users