Switching Rails to PostgreSQL

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

to

# "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.

Epilogue

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.