Re: Question about Postgres

On Thu, 26 Jul 2007 21:02:58 -0500, John Koller
<johnckoller@yahoo.com> wrote:

>NetComrade wrote:
>
>> I'd like to know a couple of things
>> a) how do I access this thing as a DBA to poke around
>
>The command line client is psql. Since psql defaults to port 5432 and you do
>appear to have two clusters running use psql -p 5733 to access the other
>cluster.
>
>> b) how do I poke around
>
>psql -l from the command line to list databases:

[root@mt-adm bin]# su - postgres
-bash-3.00$ psql -l
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(2 rows)

-bash-3.00$ export PGPORT=5733
-bash-3.00$ psql -l
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5733"?

Interestingly this only works on the default install, but not the
other 'cluster'
>
>
>> c) do I need to make any modifications to config file
>> d) what is the most common 'default' values that need to be changed
>>      what's the best way to see what a performance bottleneck is (i
>> e) why this doesn't work:
>> [root@mt-adm httpd]# vacuumdb -a -f -z -q -h 10.0.1.93 -p5733
>> vacuumdb: could not connect to database template1: FATAL:  no
>> pg_hba.conf entry for host "10.0.1.93", user "root", database
>> "template1", SSL off
>
>pg_hba.conf controls who can connect to which database.
>
>If the two lines below are your pg_hba.conf file then only the scalix user
>can connect to the scalix database from 10.0.1.201 using a password. Nobody
>else can use tcp/ip connections.
>
>Try vacuumdb -avz as the postgres user and
>vacuumdb -avz -p5733 as whatever user the other cluster is running as


-bash-3.00$ vacuumdb -avz -p5733
vacuumdb: could not connect to database template1: could not connect
to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5733"?

What are my next steps :)?
>
>> Some 'details' on the server: (ps -ef)
>> 00:00:04 /usr/bin/postmaster -i -h mt-adm.mentora.biz -p 5733 -D
>> /var/opt/scalix/mm/postgres/data -k /var/opt/scalix/mm/postgres/data
>> 00:00:00 postgres: stats buffer process
>> 00:00:00 postgres: stats collector process
>> 00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
>> 00:00:00 postgres: stats buffer process
>> 00:00:00 postgres: stats collector process
>>
>> The above is confusing.. do I have 2 instances (or databases) running
>> on different ports? Should I shutdown the default one? (Scalix is the
>> product that uses the db)
>
>That does look like 2 clusters running. Use -p on the command line or set
>PGPORT to get to the one on 5733. Try listing the databases for each
>cluster with psql -l. If the /var/lib/pgsql/data cluster does not have any
>databases you may not need it.
>
>> Should I be 'playing' with
>> /var/opt/scalix/mm/postgres/data/postgresql.conf ?
>
>Yes, each cluster will have its own set of config files.
>
>> [root@mt-adm httpd]# du -skh /var/opt/scalix/mm/postgres/data
>> 276M    /var/opt/scalix/mm/postgres/data
>>
>> # "database" is rather small
>
>If all of your attempts to vacuum are failing then some of that will be
>bloat.

>> [root@mt-adm data]# cat pg_hba.conf|grep -v \#
>> host    scalix      scalix      10.0.1.201/32   md5
>> local  all    all             ident   sameuser
>
>Is this from /var/opt/scalix/mm/postgres/data/pg_hba.conf or
>/var/lib/pgsql/data/pg_hba.conf

Both are from scalix directory

>> postgresql.conf (comments taken out)
>> max_connections = 100
>> shared_buffers = 1000
>> # bunch of locale params en_US.UTF-8'
>>
>> http://www.scalix.com/forums/viewtopic.php?t=7809&highlight=sharedbuffers
>>
>> There are some suggestions here, but they seem rather high (I did look
>> at some of them ,and the majorify make sense tweaking, I don't think I
>> fully understood the vacuum paramter)
>
>http://www.postgresql.org/docs/7.4/interactive/tutorial.html
>

Thanks for your kind reply.. I'll check out the tutorial over the
weekend.
.......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
remove NSPAM to email