OLM On Rails

Switching Rails to PostgreSQL

with 11 comments

Since most of the web applications we develop here uses PostgreSQL, we’ve decided to port our environment to use postgres instead. Here I outline how I managed to get rails and PostgreSQL 8.3 up and running on Ubuntu 8.04.

1. Install PostgreSQL

If you haven’t already, try typing:

sudo apt-get install postgresql postgresql-client postgresql-contrib libpq-dev

Make sure that the installation completed successfully by typing psql --version. Current version is 8.3.3. libpq-dev is important since the postgres gem needs it, so make sure that we have that installed even if you have postgresql installed already.

2. Install PostgreSQL adapter for ruby

Update your rubygem first by typing sudo gem update --include-dependencies. You then need to install ruby-pg, which superseded the now-obsolete ruby-postgres gem (which can’t even compile postgres 8.3 due to a compilation bug). To install postgres gem, type:

sudo gem install postgres

3. Create a PostgreSQL user

By default, the user in the rails DB config file is the project name. Thus we can create this user by going into the postgres console

sudo su postgres -c psql

Once in the psql console, type (including the quotes)

create user "<user_name>" with [superuser] password '<password>';

Include the superuser option if needed, usually easier for development when working on your own computer. Verify that the CREATE ROLE message was displayed then exit the console.

4. Change PostgreSQL Authentication

When executing some of the database commands, you might encounter a FATAL: ident authentication failed error. To avoid this, you need to edit your pg_hba.conf (found in /etc/postgresql/8.3/main in Ubuntu 8.04) and change the authentication scheme. Open the file with root privilege and change

# "local" is for Unix domain socket connections only
local all all ident sameuser


# "local" is for Unix domain socket connections only
local all all md5

Then restart your postgres server by typing sudo /etc/init.d/postgresql-8.3 restart

5. Create the databases

to create the database we can use the createdb shell command, by typing on prompt:

createdb <project_name>_development -U <user_name> -W

or, if user does not have superuser account, from the psql console (by sudo’ing as postgres from before) typing:

create database <project_name>_development owner <user_name>;

and give privilege to the user by typing:

grant all privileges on database <database_name> to <user_name>;

Do the same thing for creating <project_name>_test for the test database.

5.5 Install phppgadmin (optional)

To make your life easier, there’s a php console for developing with postgres called phppgadmin, derived from its MySQL counterpart, phpmyadmin. phpmyadmin is still a lot better interface, but phppgadmin is better than nothing at all. Assuming you have PHP and apache2 installed, install phppgadmin by typing:

sudo apt-get install phppgadmin

and create a symlink for apache:

sudo ln -s /etc/phppgadmin/apache.conf /etc/apache2/conf.d/phppgadmin.conf

Reload apache by typing sudo /etc/init.d/apache2 reload and go to http://localhost/phppgadmin/ to play around with your new tool.

6. Generate a database config file for PostgreSQL

If you’re starting from scratch, the easiest way is to create your application using

rails <project_name> -d postgresql

However, if we’re migrating to postgres from a current rails application, then the easiest way is to just create another rails application of the same name but on a different directory. Go to a directory where your current rails application is not located and create a rails application of the same name using above command. Once that’s created, just copy the database config file to your old rails application by copying:

mv <original_project_path>/config/database.yml <original_project_path>/config/database.yml.old

cp <project_name>/config/database.yml <original_project_path>/config

Once copied, open database.yml on a text editor and fill in the database details we just created.


One of the problems I encountered was figuring out which postgresql adapter to use. ruby-postgres wasn’t working with postgres 8.3 so I regressed back to 8.2, only to find out postgres adapter exists. Not much documentation exists since it’s fairly new and a lot of people I guess is still in psql-8.2. The other one is the dreaded FATAL: ident authentication failed error you get when starting to use psql on Ubuntu. Again, thanks to Jeff and the Dr. Project documentation, we finally got it working.

And finally, psql-8.3 seems to break the implicit cast being done from the previous 8.2 before.  So now, when I try to find matches where numbers are stored as string, I get the following error:

ERROR:  operator does not exist: character varying = integer

The column being declared as string does not match the passed integer argument, which should be converted to String in the first place.  Thus I had to explicitly cast it to a String before using.

For more information regarding PostgreSQL and Ruby on Rails, see the official wiki.


Written by Geofrey

August 12, 2008 at 10:22 am

11 Responses

Subscribe to comments with RSS.

  1. […] Switching Rails to Postgres (on Ubuntu) […]

  2. […] Install PostgreSQL on Ubuntu 8.04 (cómo acceder de manera remota a tu servidor) Switching Rails to PostgreSQL […]

  3. […] Shared Switching Rails to PostgreSQL « OLM On Rails. […]

  4. Could you go into more detail on this? Btw, the advice you gave me is really good.

    Coupon Site

    March 12, 2010 at 6:55 am

  5. this is helpfull, thanks..


    March 30, 2010 at 11:08 am

  6. […] seem quite unfamiliar. A great first article to look at is available on the OLM on Rails site at Switching Rails to PostgreSQL. Also at this early stage in your PG career you’ll need to know how to change things like […]

  7. A Excellent wordpress post, I will save this in my Digg account. Have a good evening.

    Keturah Bueno

    June 13, 2010 at 11:03 pm

  8. Two and a half years old and still extremely helpful. I just inherited a Rails 2.2.2 project running PG. This is my first rodeo with PG and this post was invaluable!

    Jeff Poulton

    February 8, 2011 at 1:57 pm

  9. […] Switching Rails to PostgreSQL Getting started with Rails 3 Postgres […]

  10. I did not have the apache server installed and it was installed as a dependency when I installed phppgadmin. You need to start your apache server before hitting localhost:phpgadmin.


    February 15, 2012 at 2:35 pm

  11. […] PostgreSQL on Ubuntu 8.04 (cómo acceder de manera remota a tu servidor) Switching Rails to PostgreSQL Comparte esto:Correo […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: