pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
DB novice questions 3+ messages / 2 participants [nested] [flat]
* DB novice questions @ 2008-10-10 17:37 Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> 0 siblings, 1 reply; 3+ messages in thread From: Isabella Ghiurea @ 2008-10-10 17:37 UTC (permalink / raw) To: pgsql-admin Hello Gurus, I'm new to Postgress world and before start creating my first database for test. I have some questions, beware I have experience with other RDBMS. 1- when issue create db , is there a option to specify the database page size ? what's the default ? ( don't see any comments in docs) 2-any basic tips for cfg to achieve some reliable database I/O performance , my first toughts are to create at least 2 additional table spaces: one for indexes and one for data. when creating the db , how can I specify a separate table space for :s ystem catalog , data and indexes ?( any special syntax , can't find to many examples in PG docs) 3- what is the best practice to use ad owner for a db , if I create the db as user: postgress , any implication for not using this user as owner of all db's ? thank you Isabella ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DB novice questions @ 2008-10-10 19:19 Kevin Grittner <Kevin.Grittner@wicourts.gov> parent: Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> 0 siblings, 1 reply; 3+ messages in thread From: Kevin Grittner @ 2008-10-10 19:19 UTC (permalink / raw) To: isabella.ghiurea@nrc-cnrc.gc.ca; pgsql-admin >>> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> wrote: > 1- when issue create db , is there a option to specify the database > page size ? what's the default ? ( don't see any comments in docs) Page size is set at compile time. The default is 8kB. You probably don't need to fuss with this, as large data is automatically moved out of the base row into a "TOAST" table on an as-needed basis, row by row. Feel free to define, for example, varchar columns larger than the page size. > 2-any basic tips for cfg to achieve some reliable database I/O > performance , my first toughts are to create at least 2 additional > table spaces: one for indexes and one for data. > when creating the db , how can I specify a separate table space for :s > ystem catalog , data and indexes ?( any special syntax , can't find to > many examples in PG docs) You may not need to define additional table spaces, especially if you have (as you really should) a good battery-backed RAID controller. Throwing everything into one big RAID and letting things take their course across all those spindles often out-performs the most careful hand-tuning. You will need to adjust some configuration options for best performance on a reasonable machine -- like most products, the PostgreSQL defaults are designed to let it at least start up on an old desktop workstation someone might want to use to "try things out". The most critical settings to look at, described pretty well in the documentation, are: listen_addresses max_connections shared_buffers work_mem maintenance_work_mem checkpoint_segments effective_cache_size If you get those set right, you're well on your way. > 3- what is the best practice to use ad owner for a db , if I create > the db as user: postgress , any implication for not using this user > as owner of all db's ? That depends on your environment. In ours, we routinely do something like: create user xxxowner with password 'asdf'; create user xxx with password 'asdf'; create user viewer with password 'asdf'; create database xxx with owner xxxowner; \c xxx revoke create on database xxx from public; revoke create on schema public from public; grant create on schema public to xxxowner; Then we have xxxowner create all database objects and grant appropriate rights to other users. It's best to use the database superuser only for tasks which require that. In our shop we don't allow remote access by the superuser, you have to ssh to the box, sudo su to the superuser OS login, and then connect. See the documentation for pg_hba.conf to see how to manage access. I hope this was helpful. -Kevin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: DB novice questions @ 2008-10-10 19:48 Kevin Grittner <Kevin.Grittner@wicourts.gov> parent: Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 0 replies; 3+ messages in thread From: Kevin Grittner @ 2008-10-10 19:48 UTC (permalink / raw) To: isabella.ghiurea@nrc-cnrc.gc.ca; +Cc: pgsql-admin Please keep responses on the list; others may be able to help. >>> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> wrote: > you mentioned about this PG cfg parameters , will the Postgress.org Docs > provide > good example for setting this values in RH 5 -x86 -64 bits OS ? I don't think many of these are OS-specific. http://www.postgresql.org/docs/8.3/interactive/runtime-config.html -Kevin ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-10-10 19:48 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-10-10 17:37 DB novice questions Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> 2008-10-10 19:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov> 2008-10-10 19:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox