Updating the PostgreSQL Admin Cookbook

rubyonrails-talk
1

I'm planning a new edition of the PostgreSQL Admin Cookbook

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.

2

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

3

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

4

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.)

5

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.