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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate