Question about Postgres

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
cluster.

b) how do I poke around

psql -l from the command line to list databases:
john@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@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

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.

# 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@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-11.0.4.25-1

Files that seem important:
/var/opt/scalix/mm/postgres/data

[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

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

On 26 Jul, 18:51, NetComrade <netcomradeNS...@bookexchange.net> 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

[snip]

We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc)
remove NSPAM to email

Contact me offline. I may be able to give you a few pointers.

HTH

-g

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&amp;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

NetComrade wrote:

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

NetComrade wrote:

-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"?

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

Check /var/opt/scalix/mm/postgres/data/postgresql.conf for the entries
unix_socket_directory
unix_socket_group
unix_socket_permissions

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
postgresql.conf".

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

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
autovacuum.

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)

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

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

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

[root@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@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-11.0.4.25-1

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@mt-adm data]# cat pg_hba.conf|grep -v \#
host scalix scalix 10.0.1.201/32 md5
local all all ident sameuser

That means that the scalix user can connect to the scalix database
from 10.0.1.201 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'

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)

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
this.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

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.

http://www.postgresql.org/community/lists/

Paul...