SUM:: [ELGIS] Untangling pgdg postgresql90 from Centos and ELGIS

[Bill Thoen]

Thanks for the help, everyone. This has been resolved. 

For anyone who finds themselves with PostgreSQL 9  and the ELGIS suite of GIS tools installed on their system, you've probably noticed that they conflict with each other when you try to upgrade. Since I needed the GIS tools working in CentOS 5.5 more than I needed the new features in Pg 9, I decided to downgrade my database to PostgreSQL 8.4 to fix this problem.

To avoid the mistakes I made (due to my lack of familiarity with repositories and yum) I'll summarize the correct way to resolve this problem and leave out the frustrations, confusion and the brief ball-of-ice-in-the-belly moments of fear that my 58Gb database just went down the drain.

First make a complete backup using 'pg_dumpall > myBackup.sql'
This does a more complete job than pg_dump.  You can do this with any superuser account, but if you use the postgres account, that's the one you'll have available when you re-populate the database. While this is running, read http://www.postgresql.org/docs/8.4/static/install-upgrading.html and  
http://wiki.osgeo.org/wiki/Enterprise_Linux_GIS#How_to_enable_the_ELGIS5_repository. 

When you've got a good backup, shut down postgres with 'service postgresql90 stop'. Since the default data directory for Pg 9 is under /postdata and the default for Pg 8.4 is under /var/lib/pgaql/data, you don't need to take any special steps to protect your existing data, should you want to go back.
..
Next, clean out PGDG repo and it's cache. At the very least put an 'exclude postgresql*' directive in it because we want to get postgresql84 from CentOS-Base.repo, not PGDG. I just deleted pgdg*.repo and then removed the reference in /var/cache/yum/. Make sure you haven't blocked postgresql* from [base] or [updates] with 'exclude postgresql*' like you may have been instructed to do in some of the docs ornline about repos.

Then make sure you have the most recent version of all the software. The URLs you need are listed in the web pages mentioned earlier. Enter 'yum upgrade' and that will get you all the latest GIS and database software. Then just follow the instructions for creating a new database cluster, and then run your backup by entering pgsql -dpostgres -Upostgres -f myBackup.sql 2&1> load_errors.log. 

That should do it. Now when you run !yum update' you won't have any conflicts from that source!

Regards,

Bill Thoen
GISnet
http://gisnet.com
303-786-9961

On Oct 5, 2011, at 2:29 PM, Bill Thoen <bthoen at gisnet.com> wrote:

> Thanks, Peter!
> 
> The reference to http://www.postgresql.org/docs/8.4/static/install-upgrading.html  looks like just what I needed. Now I think I can do this and not make a mess.
> 
> - Bill Thoen
> 
> On 10/5/2011 2:07 PM, Peter Hopfgartner wrote:
>> --------Bill Thoen<bthoen at gisnet.com>  wrote--------
>> 
>> Subject: Re: [ELGIS] Untangling pgdg postgresql90 from Centos and ELGIS
>> 
>> Date: 05.10.2011 18:23
>> 
>> 
>> 
>>> On 10/5/2011 6:17 AM, Peter Hopfgartner wrote:
>>>> On 10/05/2011 01:30 AM, Bill Thoen wrote:
>>>> Hi Bill
>>>>> Not knowing what I was doing several months ago I installed the
>>>>> pgdg90 repository, because I wanted to get version 9.0 of podstgres.
>>>>> It seemed to work; there were no problems. But now I've got all kinds
>>>>> of compatibility problems as I find out that elgis and pgdg90 really
>>>>> aren't compatible. So I 've got to do something about this now.
>>>> Is there any particular feature in PostgreSQL 9.0 you really need? Is
>>>> 8.4 an option for you?
>>>> (...)
>>> I can use 8.4 just fine. The only reason I tried the pgdg90 version was
>>> that it supported a newer version of postgres (and I thought then that
>>> newer is always better) and it seemed to work just fine. THee after a
>>> couple of upgrades problems developed with errors about missing
>>> dependencies, etc.
>>>>> Then it occurred to me that maybe I should ask for advice before
>>>>> doing any (more) damage. What do you all recommend I do to  get back
>>>>> to PostgreSQL 8.4 and make ELGIS the primary source for postgres
>>> again?
>>>>> Thanks in advance; I really appreciate all your help!
>>>> Otherwise it would save you some head aches not to rely on yet another
>>>> repo which has many packages in common with ELGIS. We are running a
>>>> discrete number of server with CentOS 5 and the postgresql84 packages
>>>> and they run without any flaws.
>>> Yes, that's what I'm asking about. How do I get back to using postgres
>>> 8.4 (which elgis supports), with all my data intact? What's the startegy?
>>> Right now, my thought is to make a full backup of the data using pg_dump
>>> with the -Fc option, then wipe postgres off the system using yum remove
>>> postgresql90, delete the pgdg09 repo and cache, then run yum install
>>> postgresql, using the elgis repo, and finally, restore my data with
>>> pg_restore. Is that the best way to do this? Or am I missing something
>>> important?
>> As said before, consider installing postgresql84 instead of postgresql, which is still at version 8.1. PostgreSQL 8.4 has a lot of nice features more then 8.1 and it seems to be faster in a large number of use cases.
>> 
>>> Could it be that this is one of those situations where the experts see
>>> the train wreck coming and don't want to have anything to do with it?
>>> ;-)
>> 
>> 
>> I hope you have still a running PostgreSQL installation. Then the standard upgrade procedure, as described in http://www.postgresql.org/docs/8.4/static/install-upgrading.html, should be also fine for downgrading.
>> 
>> 
>> 
>>> TIA,
>>> - Bill Thoen
>> 
>> 
>> Peter Hopfgartner
>> 
>> 
>> 
>> R3 GIS Srl - GmbH
>> 
>> http://www.r3-gis.com
>> 
>> 
>> 
> 
> _______________________________________________
> el mailing list
> el at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/el
>