Updating the PostgreSQL Admin Cookbook

I'm planning a new edition of the PostgreSQL Admin Cookbook
http://www.2ndQuadrant.com/en/books/

The 1st Edition covered PostgreSQL without regard to the client
environment, meaning it didn't have much in there about Ruby on Rails.
Obviously, that's a shame and I'd like to improve things next time
around.

If we have 5-10 pages aimed directly at Ruby on Rails users, what
topics/hints/tips do you think I should cover? What confused you the
first time? What aspects aren't as obvious as they should be? What
still annoys you?!? Would it be worth having a "Rails edition" of the
book? Other ideas?

Thanks for your help.

I see a few elements that are somewhat postgresql - Rails specific

and may be relevant ?? Not sure how relevant, just my 2c.

  • explain the difference between connecting via

tcp/ip (host: localhost in the database.yml or via

socket) : it changes the authentication mechanism

(ref local => ident vs. host => md5). Maybe you

already explain the pg_hba.conf and you refer to

it from a Rails config/database.yml section.

  • the usage of “port: 5433” (on Ubuntu, when

2 postgresql versions are installes in parallel (e.g.

8.3 and 8.4 on 10.04), they use ports 5432 and port

5433 but you need to know that and set the port to

5433 for the database.yml.

  • using schema’s in postgresql seems non-trivial

to match with Rails (I have not used schema’s recently,

so I am not aware of recent solutions).

  • Default Rails migrations do not generate foreign

key constraints. Explain when that would make

sense (not necessarily always) and if so, what

are proper ways to implement them (there exist

gems to do it properly).

  • similar on indices and uniq indices. You could

explain when they do make sense (certrainly

not always) and if so, what are proper ways to

implement them in Rails. And how to properly

catch a uniqueness validation exception thrown

by the database in the Rails uniqueness

validator.

  • you might discuss Sequel and DataMapper

as alternatives ?

HTH,

Peter

Rails is pretty database agnostic, so the subjects needed are either
at the very basic set up stages, or very situation specific when using
customized SQL. Most of the normal everyday needs will be handled by
rails itself.

basic:
- How to start a new project and use postgresql by passing the -d flag
to the rails new command
- How to switch an existing project to use postgres
- How to use sqlite in development and test and postgres in
production
- The difference between using trust, ident and md5 in the pg_hba.conf
file
- How to use encrypted passwords in the database.yml file
more advanced:
- A new Rails project and an existing postgres database: How to set up
rails models for a legacy database
- Several examples of find by SQL using custom SQL for postgres
- Using rake to migrate data and tune a postgresql database

Actually, you really need to start with which gem to use and how to get it to build... That took me a while to figure out. Googling got tons of blogs that were either out of date, or based on non-standard install locations of Postgres with half-baked instructions on building. (I'm on OS X, so there's a lot of people who make the mistake, IMHO, of using some package manager rather than just building from source.)

Without wishing to ignite flames, why would you want to do that? What
advantage is there in developing on sqlite if you aren't going to
deploy with it? Surely its best practice to use similar environments?

Thanks for your other comments.