Forum OpenACS Q&A: PostgreSQL from scratch

Collapse
1: PostgreSQL from scratch
Posted by Torben Brosten on 01/21/02 09:11 AM

I'm getting the following error on setting up plpgsql 

postgres@huey:~ > createlang plpgsql template1
createlang: cannot find the file '/usr/lib/pgsql/lib/plpgsql.so'


I find the file (I think properly) at:

/usr/local/pgsql/lib/plpgsql.so

I suspect that my previous postgreSQL installation failure has crept 
into this re-try.  What commands should I issue to erase *all* of a 
previous install when trying again?

I've tried "deluser postgres", and removing directories called pgsql (actually renaming them pgsql.old for history's sake). I'm using SuSE 6.4 (kernel 2.219) on an i686.

Thanks in advance,

Collapse
2: Response to PostgreSQL from scratch (response to 1)
Posted by Vinod Kurup on 01/21/02 04:18 PM
Hi Torben,

what does echo $LD_LIBRARY_PATH return? It should have /usr/local/pgsql/lib in it. This is usually set in the .bash_profile file in postgres's home directory.

Since, I've been installing and reinstalling frequently while working on the install guide, i've made some sparse notes on how to wipe an openacs4 pg installation. These are rough and very debian-specific, but if people think this is valuable, i'll put it together into a more formal document:

Stop any aolserver instances:
inittab:
  1. clearing out entries from /etc/inittab
  2. run /sbin/init q to re-read /etc/inittab
  3. killall -9 nsd to kill any running servers
daemontools
  1. stop daemontools - /etc/init.d/daemontools stop
  2. killall -9 nsd to kill any running servers
Stop postgresql
  1. /etc/init.d/postgresql stop
  2. update-rc.d -f postgresql remove (chkconfig for RH)
Remove vacuumdb from crontab (crontab -e)

Get rid of nsadmin and postgres users and files

  1. deluser --remove-all-files nsadmin
  2. deluser --remove-all-files postgres
Get rid of web group
  1. groupdel web
Get rid of extra files:
  1. rm -r /usr/local/pgsql (relkov.so from OpenFTS still in there)
  2. rm /etc/init.d/postgresql
  3. rm -r /usr/local/aolserver /web /usr/lib/tcl8.3/nsfts-0.1
  4. rm -r /usr/local/bin/restart-aolserver /usr/local/bin/svgroup /usr/local/src/root /usr/local/src/postgresql
  5. rm -r /service/
get rid of daemontools:
  1. apt-get --purge remove daemontools
Collapse
3: Response to PostgreSQL from scratch (response to 1)
Posted by Torben Brosten on 01/21/02 11:15 PM
Hi Vinod,

You ask: what does echo $LD_LIBRARY_PATH return?

I made a mistake on the previous bash_profile edit of typing "lib" instead of "local" in the path. Oddly, both paths are present on the postgres user even after a userdel, mv user files, and useradd with new bash_profile.

Your instructions seem thorough. I'll give them a try and post the results in this thread.

Thank you! -Torben.

Collapse
4: Response to PostgreSQL from scratch (response to 1)
Posted by Torben Brosten on 01/26/02 01:57 PM
Hi Vinod,

I used your suggestions (with some variations for SuSE6.4). After 3 re-installs of postgreSQL, I'm still getting the same problem of: "plpgsql.so" not found, and having the file located at: /usr/local/pgsql/lib/plpgsql.so

postgres@huey:~ > echo $LD_LIBRARY_PATH

returns:

:/usr/local/pgsql/lib

Could this be a SuSE variation? Any suggestions on where to trackdown root cause?

