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

Re: [GENERAL] Question about Postgres

From: Decibel! <decibel(at)decibel(dot)org>
To: NetComrade <netcomrade(at)bookexchange(dot)net>
Cc: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Question about Postgres
Date: 2007-07-31 00:44:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-adminpgsql-general
Moving to -general.

On Jul 26, 2007, at 12:51 PM, 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.

First, let me mention that there's a number of companies that offer  
PostgreSQL training. If you find a "public class" the cost shouldn't  
be too bad; likely in the $1500 range for a few days. Coming from  
Oracle you might also want to look at EnterpriseDB, since we're  
Oracle compatible (disclosure: I work for EnterpriseDB).

For stored procs, take a look at plpgsql, which is similar to PL/SQL  
(except there's no packages). There's a section on it in the manual.

> a) how do I access this thing as a DBA to poke around

Connect to the database as a database user that has superuser  
privileges. Details vary depending on how the database was installed,  
but you can usually do that if you su into the OS account that's  
running the database. I recommend that you immediately create another  
superuser so that you're not doing stuff on the machine as the same  
user that's running the database.

> b) how do I poke around

Uhm... psql (far more useful than sqlplus, IMO)? Or maybe pgAdmin  
would be more to your liking.

> c) do I need to make any modifications to config file

You probably want to, yes... the default config is *very*  
conservative. At a minimum change shared_buffers and  
effective_cache_size, and enable autovacuum. Google for "annotated  

> 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

PostgreSQL relies heavily on OS monitor tools; so things like top,  
vmstat and cricket are your friends.

> 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

Because if you don't specify a database user to connect as the  
default is to connect as the OS user. There's no "root" user in  
PostgreSQL by default (and I recommend not adding one).

Some other comments:

Don't use vacuum full (the -f above) unless you really, really have  
to. Regular 'lazy' vacuuming is what you want. Better yet, use  

> 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)

Yes, you have two servers running; one on 5733 and one on the default  

> Should I be 'playing' with
> /var/opt/scalix/mm/postgres/data/postgresql.conf ?

If that's what scalix is using... yes. :)

> [root(at)mt-adm httpd]# du -skh /var/opt/scalix/mm/postgres/data
> 276M    /var/opt/scalix/mm/postgres/data
> # "database" is rather small
> # 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)

Wow, that's some serious horsepower for a 300MB database... or is the  
server doing other things?

BTW, saying RAID10 doesn't tell us much without mentioning how many  
drives you have. :)

> [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-

UGH. Please try and run a recent release... 7.4 is like 4 years old.

> 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

That means that the scalix user can connect to the scalix database  
from using password (md5) authentication. Any user on the  
local machine can connect via the filesystem socket using ident  
sameuser authentication... that means that if your username on the OS  
is "bob", you can connect to the database as database user "bob"  
without needing to enter a password.

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

I'm on a plane right now, so I can't fully comment. If you expect the  
database to stay fairly static in size, I'd set shared_buffers to  
62500 and effective_cache_size to the same. If you'll be growing  
larger than 500MB or so, I'd set effective_cache_size to 875000. If  
you move up to a current release, you could set shared_buffers much  
higher, but I wouldn't go much past 1GB (131072) in 7.4.

> remove NSPAM to email

If you want folks to help you, please don't throw up roadblocks like  
Decibel!, aka Jim Nasby                        decibel(at)decibel(dot)org
EnterpriseDB      512.569.9461 (cell)

In response to

pgsql-admin by date

Next:From: Decibel!Date: 2007-07-31 01:52:17
Subject: Re: Question about fixes and updates
Previous:From: adeyDate: 2007-07-30 22:35:25
Subject: Re: Lock Statistics

pgsql-general by date

Next:From: Decibel!Date: 2007-07-31 00:48:16
Subject: Re: PostgreSQL install problem
Previous:From: pcDate: 2007-07-31 00:41:32
Subject: Re: query to match '\N'

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