[postgis-users] Restoring a dump

Mon May 19 15:34:13 PDT 2014

Well, the the thing is psql is what's initially giving me the error
that function st_bandmetadata(public.raster, integer[]) does not exist.

Would it be okay to let the restore continue even with those errors? I just
simply want all the data back into a database so I can get our webapp back
online.

Our data is static, so I don't see the benefit of dumping each database
individually. I only did this since we were having issues upgrading
Postgres and PostGIS, along with other Ubuntu issues.

Thanks for all the help. I will start afresh tomorrow morning.

Cheers,
Jayson

On Mon, May 19, 2014 at 5:24 PM, Bborie Park <dustymugs at gmail.com> wrote:

> Jayson,
>
> Too late now but you generally don't want to use pg_dumpall as that dumps
> a database cluster (all databases and catalog data) into one text (SQL)
> file. You may need to go down this nasty road...
>
> 1. Set up a clean postgresql server
>
> 2. Use psql to restore the database cluster to that new server
>
> 3. Dump your databases individually using pg_dump and the custom file
> format ("-F c")
>
> 4. On your final database server, restore each database using pg_restore.
> I usually extract the backup from the custom file format to a text file as
> I have to modify the search_path parameter to include my schema layout.
>
> -bborie
>
>
> On Mon, May 19, 2014 at 3:11 PM, Jayson Gallardo <jaysontrades at gmail.com>wrote:
>
>> We have about that many raster sources that are tiled into the database.
>> I would like to try your suggestion, however when I run pg_restore it tells
>> me that my dump is text format and that I should use psql. I don't see an
>> option for psql to do a restore schema only.
>>
>>
>> On Mon, May 19, 2014 at 4:14 PM, Jason Mathis <
>> jmathis at redzonesoftware.com> wrote:
>>
>>> Are you getting that error on the postgis_restore.pl?
>>>
>>> If yes I am not sure about that one I will leave it up to the pros. I
>>> think there is a bug open somewhere about this issue…Basically its calling
>>> a function (to create the constraint) that is in another schema not the one
>>> you are restoring to and the call is not fully qualified.
>>>
>>> In the past with raster tables I have had to go this route:
>>>
>>>    1. restore schema only
>>>    2. drop all raster constraints for each table
>>>    http://postgis.net/docs/RT_DropRasterConstraints.html
>>>    3. restore the data
>>>    4. add all raster constants back for each table
>>>    http://postgis.net/docs/RT_AddRasterConstraints.htm
>>>
>>> I actually had to create a separate script to run through my tables
>>> because we have about 25-30 raster tables.
>>>
>>>
>>> On May 19, 2014 at 3:02:32 PM, Jayson Gallardo (jaysontrades at gmail.com)
>>> wrote:
>>>
>>>  Yes. The database has rasters. I just tried loading rtpostgis.sql,
>>> etc., but none of those seemed to help at all. I did load legacy.sql before
>>> the dump as well.
>>>
>>>
>>> On Mon, May 19, 2014 at 3:54 PM, Jason Mathis <
>>> jmathis at redzonesoftware.com> wrote:
>>>
>>>>
>>>>  Do you have raster columns/tables?
>>>>
>>>> On May 19, 2014 at 2:47:47 PM, Jayson Gallardo (jaysontrades at gmail.com)
>>>> wrote:
>>>>
>>>>   So, I finally got around to running the restore, but I get this
>>>> following error:
>>>>
>>>> ERROR:  function st_bandmetadata(public.raster, integer[]) does not
>>>> exist
>>>>
>>>> Any ideas?
>>>>
>>>>
>>>> On Mon, May 19, 2014 at 12:29 PM, Jason Mathis <
>>>> jmathis at redzonesoftware.com> wrote:
>>>>
>>>>>  What do you mean, starting from scratch? You have to install
>>>>> postgresql/postgis and then follow the answers/links from sandro. You
>>>>> should be good with that dump.
>>>>>
>>>>>  good luck!
>>>>>
>>>>>
>>>>> On May 19, 2014 at 10:22:38 AM, Jayson Gallardo (
>>>>> jaysontrades at gmail.com) wrote:
>>>>>
>>>>>  Here's the thing, all I have is a dump (postgres_dump_03172014.gz)
>>>>> using pg_dumpall. The server has been reformatted with a clean install of
>>>>> Debian. I haven't even installed Postgres yet. So, what are my options
>>>>> then? I really would hate to start from scratch.
>>>>>
>>>>>
>>>>> On Mon, May 19, 2014 at 10:15 AM, Sandro Santilli <strk at keybit.net>wrote:
>>>>>
>>>>>> On Mon, May 19, 2014 at 09:39:20AM -0500, Jayson Gallardo wrote:
>>>>>> > Hey, all. So, we recently re-did our server. We were previously
>>>>>> running
>>>>>> > Ubuntu, and now we are running Debian. I had performed a pg_dumpall
>>>>>> on our
>>>>>> > Postgres database (with PostGIS) and would now like to restore said
>>>>>> dump.
>>>>>> > However, I want to make sure I do it right.
>>>>>>
>>>>>> Follow the instructions here:
>>>>>>
>>>>>>
>>> http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade
>>>
>>>   > Since I dumped everything, will I need to create all the databases
>>>>>> before
>>>>>> > restoring the dump?
>>>>>> >
>>>>>> > I should probably install the Postgis plug-in first, right?
>>>>>>
>>>>>> You should create the databases and spatially-enable them
>>>>>> (could be a chance to switch to EXTENSION-based install).
>>>>>>
>>>>>> > My dump is pretty big at 101 GB (it contains 3m resolution DEMs of
>>>>>> Iowa).
>>>>>> > Is there anything I need to do in order to minimize errors in the
>>>>>> restore?
>>>>>>
>>>>>> I guess you could reinstall each database separately, to at least
>>>>>> be more in control of what fails ...
>>>>>>
>>>>>> --strk;
>>>>>>
>>>>>>  ()  ASCII ribbon campaign  --  Keep it simple !
>>>>>>  /\  http://strk.keybit.net/rants/ascii_mails.txt
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org
>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>> This transmission contains confidential and privileged information
>>>>> intended solely for the party identified above. If you receive this message
>>>>> in error, you must not use it or convey it to others. Please destroy it
>>>>> immediately and contact the sender at (303) 386-3955 or by return
>>>>> e-mail to the sender.
>>>>>
>>>>
>>>>
>>>> This transmission contains confidential and privileged information
>>>> intended solely for the party identified above. If you receive this message
>>>> in error, you must not use it or convey it to others. Please destroy it
>>>> immediately and contact the sender at (303) 386-3955 or by return
>>>> e-mail to the sender.
>>>>
>>>
>>>
>>> This transmission contains confidential and privileged information
>>> intended solely for the party identified above. If you receive this message
>>> in error, you must not use it or convey it to others. Please destroy it
>>> immediately and contact the sender at (303) 386-3955 or by return
>>> e-mail to the sender.
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140519/8d3daeed/attachment.html>