[postgis-users] Restoring a dump

Jayson Gallardo jaysontrades at gmail.com
Mon May 19 15:47:24 PDT 2014 

Honestly, I have no idea. I'm sure you probably don't remember, but you
assisted me in setting up this database last year.

One other thing that I noticed, though, is that this occurs after it
switches to my personal database which is where I tested a lot of things.
Our main database is in "production".

I am going to let the restore do it's thing overnight. Hopefully, the
production database doesn't have any issues.

Thanks again,
Jayson
On May 19, 2014 5:38 PM, "Bborie Park" <dustymugs at gmail.com> wrote:

> Pulling things out of thin air here but is your PostGIS installed in a
> separate schema? It makes me wonder as to the "public" schema in "st_bandmetadata(public.raster,
> integer[])".
>
> -bborie
>
>
> On Mon, May 19, 2014 at 3:34 PM, Jayson Gallardo <jaysontrades at gmail.com>wrote:
>
>> 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
>>>
>>
>>
>> _______________________________________________
>> 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/33e4a4d5/attachment.html>

More information about the postgis-users mailing list