Trying out PostgreSQL/Postgres ( I'm a common MySQL User ) - recommend any references, any gotchas?

Question

I'm not really looking for a basic SQL guide but just something specific to PostgreSQL.

And I do run my own servers so getting the latest version ( 8.2 I believe? ) is no issue.

Answer 1

The PostgreSQL wiki is a great resource. Specifically, check out the optimization page:

http://wiki.postgresql.org/wiki/Performance_Optimization

Which links to this great article that I've referred to many times:

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Answer 2

Important things:

1. If you can (as since you're starting, and own a server - you can) use the latest released version (8.4 at the moment)
2. Make sure autovacuum is turned on in postgresql.conf
3. If you have problems - usually the fastest way to get help is to join #postgresql on freenode.net IRC network and ask there
4. There are some things MySQL has, and PostgreSQL doesn't (REPLACE for example) - usually if something is not in PostgreSQL there are good reasons for it and/or a workarounds.
5. Try to use psql client. You might feel better with pgadmin/phppgadmin, but when searching for help (especially on irc) knowing psql really helps.
6. You might want to subscribe to RSSes of one of 2 postgresql planets (don't ask why there are 2 of them :) ) : planet.postgresql.org and planetpostgresql.org
7. Also subscribing to postgresql related lists can help - you can find list of them in here