Ruby, ActiveRecord and Postgresql

Here are some tips on getting Postgresql and Ruby working together. This has not been as simple as I had hoped but after some experimentation, here are the steps that worked for me...

My environment:
RedHat Linux (Enterprise Linux ES Release 4 - Nahant Update 3)
Ruby 1.8.6
Postgres 8.3 (?)

1: Downloaded Postgresql as source and compiled as directed, installed into /usr/local/pgsql
NOTE that I had to be root to get make to successfully compile the code - don't know why

2: Install the Ruby postgres Gem
You will see references to the postgres, ruby-postgres and postgres-rb gems. As far as I can tell, the one you want is 'postgres'
As root run:
# gem install postgres
This may very well blow up with error messages! It did with me. For whatever reason it does not know where to find the Postgresql include and lib directories.
Look at the error message and cd to the gem install directory. For me this was:
/usr/local/lib/ruby/gems/1.8/gems/postgres-0.7.9.2008.01.28/ext
Install the gem manually using these commands:
# ruby extconf.rb --with-pgsql-include=/usr/local/pgsql/include --with-pgsql-lib=/usr/local/pgsql/lib
# make
# gem install postgres -- --with-pgsql-include-dir=/usr/local/pgsql/include --with-pgsql-lib-dir=/usr/local/pgsql/lib
NOTE that the options are different for the ruby extconf.rb and gem install commands! They look very similar but the gem install options have a '-dir' suffix.

3: Create a test database with 'psql'
You will want to follow a basic tutorial on Postgresql and the client 'psql' to learn about this. They are plentiful and easy to find online.
For this example, let's assume that the database is called 'test', the table is called 'employees' (plural) and that it has two fields called 'id' (int) and 'name' (varchar(255)).
Populate that with a few rows of data.

4: Write a ruby script to interact with the database using ActiveRecord
ActiveRecord is the way that Rails interacts with databases, but you can use it outside of Rails with no problem.
Here is one that will fetch the 'employee' records from a database on the same machine as the script:

#!/usr/bin/env ruby
require 'rubygems'
require 'active_record'

# create a class for employee records (the class is singular but the table is plural)
class Employee < ActiveRecord::Base
end

# connect to the database
ActiveRecord::Base.establish_connection(:adapter => 'postgresql',
                                        :host => 'localhost',
                                        :username => 'postgres',
                                        :database => 'test');

employees = Employee.find(:all)
employees.each do |employee|
   print "#{employee.id}   #{employee.name}\n"
end

NOTE the adapter is 'postgresql', not 'postgres', you don't need to explicitly require the 'postgres' gem, and the username is 'postgres'

To connect to a remote postgresql instance you will need to change the :host value in the establish_connection call. You may need to add the port that it is listening on (default 5432) with a :port directive.

You will also need to set up Postgres to allow remote calls. In /usr/local/pgsql/data/ you will need to edit pg_hba.conf to add a line allowing connections from a defined set of remote machines. Read the documentation to make sure you don't open up your database to the entire world.
Then you need to edit postgresql.conf and uncomment the 'listen_addresses' and 'port' lines. You want to add the IP address of the database server to the listen addresses so that the default line

listen_addresses = 'localhost'

becomes something like this

listen_addresses = 'localhost,192.168.2.100'

You'll need to restart Postgresql for these to take effect.

If you have trouble making a connection from your remote ruby script then try connecting using 'psql' on the database machine, if that works, try it from the remote machine. That should help pin down where the problem lies.