Ruby on Rails
PostgreSQL (Version #730)

PostgreSQL is a ANSI SQL and ACID compliant Relational Database. It has support for Rules, Views, Sequences, Inheritance, Sub-selects and Left Outer Joins. It is also very extendable. See the Full Text extension for a good example of this.

PostgreSQL Sequences and Rails

You can install a pure Ruby postgres driver called postgres-pr using Gems:

gem install postgres-pr

Note that you still have to write adapter: postgresql and not adapter: postgres-pr in your database.yml.

A faster, native driver called “postgres” is also available. It can be installed with the command:

gem install postgres

If you receive an error message like

extconf.rb:6:in `require': no such file to load --  mkmf (LoadError) from extconf.rb:6

you have to install the ruby1.8-dev package. This happens with Ubuntu Dapper.

Note that you need the PostgreSQL dev package (postgresql-dev on Debian, now known as libpq-dev for Debian’s multicluster layout) to successfully build and install these bindings. If you installed the dev package and still get errors for missing header files such as libpq-fe.h or libpq/libpq-fs.h, make sure you indeed have those files in the appropriate directory (/usr/include/postgresql for Debian), then try the following command instead of the one above:

POSTGRES_INCLUDE=/usr/include/postgresql gem install postgres

With Debian etch on a 64 bit system, the gem successfully installed for me but testing it with the following failed…


irb
require 'postgres' # => Could not load driver (no such file to load -- postgres)

The trick was to manually install it


wget <a href="http://ruby.scripting.ca/postgres/archive/ruby-postgres-0.7.1.tar.gz">http://ruby.scripting.ca/postgres/archive/ruby-postgres-0.7.1.tar.gz</a>
tar zvxf ruby-postgres-0.7.1.tar.gz
cd ruby-postgres-0.7.1
ruby extconf.rb --with-pgsql-include-dir=/usr/include/postgresql --with-pgsql-lib-dir=/usr/lib/
make
sudo make install

which should be the same as (but I didn’t try it)


gem install postgres -- --with-pgsql-include-dir=/usr/include/postgresql --with-pgsql-lib-dir=/usr/lib/

Note: It’s just been announced to the PostgreSQL mailing list that ruby-pg is now the “official” project for the postgres ruby gem that provides the postgres adapter to connect to a PostgreSQL database. [Rebecca Blyth 17 Dec 2007]

The postgres gem can be downloaded and installed (from http://gems.rubyforge.org) using

sudo gem install postgres -- --with-pgsql-include-dir=/Library/PostgreSQL8/include --with-pgsql-lib-dir=/Library/PostgreSQL8/lib</pre> (on Mac OS X with Postgresql from http://www.postgresqlformac.com )
Replace /Library/PostgreSQL8 with the location of the lib and include files on your system. You can also download the gem manually from http://rubyforge.org/projects/ruby-pg/

This builds and installs two database adapters – postgres and pg. The postgres adapter is referred to as

adapter: postgresql

in your database:yaml file
The pg adapter cannot be used with Rails yet. [David Abernethy 4 Jan 2007]

PostgreSQL versions 8.0 and up are available for Win32 natively and the Ruby postgres binding for Windows can be installed via:


gem install ruby-postgres

Note for Windows: If you are having problems using the ruby-postgresql gem even though it was “successfully” installed by rubygems, try this. It should help you fix any LoadError or “libpq.dll was not found” error you encounter. If you do not intend on keeping PostgreSQL installed on your machine you can happily get the files mentioned on the link from the zipped format of the ODBC driver that corresponds to your server’s version. I do not know if an older or newer version of libpq.dll could cause any conflict with your server’s version, so try at your own risk.
[Javier Arias 10 Feb 2008]

FYI, PGAdmin-III is available for Win32 and the ODBC drivers are included with the PostgreSQL Win32 distribution.

Note

If you’re receiving the error:

FATAL C28000 MIDENT authentication failed for user "todo" Fauth.c L395 Rauth_failed

it is due to Rails connecting to PostgreSQL via TCP socket, not UNIX socket, so in the pg_hba.conf you need to setup a “host” line, not a “local” line.

So, something like:

host all all 127.0.0.1 255.255.255.255 password

host all all 127.0.0.1 255.255.255.255 trust

Also make sure that your PostgreSQL is listening to TCP/IP requests. If you are starting PostgreSQL via pg_ctl simply add ‘-o -i’ to the end of the line. On OS X my PostgreSQL start command looks like this:

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data  -l logfile start -o -i

The -o tells pg_ctl to pass the next parameter to the postmaster server executable, and that -i says “listen to TCP/IP requests”.

Under a stock Ubuntu install, you will probably encounter either of these errors:

e_sendauth: no password supplied

fe_sendauth: no password supplied

This is due to the permissions in pg_hba.conf being too restrictive by default. To allow rails to connect, simply change the bottom of pg_hba.conf to look like this.


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

FreeBSD and postgresql gem

If you’ve installed PostgreSQL from ports (as you should), the include path that gem uses does not get set correctly, so to compile the gem, type:

#gem install postgres -- --with-pgsql-include-dir=/usr/local/include --with-pgsql-lib-dir=/usr/local/lib

Using UNIX socket instead of TCP/IP

To use a UNIX socket instead of TCP/IP, simply
specify the directory holding the postgresql socket as the host :
E.g:


#host: localhost
host: /var/run/postgresql

(nb: you’ll only need to do this if your OS puts the socket somewhere other than /tmp)

Mac OS 10.4 getting ECONNREFUSED

On Tiger:

gem install postgres-pr

runs without error but does not allow you to connect to the database.

note: this is not a connector nor a Tiger problem, as postgres-pr is reported to work correctly on Tiger, at least when installed in a pristine Ruby+RoR environment, not involving the Tiger-shipped Ruby.

Take a look at madhatter’s Ruby on Rails, Tiger and PostgreSQL blog entry. He suggests running:

gem install postgres cd /usr/lib/ruby/gems/1.8/gems/postgres-0.7.1/ ruby extconf.rb —with-pgsql-include-dir=/usr/local/pgsql/include —with-pgsql-lib-dir=/usr/local/pgsql/lib

make install

or, as a single command:

gem install postgres -- --with-pgsql-include-dir=/usr/local/pgsql/include --with-pgsql-lib-dir=/usr/local/pgsql/lib

On my installation of Tiger the postgres-0.7.1 gem is located here:
<pre>/usr/local/lib/ruby/gems/1.8/gems/postgres-0.7.1</pre>

Problems with BYTEA

Sometime between release 0.7.1 and the 2005-12-21 of ruby-postgres (the binary driver) the driver was changed to return binary data for a BYTEA column instead of returning the escaped data. Rails 1.0.0, unfortunately, always unescapes the returned data, which means that with this newer driver the data are unescaped twice.

One possible way to deal with this would be, on connect, to select some static binary data (e.g., “SELECT ‘\000’::bytea” or similar) and see if the escaped or unescaped version is returned, and change active_record/connection_adapters/postgresql_adapter.rb not to call unescape_bytes around line 360 if the driver is already returning the unescaped bytes.

Fun with WindowsXP?

Install native PostgreSQL 8.x as a windows service

gem install postgres-pr


gem install ruby-postgres

Note: If you are having problems with ruby-postgres search this page for “Note for Windows” (no quotes).
[Javier Arias 10 Feb 2008]

```
listen_addresses = '*'
```
in C:\Program Files\PostgreSQL\8.x\data\postgresql.conf. This enables TCP/IP for PostgreSQL
add
```
host all all 192.168.1.0 255.255.255.0 password
```
in C:\Program Files\PostgreSQL\8.x\data\pg_hba.conf. Obviously, put your IP network there. When connections come from Rails, they are coming from your IP, not 127.0.0.1. Keep the default host line too so that your DB tools can connect too.

This is what it took to get me up and running. Tweak settings for security as you see fit.

You can also set:

log_destination = 'eventlog'

in C:\Program FilesPostgreSQL\8.x\data\postgresql.conf for Event Viewer logging.

Problems with Make on some platforms

For some users, building the native postgres gem appears to succeed but make has failed with the message:

"make: Nothing to be done for `install'."

And then despite the gem being installed, you can’t use it.

You can work around this by running make again yourself:


cd /var/lib/gems/1.8/gems/postgres-whatever
(or on some systems /usr/lib/ruby/gems/1.8/gems/postgres )
make distclean
ruby extconf.rb
make install(or 'sudo make' if gems installed as root)

After this the gem should work.

Schemas

To specify the schemas, in your database.yml, add the following line:

schema_search_path: yourSchemaName

Troubleshooting

I found that on WinXP, to get scaffolding to work, I had to modify database.yml, specifying the host IP address ‘127.0.0.1’ instead of ‘localhost’, changing from:

development/test/production:
  adapter: postgresql
  database: cookbook
  username: uid
  password: pwd
  host: localhost

to:

development/test/production:
  adapter: postgresql
  database: cookbook
  username: uid
  password: pwd
  host: 127.0.0.1

Additionally, here is my working pg_hba.conf contents:

# IPv4 local connections:
# pgAdmin III connection parameters:
host    all         all         127.0.0.1 255.255.255.0          md5
# Rails connection parameters:
host    all	    	  all 	  192.168.2.2 255.255.255.0 	     password

PostgreSQL Version 7.4 is the earliest version of PostgreSQL that is compatible with the ActiveRecord component of Rails.

Note: As of Rails 0.13, the PostgreSQL adapter supports older versions of PostgreSQL, at least back to 7.1.3 and possibly much older.

Note: The PostgreSQL Global Development Group (PGDG for short) only provide support and bug fixes for PostgreSQL versions 7.3 and up. Older versions are NOT supported, and in fact have known data loss and stability bugs. The latest version should be used where possible.

Tests

How to test with postgresql?
Test user needs privileges to create/drop test database. How could this be handled?

One way to handle this would be to clean up the connection code to use the default connect parameters (PGUSER, PGDATABASE, etc. in the Unix environment, which connect with appropriate defaults in the ruby-postgres driver if not set) and just create a new schema to hold the test information. Thus, if a user can type “psql” and connect to his own database (or whichever one he’s set up with environment variables), things will be fine.

The other way is to give this privelege to the test user:
UPDATE pg_authid SET rolcreatedb=true WHERE rolname='your_test_username'

Resources


wget <a href="http://ruby.scripting.ca/postgres/archive/ruby-postgres-0.7.1.tar.gz">http://ruby.scripting.ca/postgres/archive/ruby-postgres-0.7.1.tar.gz</a>
tar zvxf ruby-postgres-0.7.1.tar.gz
cd ruby-postgres-0.7.1
ruby extconf.rb --with-pgsql-include-dir=/usr/include/postgresql --with-pgsql-lib-dir=/usr/lib/
make
sudo make install

which should be the same as (but I didn’t try it)


gem install postgres -- --with-pgsql-include-dir=/usr/include/postgresql --with-pgsql-lib-dir=/usr/lib/

The postgres gem can be downloaded and installed (from http://gems.rubyforge.org) using

This builds and installs two database adapters – postgres and pg. The postgres adapter is referred to as

adapter: postgresql

in your database:yaml file
The pg adapter cannot be used with Rails yet. [David Abernethy 4 Jan 2007]

PostgreSQL versions 8.0 and up are available for Win32 natively and the Ruby postgres binding for Windows can be installed via:


gem install ruby-postgres

Note

If you’re receiving the error:

FATAL C28000 MIDENT authentication failed for user "todo" Fauth.c L395 Rauth_failed

it is due to Rails connecting to PostgreSQL via TCP socket, not UNIX socket, so in the pg_hba.conf you need to setup a “host” line, not a “local” line.

So, something like:

host all all 127.0.0.1 255.255.255.255 password

host all all 127.0.0.1 255.255.255.255 trust

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data  -l logfile start -o -i

The -o tells pg_ctl to pass the next parameter to the postmaster server executable, and that -i says “listen to TCP/IP requests”.

Under a stock Ubuntu install, you will probably encounter either of these errors:

e_sendauth: no password supplied

fe_sendauth: no password supplied

This is due to the permissions in pg_hba.conf being too restrictive by default. To allow rails to connect, simply change the bottom of pg_hba.conf to look like this.


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

FreeBSD and postgresql gem

If you’ve installed PostgreSQL from ports (as you should), the include path that gem uses does not get set correctly, so to compile the gem, type:

#gem install postgres -- --with-pgsql-include-dir=/usr/local/include --with-pgsql-lib-dir=/usr/local/lib

Using UNIX socket instead of TCP/IP

To use a UNIX socket instead of TCP/IP, simply
specify the directory holding the postgresql socket as the host :
E.g:


#host: localhost
host: /var/run/postgresql

(nb: you’ll only need to do this if your OS puts the socket somewhere other than /tmp)

Mac OS 10.4 getting ECONNREFUSED

On Tiger:

gem install postgres-pr

runs without error but does not allow you to connect to the database.

Take a look at madhatter’s Ruby on Rails, Tiger and PostgreSQL blog entry. He suggests running:

gem install postgres cd /usr/lib/ruby/gems/1.8/gems/postgres-0.7.1/ ruby extconf.rb —with-pgsql-include-dir=/usr/local/pgsql/include —with-pgsql-lib-dir=/usr/local/pgsql/lib

make install

or, as a single command:

gem install postgres -- --with-pgsql-include-dir=/usr/local/pgsql/include --with-pgsql-lib-dir=/usr/local/pgsql/lib

On my installation of Tiger the postgres-0.7.1 gem is located here:
<pre>/usr/local/lib/ruby/gems/1.8/gems/postgres-0.7.1</pre>

Problems with BYTEA

Fun with WindowsXP?

Install native PostgreSQL 8.x as a windows service

gem install postgres-pr


gem install ruby-postgres

Note: If you are having problems with ruby-postgres search this page for “Note for Windows” (no quotes).
[Javier Arias 10 Feb 2008]

```
listen_addresses = '*'
```
in C:\Program Files\PostgreSQL\8.x\data\postgresql.conf. This enables TCP/IP for PostgreSQL
add
```
host all all 192.168.1.0 255.255.255.0 password
```
in C:\Program Files\PostgreSQL\8.x\data\pg_hba.conf. Obviously, put your IP network there. When connections come from Rails, they are coming from your IP, not 127.0.0.1. Keep the default host line too so that your DB tools can connect too.

This is what it took to get me up and running. Tweak settings for security as you see fit.

You can also set:

log_destination = 'eventlog'

in C:\Program FilesPostgreSQL\8.x\data\postgresql.conf for Event Viewer logging.

Problems with Make on some platforms

For some users, building the native postgres gem appears to succeed but make has failed with the message:

"make: Nothing to be done for `install'."

And then despite the gem being installed, you can’t use it.

You can work around this by running make again yourself:


cd /var/lib/gems/1.8/gems/postgres-whatever
(or on some systems /usr/lib/ruby/gems/1.8/gems/postgres )
make distclean
ruby extconf.rb
make install(or 'sudo make' if gems installed as root)

After this the gem should work.

Schemas

To specify the schemas, in your database.yml, add the following line:

schema_search_path: yourSchemaName

Troubleshooting

I found that on WinXP, to get scaffolding to work, I had to modify database.yml, specifying the host IP address ‘127.0.0.1’ instead of ‘localhost’, changing from:

development/test/production:
  adapter: postgresql
  database: cookbook
  username: uid
  password: pwd
  host: localhost

to:

development/test/production:
  adapter: postgresql
  database: cookbook
  username: uid
  password: pwd
  host: 127.0.0.1

Additionally, here is my working pg_hba.conf contents:

# IPv4 local connections:
# pgAdmin III connection parameters:
host    all         all         127.0.0.1 255.255.255.0          md5
# Rails connection parameters:
host    all	    	  all 	  192.168.2.2 255.255.255.0 	     password

PostgreSQL Version 7.4 is the earliest version of PostgreSQL that is compatible with the ActiveRecord component of Rails.

Note: As of Rails 0.13, the PostgreSQL adapter supports older versions of PostgreSQL, at least back to 7.1.3 and possibly much older.

Tests

How to test with postgresql?
Test user needs privileges to create/drop test database. How could this be handled?

The other way is to give this privelege to the test user:
UPDATE pg_authid SET rolcreatedb=true WHERE rolname='your_test_username'

Ruby on Rails
PostgreSQL (Version #730)

Note

FreeBSD and postgresql gem

Using UNIX socket instead of TCP/IP

Mac OS 10.4 getting ECONNREFUSED

Problems with BYTEA

Fun with WindowsXP?

Problems with Make on some platforms

Schemas

Troubleshooting

Tests

Resources

See Also

Note

FreeBSD and postgresql gem

Using UNIX socket instead of TCP/IP

Mac OS 10.4 getting ECONNREFUSED

Problems with BYTEA

Fun with WindowsXP?

Problems with Make on some platforms

Schemas

Troubleshooting

Tests

Resources

See Also

Ruby on RailsPostgreSQL (Version #730)

Note

FreeBSD and postgresql gem

Using UNIX socket instead of TCP/IP

Mac OS 10.4 getting ECONNREFUSED

Problems with BYTEA

Fun with WindowsXP?

Problems with Make on some platforms

Schemas

Troubleshooting

Tests

Resources

See Also

Note

FreeBSD and postgresql gem

Using UNIX socket instead of TCP/IP

Mac OS 10.4 getting ECONNREFUSED

Problems with BYTEA

Fun with WindowsXP?

Problems with Make on some platforms

Schemas

Troubleshooting

Tests

Resources

See Also

Ruby on Rails
PostgreSQL (Version #730)