Setting up Postgresql on Linux

On Mon, 1 Mar 2004, phil campaigne wrote:

Nigel J. Andrews wrote:

On Mon, 1 Mar 2004, Phil Campaigne wrote:

Hello,
I originally installed postgresql as root user and now I am setting up a
development environment with cvs and a java ide and tomcat. I have
everything with the exception of postgresql integreted using a non-root
user.
THe process I am using is to logon as postges and start the database
and do queries from the command line using psql. Then I logoff and logon
as phil and start tomcat and my java ide.

I'm a little confused as to what you are trying to accomplish.

1.Is there a better way to start the database and the web application?

Can't you just start postgresql and tomcat?

2. During setup of postgresql I chown and ghgrp the /usr/local/pgsql
directoy tree to postgres?

A little strange but perfectly acceptable if that's how you want it; I install
all software under /usr/local as a special software manager, although I'm not
sure if I'd bother chown-ing an existing directory tree to non-root.

3.However my development environment(i.e. jdbc connection from my
application) doesn't seem to have permission to access to all the files
it needs in postgres?

What's the error message? I'm not a jdbc person but I suspect from the
preceding comments that you're not running the postmaster or jdbc requires a
tcp connection and you haven't configured postgresql to allow that.

4. Is is best to install postgresql as user "phil" in order to gain
access for my integrated develoment environment?

You can install postgresql as whatever user you want, all it requires is that
it runs as a non-root user, and has the proper ownership etc on it's data
directory.

First, I would do a: ps axu | grep post
and see if the postmaster is running. If not nad this is not supposed to run as
a normal service use:

pg_ctl -l /path/to/a/logfile \
-D /path/to/data/directory/initialised/with/initdb \
start

Start Tomcat. If after doing whatever to setup and use jdbc to access your
database (for testing you may want to createdb testdb) you still don't get any
connection set tcpip_socket to true in postgres.conf in the data directory and
stop and start postgresql using pg_ctl. Also look in the Tomcat logs and in
/path/to/a/logfile. While editing postgres.conf you may also want to look at
the log_ settings and enable a few to see what is happening in more detail.

The postgresql documentation is at http://www.postgresql.org/doc/ and
unfortunately I can't be more specific with urls since the domain falls into to
large IP block I block completely due to the excessive number of speculative
probes of my systems (not postgresql.org addresses I hasten to add). In deed,
this email will probably not reach the list, I wish I know what causes the
switch from that working to not working.

Hi Nigel,
I have had a devil oaf a time with file permissions. I don't think I
know how to set them up for application that have been installed by root
user. Here's what I have done to make things work for user postgres:
change in /usr/local/pgsql/data/postgresql.conf
#tcpip_socket = true

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH

PATH=/usr/local/pgsql/bin:$PATH
export PATH

touch /tmp/postgresql.log
set permissions on /tmp/postgresql.log to postgres

mkdir /usr/local/pgsql/data
chown -R postgres:postgres /usr/local/pgsql/data
chown -R postgres:postgres /usr/local/pgsql/lib
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

pg_ctl start -D /usr/local/pgsql/data -o "-i" -l /tmp/postgresql.log

/usr/local/pgsql/bin/createdb -h localhost -U postgres hardwoodthunder

psql -h localhost -U postgres hardwoodthunder

Now if I try to run my web app from user phil in my ide using tomcat I
think I am getting file permission errors.

Phil, backtrack a bit...you can't edit postgres.conf until you've done an
initdb because initdb initialises the data directory, including postgres.conf,
pg_hba.conf and all the other files associated with a postgresql database
cluster.

So starting from the beginning:

su to your desired user for postgresql, ie. postgres
Don't bother chowning anything.
Don't create a data directory; rename /usr/local/pgsql/data to something (just
in case it is wanted later however unlikely that seems).
_Do_ run: initdb -D /usr/local/pgsql/data
_Do_ run pg_ctl to start the server but don't use the -o '-i' switch.
_Do_ run createuser to create a database user of the same name as the user
running the Tomcat etc. processes (user phil?)
_Do_ run createdb -O <user from above step> hardwoodthunder

Return/su to the Tomcat etc. user and try accessing the database with:
psql hardwoodthunder

Create schema, insert data, whatever.

Use you IDE, Tomcat etc.

If Tomcat etc. can't make a database connection, and you should see something
in it's log if it can't, then go and edit /usr/local/pgsql/data/postgres.conf
to set tcpip_socket to true, stop and start postgresql as postgres using pg_ctl
as above.

If you have any doubts about the database connectivity, turn on connection and
statement logging in postgres.conf and you will see in /tmp/postgresql.log a)
when a client connects to a database in the cluster and b) what statements it
issues to the engine.

Also, it's practice on these lists to reply to all, including the list, when
responding to a message. Bearing in mind my emails currently don't propagate to
the postgresql.org domain could you please forward this response to the
-general list so other's can see and contribute as appropiate. Thanks.

--
Nigel Andrews