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