upgrading postgresql
 

I'm trying to upgrade postgresql.
I have mandrake 9.0 which came with pgsql7.2.2
I've downloaded, unzipped, compiled and installed postgresql 7.3.3
I can run it from /usr/local/pgsql/bin

I even compiled PHP to use it.
My only problem is that the old version 7.2.2 runs on boot.
I followed the instructions on installing at postgresql.org but I found nothing to help me switch which version runs at boot.

my postgres user's home directory is still /var/lib/pgsql

Where have I gone wrong and what can I do to fix this?

I think my problem is similar. Under RH 8, I upgraded PGSQL 7.2.2 to 7.3.2. It has been more difficult to get into it ever since. Sometimes it appears that it may have started under Server|Services. How can I reset that to refer to the proper partition?
Since a disorderly shutdown last night (caused by something else entirely) I can't get into PGSQL at all, and when I try, all command line functions slow down. Sometimes I have to leave the computer for several minutes even to log out.
From upgrade until last night, I have been getting in by using pg_ctl -D /var/lib/pgsql/data start.

PostgreSQL after update
 

I finally copied all my data to another directory, not having succeeded at that time in using pg_dump/pg_dumpall, and reinstalled. It works, but I have no access to existing data (in ../data/base). I had backed everything up to text files using COPY so still have the data but have to go through rebuilding all the tables before I can use anything. Is there a better solution, besides using pg_dump next time (which I subsequently found out how to do)?

Responding directly to one of Pete's questions, since I got a version conflict when trying to use pg_dump, I did a find on pg_dump and compared dates. Then I copied the newer version of all pg_* files over the older ones.

I had the exact same problem because my RH 8 came with 7.2 and I went to 7.3 so I could use PGADMINIII on my winXP box.

I found the problem for me was that RedHat uses the old style installation directories for Postgres and when you download the new version of Postgres from the postgres website, it installs in a different directory set. The solution for me was to manually erase the old postgres setup. This was not as hard as I thought it would be because the directory structure for the old postgres is well-documented.

Basically I did something like this (warning**going from memory here):

The new style use /usr/local/pgsql/data as the data directory
The old style used /var/lib/pgsql/data

So I erased the /var/lib/pgsql/data folder along with /var/lib/pgsql folder.

RedHat will still execute the old binaries though because the default location for a RedHat postgres install is

/usr/bin

while the "website download" version of postgres installs its binaries in

/usr/local/pgsql/bin

To fix this, I

ls -l /usr/local/pgsql/bin

to get a complete list of the binaries that comes with postgres. Then I went to the /usr/bin directory and erased each program(VERY CAREFULLY) that matched up.

In other words, because RH installs those binaries in one folder and the "downloaded" postgres in another, you literally have two copies of everything. So why does the old version run? Probably because /usr/bin is in your search path and /usr/local/pgsql/bin is not. So if you just type in pg_ctl its going to hit the old one in /usr/bin.

Once this is done, I set up the variables in my /etc/profile to ensure the correct version of postgres was being used. For me these were:

PGDATA = /var/local/pgsql/data
PGLOG = /var/log/pgsql
PGPORT = 5432
PGHOST = "localhost"

and added the new postgres binaries to my search path by

pathmunge /usr/local/pgsql/bin

in the same file.

By the way, before you destroy the old data directory

/var/lib/pgsql/data

make sure you backup all your database files.

pg_dumpall > outputfile

Also, do not assume my directory paths are correct. I posted that answer from memory.

I deleted all the old stuff and reloaded 7.3.3 (tried 7.3.4 first but had a lib problem). Now it starts automatically and runs but when I try pg_dump or pg_dumpall from
/usr/local/pgsql/bin/pg_dump
I get
error while loading shared libraries: libpq.so.3:
cannot open shared object file: No such file or directory
Searching for libpq.so.3 (the same one that kept me from installing 7.3.4):
/usr/bin/libpq.so.3
/usr/local/bin/libpq.so.3
/usr/local/pgsql/bin/libpq.so.3
/usr/local/pgsql/lib/libpq.so.3
I can now only back up by using COPY to a text file, which leads to problems when reinstalling, if that becomes necessary (no schema, for one thing).

jdtiede - you might try this:

in the file:

/etc/ld.so.conf

you may need to add:

/usr/local/pgsql/lib/libpq.so.3

then run /sbin/ldconfig

Thanks. I did try adding to /35c/ld.so.conf and running /sbin/lcdonfig. But before I did that I rebooted because I could no longer log on as my self; only as postgres and root. I added a new user and deleted myself (keeping my /home dir for the new user) and had the same problem. Pgsql didn't come back up automatically, but I ran postgres -D /usr/local/pgsql/date start and that did it. But if I log on as postgres and try su, it seems to hang up on password, yet if I deliberately enter a wrong password it comes back in a reasonable time (2 sec., maybe) with "incorrect password."

Is it possible a non-postgres binary got erased?

Were you having these problems before attepting to remove old postgres and install new postgres?

Is the /etc/passwd file in good shape and proper format?

I am not sure what the process RH goes through when trying to validate root or another user. Is there someone else in the forum that can help with this?

I do not think this last part is a postgres problem, but I am defnitely not good at diagnosis "non-postgres" problems.

I was locked out of my home directory for a while, which I think was result of trying to make changes to postgres, but I ran chmod 777 /home and that fixed it.

The real question now is where does postgres expect libpq.so.3 to be? If I knew that, wouldn't I be able to back up properly?

On my system, it is in:

/usr/lib/libpq.so.3

Here is a link to download the postgresql client shared libraries. At the bottom of the list, you will also notice they have documented where the libraires are stored by default. There is also some documentation on why you need the library in the first place.

http://ajax.ifsic.univ-rennes1.fr/./...3-12.i386.html