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.


Simon R. http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sat, Feb 18, 2012 at 6:21 PM, Simon R. [email protected]
wrote:

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?

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


*** Available for a new project ***

Peter V.
http://twitter.com/peter_v
http://rails.vandenabeele.com
http://coderwall.com/peter_v

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

On Sun, Feb 19, 2012 at 2:37 PM, seanlynch [email protected]
wrote:

  • How to use sqlite in development and test and postgres in
    production

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.


Simon R. http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Feb 19, 2012, at 7:37 AM, seanlynch wrote:

basic:

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


Scott R.
[email protected]
http://www.elevated-dev.com/
(303) 722-0567 voice

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs