Re: [GENERAL] 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. http://www.postgresql.org/community/lists/ Paul... ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Question about Postgres
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: [EMAIL PROTECTED] 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. :) [EMAIL PROTECTED] 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. :) [EMAIL PROTECTED] 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 [EMAIL PROTECTED] data]# cat pg_hba.conf|grep -v \# hostscalix scalix 10.0.1.201/32 md5 local allall 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
[GENERAL] Question about Postgres
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 b) how do I poke around 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: [EMAIL PROTECTED] 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 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) Should I be 'playing' with /var/opt/scalix/mm/postgres/data/postgresql.conf ? [EMAIL PROTECTED] 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) [EMAIL PROTECTED] 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 [EMAIL PROTECTED] data]# cat pg_hba.conf|grep -v \# hostscalix scalix 10.0.1.201/32 md5 local allall ident sameuser 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) ... We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc) remove NSPAM to email ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Question about Postgres
On 26 Jul, 18:51, NetComrade <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 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: [EMAIL PROTECTED]:~$ 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: > [EMAIL PROTECTED] 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. > [EMAIL PROTECTED] 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) > > [EMAIL PROTECTED] 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 > > > [EMAIL PROTECTED] data]# cat pg_hba.conf|grep -v \# > hostscalix scalix 10.0.1.201/32 md5 > local allall 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' >
