PostGRES (Pete's notes)

Postgresql home page

As of 2006-06-01, I use the postgresql that David Michell installed on netserver. Notes about how to access in are in the Nagios Implementation Plan.

On 2006-08-03, I installed pgadmin on netserver. I edited the /etc/apt/sources.list file to add this:

# This is so Pete could install pgadmin3
deb ftp://ftp.us.postgresql.org/pub/mirrors/postgresql/pgadmin3/release/debian sarge pgadmin
...and the I did
apt-get update
apt-got install pgadmin3
What follows is rather obsolete.

Introduction

I need PostgreSQL because OpenNMS needs it. I thought I could get away with using PostgreSQL 7.0.2, which is installed as part of the SuSE 7.0 full install. Unfortunately, I couldn't find documentation for how to start the database. I did find file /etc/rc.d/postgres. When I ran it with "start" it said "done". I didn't check if a process was actually started. Later, after trying to install OpenNMS and failing because PostgreSQL wasn't running, I looked for the process and didn't find it. Doing a "start" failed this time. Perhaps OpenNMS caused the database process to die, and left files corrupted? Anyway, I gave up and tried a full install of 7.0.3 as described below.

Get PostgreSQL

PostgreSQL 7.0.2 is installed with SuSE 7.0. Before getting 7.0.3, use yast to delete pg_devel, pg_jdbc, pg_lib, pg_odbc, pg_perl, pg_pyth, pg_serv, pg_tcl, pg_test

Next, get PostgreSQL. Note that RPMs are available. When I tried to use the RPM for suse7, I got dependency problems, so I got the sources.

cd /usr/src
ftp gate.ucar.edu
anonymous@ftp.postgresql.org
* anonymous@ftp.crimelabs.net
siemsen@ucar.edu
cd pub/source/v7.0.3/ * cd pub/postgresql/v7.1 binary
get postgresql-7.0.3.tar.gz
* get postgresql-7.1.tar.gz
quit
gunzip postgresql-7.0.3.tar.gz
* gunzip postgresql-7.1.tar.gz
tar xf postgresql-7.0.3.tar
* tar xf postgresql-7.1.tar
rm postgresql-7.0.3.tar
* rm postgresql-7.1.tar

Install PostgreSQL

Follow the directions in the /usr/src/postgresql-7.0.3/INSTALL file. The file says to create a "PostgreSQL superuser account". The phrase is a little confusing: it does not mean to create an account with uid 0. SuSE already had a "postgres" account. It's home directory is /usr/local/pgsql. I set its shell to /bin/bash and copied siemsen's .bashrc, .bash_profile and .emacs files to the postgres account's home directory in case I ever want to login in as "postgres". Then I edited the .bashrc file to clean out crap.

I later found out that these are some good things to set in the .bashrc file:

pathadd /usr/local/pgsql/bin
manadd /usr/local/pgsql/man
export PGDATA=/usr/local/pgsql/data
export PGLIB=/usr/local/pgsql/lib

To get on with the install:

cd /usr/src/postgresql-7.0.3/src
./configure --with-perl --with-odbc --with-tcl
gmake
edit /etc/ld.so.conf and add this line:
/usr/local/pgsql/lib
and save the file /sbin/ldconfig
set the password on the postgres account
log in as postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Install the headers, libraries, man and HTML files:

cd /usr/src/postgresql-7.0.3
gmake install

To get access to the HTML pages, put a link to /usr/local/pgsql/doc into your web tree.

To get PostgreSQL to start at boot time:

cp /usr/src/postgresql-7.0.3/contrib/linux/postgres.init.sh /sbin/init.d/postgres

...and then edit the file. Delete all the lines from "# Source function library." to "# See how we were called". Set USE_SYSLOG to "no". Uncomment (activate) the line that sets PG_OPTS to "-i".

Note that the postmaster daemon logs to /tmp/postgres.log. It would be nice if it would log to /var/log/postgres.log, but it can't because it runs as user "postgres" and therefore can't write to /var/log. To reduce my confusion, create a link in /var/log so that I can find the damn log.

cd /var/log
ln -s /tmp/postgres.log postgres.log

Ok, so you've created the startup script, but you have to make a link for it to actually get executed at boot/shutdown.

/etc/rc.d/rc2.d
ln -s ../postgres S96postgres
ln -s ../postgres K09postgres

When later reading the documentation, I discovered that the postgres account should own all the PostgreSQL binaries, so I did:

cd /usr/local/pgsql
chown -R postgres *

Notes about documentation typos, for eventual reporting to docs@postgresql.org:

Run PostgreSQL

To run with OpenNMS, some PostgreSQL parameters should be tuned.

(as root)
xemacs /usr/local/pgsql/data/postmaster.opts
 Change the value of "N" from 32 to 64 to increase the maximum number of backend server processes.
Change the value of "B" from 64 to 128 to increase the number of shared-memory disk buffers
chown postgres.daemon /usr/local/pgsql/data/postmaster.opts*
Restart postmaster.

The user "postgres" is the database administrator. He can allow siemsen to have access to the database as follows:

su - postgres
createuser -d siemsen
logout
User "siemsen" can now use the database:
proteus$ createdb testdb
proteus$ psql testdb
testdb=> ^D
proteus$
Address comments or questions about this Web page to the Network Engineering & Technology Section at nets-www@ncar.ucar.edu. The NETS is part of the Scientific Computing Division of the National Center for Atmospheric Research, which is part of the University Corporation for Atmospheric Research.
Last modified: Thu Aug 3 17:37:36 MDT 2006