Thread: pg installation for dummies
I'm writing an article based on my experience setting up a ruby-on-rails server for a web apps class I'm teaching. My target audience is people who have a clue, but are not gurus at the wide collection of software necessary to get a server running. The in-work article is at http://www.schumeyer.com/content/view/16/29/ I have a section describing pg installation. I would like to be sure that I did not inadvertently do something stupid with my pg setup. So a couple of questions: 1) People often ask about the memory settings in postgresql.conf. My assumption is that if you are running small databases (like a class project) then you don't need to change these settings. But if you need to change them, then the question is, change them to what? Are these reasonable for a system with 2GB memory: shared_buffers = 5000 work_mem = 20000 maintenance_work_mem = 20000 I'm not looking for optimal settings. If someone want optimal, they should find someone who knows what they are doing to install pg. I'm just looking for "reasonable" settings. 2) I created accounts and databases for each student, where the database name and account name are the same. I did not realize that by default, all users can access all databases!? (Unless I did something stupid). I now believe that the "right" thing to do is this (in pg_hba.conf) # "local" is for Unix domain socket connections only local sameuser all md5 local all postgres md5 This seems to prevent a student from logging into anything other that his own database. Is this the best way? I certainly appreciate comments and feedback.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/20/07 10:27, Rick Schumeyer wrote: > I'm writing an article based on my experience setting up a ruby-on-rails > server for a web apps class I'm teaching. My target audience is people > who have a clue, > but are not gurus at the wide collection of software necessary to get a > server running. > > The in-work article is at http://www.schumeyer.com/content/view/16/29/ > > I have a section describing pg installation. I would like to be sure > that I did not inadvertently do something stupid with my pg setup. > So a couple of questions: > Why build from source, when the FC5 RPM is at: http://rpm.pbone.net/index.php3/stat/4/idpl/3906125/com/postgresql-server-8.2.3-1PGDG.i686.rpm.html If you're worried about library compatibility, that page also links to the SRPM. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGAAPCS9HxQb37XmcRAqvKAKDLKdlo4de02R+262EBcZMOF/n3WACg1H9o GUnhIYuoYy9gu3n8zzpBLE4= =bfBv -----END PGP SIGNATURE-----
Rick Schumeyer <rschumeyer@ieee.org> writes:
> 1) People often ask about the memory settings in postgresql.conf.
Aside from the ones you mentioned, checkpoint_segments is my favorite
bottleneck. It doesn't matter for a read-mostly database, but under
any sort of write-intensive load you really got to bump it up. Note
that this is a disk space tradeoff not a memory tradeoff.
> I now believe
> that the "right" thing to do is this (in pg_hba.conf)
> # "local" is for Unix domain socket connections only
> local sameuser all md5
> local all postgres md5
> This seems to prevent a student from logging into anything other that
> his own database. Is this the best way?
It's kinda hardwired --- if you want to grant specific exceptions to
let B use A's database, you end up editing pg_hba.conf a lot. As of
8.2 I think the best way is to use GRANT/REVOKE CONNECT ON DATABASE.
regards, tom lane