Skip site navigation (1) Skip section navigation (2)

Re: Question about Postgres

From: John Koller <johnckoller(at)yahoo(dot)com>
To: pgsql-admin(at)postgresql(dot)org(dot)pgsql-general(at)postgresql(dot)org
Subject: Re: Question about Postgres
Date: 2007-07-27 02:02:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-general
NetComrade wrote:

> I apologize for cross-posting, but I need some help w/o too many
> advices RTFM :). After Oracle and MySQL, this becomes the third
> product that I need to learn to some degree, and I need a few links
> which would provide a 'quick tutorial' especially for folks with
> Oracle background like myself. Last time I had to deal with MySql it
> took me a few days just to figure out how to login, and then how to
> poke around, and then a few more to finally start writing some useful
> code in whatever language they use that's similar to PL/SQL.
> We are running a mail server, which for whatever stupid reason uses a
> database (stupid, b/c it only uses it for web access, mail is actually
> on the file system)
> 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

> b) how do I poke around

psql -l from the command line to list databases:
john(at)mirror:~$ psql -l
        List of databases
   Name    |  Owner   | Encoding
 amarok    | john     | UTF8
 john      | john     | UTF8
 postgres  | postgres | SQL_ASCII
 template0 | postgres | SQL_ASCII
 template1 | postgres | SQL_ASCII
(5 rows)

\? for help
\dS to list system tables
\dt to list tables
\d table_name to describe a table

amarok=> \d pg_tables
    View "pg_catalog.pg_tables"
   Column    |  Type   | Modifiers
 schemaname  | name    |
 tablename   | name    |
 tableowner  | name    |
 tablespace  | name    |
 hasindexes  | boolean |
 hasrules    | boolean |
 hastriggers | boolean |
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename,
pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace",
c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0
AS hastriggers
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
  WHERE c.relkind = 'r'::"char";

> 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(at)mt-adm httpd]# vacuumdb -a -f -z -q -h -p5733
> vacuumdb: could not connect to database template1: FATAL:  no
> pg_hba.conf entry for host "", 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 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

> Some 'details' on the server: (ps -ef)
> 00:00:04 /usr/bin/postmaster -i -h -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(at)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

> # there are no more than 20-30 users on the server at any given time
> # the disks are fast (50megs/sec, RAID10, SCSI)
> # memory is big 8g
> # cpu count is 2 with hyperthreading (it's a dell 2650)
> [root(at)mt-adm data]# rpm -qa|grep post
> postgresql-libs-7.4.17-1.RHEL4.1
> postgresql-server-7.4.17-1.RHEL4.1
> postgresql-7.4.17-1.RHEL4.1
> scalix-postgres-
> Files that seem important:
> /var/opt/scalix/mm/postgres/data
> [root(at)mt-adm data]# cat pg_hba.conf|grep -v \#
> host    scalix      scalix   md5
> local  all    all             ident   sameuser

Is this from /var/opt/scalix/mm/postgres/data/pg_hba.conf or

> postgresql.conf (comments taken out)
> max_connections = 100
> shared_buffers = 1000
> # bunch of locale params en_US.UTF-8'
> 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)

In response to


pgsql-admin by date

Next:From: adeyDate: 2007-07-27 02:41:12
Subject: Re: No create table
Previous:From: NetComradeDate: 2007-07-26 17:51:31
Subject: Question about Postgres

pgsql-general by date

Next:From: Luca FerrariDate: 2007-07-27 07:20:57
Subject: Re: a few questions (and doubts) about xid
Previous:From: StuartDate: 2007-07-27 00:57:27
Subject: generating part of composite key

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group