Collapse
5: Response to PostgreSQL from scratch (response to 1)
Posted by Torben Brosten on 01/26/02 03:19 PM
[For the record, I'm installing OpenACS 4.x]

Well, this is getting interesting. There are two plpgsql.so files.

1.An "old" file from a general SuSE package install. The path is /usr/lib/pgsql/lib/plpgsql.so. I just discovered this one. I had renamed it instead of removing it when originally attempting a fresh re-install.

The other plpgsql.so is a "new" file from an OACS install. The path is /usr/local/pgsql/lib/plpgsql.so

Apparently the new postgresql install is using the old plpgsql.so instead of the new plpgsql.so --I assume the files perform the same function.

So, I mv'd the "old" plpgsql.so and associated file set back to /usr/lib/pgsql and tried (again, in the same postgres login session as the last attempt):

postgres@huey:~ > createlang plpgsql template1

results:

ERROR:  Load of file /usr/lib/pgsql/lib/plpgsql.so failed: /usr/lib/pgsql/lib/plpgsql.so: undefined symbol: CurrentTriggerData
createlang: language installation failed

Should I:

1. re-install the SuSE postgres package (and try again --assuming I erased some other critical package info),

2. search for an OACS configuration parameter that's referring to the old plpgsql.so,

3. do something completely different? I see a couple of older threads involving plpgsql.so and earlier OACS versions, but I'm not sure of the extent the info is relevant to this current OACS version, or if it is consistent with this situation.

Collapse
6: Response to PostgreSQL from scratch (response to 1)
Posted by Don Baccus on 01/26/02 05:19 PM
The easiest thing to do is to get rid of the SUSE-installed package (/usr/lib/pgsql/*, /usr/bin/pgsql/*, /usr/doc/pgsql/*) and grab the source tarball for PG 7.1.3 from the PG ftp server.

All you need to do is "./configure", "make", then (as root) "make install" and it will be installed in /usr/local/pgsql.

If libraries aren't being found, adding the new library directory to /etc/ld.so.conf and running /sbin/ldconfig (as root) will make them findable.

Looking at your original post, it looks like "createlang" was installed in /usr/bin/pgsql, i.e. from the RPMs but the library didn't make it.  And your reinstall (from source?) put the library elsewhere, i.e. /usr/local/pgsql/lib.  So the createlang you were running was looking in the wrong (RPM-install) place.

The PG folks source builds have forever put the entire product into a single directory subtree, /usr/local/pgsql (by default, you can change this, useful for running multiple versions).

The file system standard followed by many linux distros is to put binaries under /usr/bin, libraries under /usr/lib, i.e. scatter a product's pieces rather than gather then in one place.

Which is better?  Who cares, they're different, that's what's important.  A mix-n-match installation is hard to get right because of this.

Thus my advice - strip out the scattered /usr/bin, /usr/lib etc PG pieces and start over.  I always install "the PG way" because distros are always behind the times and until recently PG betas were so much better than previous releases that I'd use them rather than the "official" release.  PG's matured to the point where that's not so true (I'm still running PG 7.1 rather than PG 7.2rc1) but if you ever do want to upgrade from the PG site, it's a lot easier if you've installed it "their way".

Collapse
7: Response to PostgreSQL from scratch (response to 1)
Posted by Jon Griffin on 01/26/02 05:36 PM
Actually you should blow it away and install in /opt/pgsql. /usr/local source should only be used on systems where the program is used locally only. Hence it is technically correct according to FHS but in my discussions on the FHS list, /opt/pgsql is more correct if any of the programs will be shared.

Also, there are three possible places to put the PG system data: /pgsql/sydata/data, /srv/dbdata/pgsql, or /opt/mysystem/pgsql/data (which would be where to put it for a binary distro for sure). I am not sure if PG can create seperate data files per tablespaces as does Oracle, but if it does, each users data should be in thier home directory.

Collapse
8: Response to PostgreSQL from scratch (response to 1)
Posted by Torben Brosten on 01/26/02 11:53 PM
Thank you Don and Jon,

I'm understanding that a prior package install is interfering with the source install (I'm following the OACS4.x documentation). I'll purge it and try again.

cheers,