PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. ¹⁾

There is currently only the default PostgreSQL database adapter for ActiveRecord / Rails available. In database.yml files it is referenced by the name “postgresql”. That adapter currently supports pg, postgres and postgres-pr gems.

There are currently 4 different PostgreSQL-Client libraries available as gem:

The postgres gem is written in C and is supported by ActiveRecord. The last release was 0.7.9 (2008-01-24). Development has been discontinued in favor of the pg gem.

The pg gem is the successor of the postgres gem and is also written in C. Actually, it is the main postgresql gem supported by ActiveRecord: if pg gem is not present, ActiveRecord will try to load postgres and monkey patch it to support the last functionalities. The last release is 0.8 (2009-03-29), which seems to be a sign of active development.

Both postgres and pg projects are hosted here: http://bitbucket.org/ged/ruby-pg/

The postgres-pr gem is written in pure Ruby and is also supported by ActiveRecord because an included compatibility layer which mimics the “postgres” gem. The last release was 0.6.1 (2009-03-02), which seems to be a sign of active development. The project is hosted here: https://rubyforge.org/projects/postgres-pr/

Not much is known about the ruby-postgres gem, but it's NOT supported by ActiveRecord. The last release was 0.7.1 (2006-04-06). The project is hosted here: https://rubyforge.org/projects/ruby-postgres/

To create a new Rails application using the PostegreSQL adapter:

$ rails -d postgresql my_rails_app

Next, edit database.yml accordingly.

  development:
    adapter: postgresql
    host: localhost
    port: 5432
    username: your_db_username
    password: your_db_password
    database: your_rails_project_development
    schema_search_path: public
    encoding: utf8
    template: template0

You don't need to specify the port if 5432 should be used. encoding and schema_search_path are also optional unless you want to circumvent PostgreSQL's defaults. PostgreSQL 8.3 installed with default encoding of SQL_ASCII. To create a database using UTF8, specify “template0” for the template.

If you don't specify a host and port attribute on unix like machines, domain sockets will be used instead of TCP sessions. Alternative domain socket paths can also be specified through the host attribute. The default domain socket path seems to be /var/run/postgresql (yes, that seems to be a folder, but it works nicely).

If ident auth is correctly configured for PostgreSQL and you are using domain sockets, you can also remove the password attribute to use ident auth. Normally ident auth lets you connect without a password if your system user name matches the name of the database user. So in this case, the system user running Rails has to have the same name as the database user to be used for password-free authentication to work.

Note: you could use unicode instead of utf8 for encoding.

aptitude install ruby-full build-essential postgresql-server-dev-8.3

gem install postgres

• Download source

Found at http://www.postgresql.org/ftp/source/

• Unpack source and compile

# Change the number to your downloaded version
$ tar zxvf postgresql-8.3.6.tar.gz
$ cd postgresql-8.3.6
$ ./configure
$ make
$ sudo make install

• Make Postgres user and group

$ sudo dscl . create /Users/_pgsql

 $ sudo dscl . create /Users/_pgsql UserShell /bin/tcsh
 $ sudo dscl . create /Users/_pgsql UniqueID 101
 $ sudo dscl . create /Users/_pgsql PrimaryGroupID 101
 $ sudo dscl . create /Users/_pgsql NFSHomeDirectory /usr/local/pgsql/
 $ sudo dscl . create /Users/_pgsql RealName "PostgreSQL Server"
 $ sudo dscl . append /Users/_pgsql RecordName pgsql
 $ sudo dscl . create /Groups/_pgsql
 $ sudo dscl . create /Groups/_pgsql PrimaryGroupID 101
 $ sudo dscl . append /Groups/_pgsql RecordName pgsql
 $ sudo dscl . create /Groups/_pgsql RealName "PostgreSQL Users"

• Make pgsql directory owned by Postgres user and group

 $ sudo chown -R _pgsql:_pgsql /usr/local/pgsql

• Initialize cluster

 $ sudo su - _pgsql
 $ /usr/local/pgsql/bin/initdb -E utf8 --locale=en_US.UTF-8 -D /usr/local/pgsql/data
 $ exit

• Install server port

$ sudo port install postgresql83-server

• Launch postgresql right now and at each reboot through LaunchDaemons

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist

• Initialize database

$ sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'

• Note:
  • For Snow Leopard 32-bit build use: '-arch i386'
  • For Snow Leopard 64-bit build use: '-arch x86_64'

• Build pg gem (on Intel Mac OS X 10.5, with postgresql installed from source)

 $ sudo su -
 # setenv ARCHFLAGS '-arch i386' # or '-arch x86_64' for 64bit version
 # gem install pg -- --with-pg-include=/usr/local/pgsql/include --with-pg-lib=/usr/local/pgsql/lib
 # exit

• Build pg gem (on Intel Mac OS X 10.5, with postgresql installed from macports)

 $ sudo env ARCHFLAGS="-arch i386" gem install pg

• If pg_config is not in your PATH (ERROR: can't find pg_config.)

$ PATH=/opt/local/lib/postgresql83/bin:$PATH sudo env ARCHFLAGS=”-arch i386” gem install pg

Please note: from http://rubyforge.org/projects/ruby-pg/ you can see that the most up to date version of the gem is 'pg' not 'postgres'.