PostgreSQL for Ruby on Rails on Ubuntu

My new desktop came in at work this week, and the installation was painless thanks to the great driver support of Ubuntu 11.10. For anyone setting up a Rails development box based on Linux, I have some tips to get around some pain points when using a PostgresSQL database.

Installation:

Postgres can be quickly and easily installed using apt-get on Debian or Ubuntu based distributions. Issue the command:

apt-get install postgresql

Ruby Driver

In order for Ruby to connect to PostgreSQL databases, you will need to install the pg gem. This gem will need the development package of PostgreSQL to successfully build its native extension. To install the PostgreSQL development package, issue the following command:

apt-get install libpq-dev # EDIT: postgresql-dev was replaced by this package on Ubuntu 11.10

Setup A PostgreSQL Role

You can configure PostgreSQL to allow your account to have superuser access, allowing your Rails tasks to create and drop databases. This is useful for development, but is strongly discouraged for a production. That being said, we can create a PostgreSQL role by logging into psql as postgres as follows:

su postgres -c psql

This will open a PostgreSQL prompt as the database owner postgres. Next, we need to create an account for our user. This should match the response from “whoami”:

CREATE ROLE <username> superuser login;

We can now exit from psql by issuing “\q“. Try to connect to psql directly by issuing the following command from your shell account:

psql postgres

This should allow you to connect to the default database postgres without being prompted for credentials. You should now be able to issue the rake commands for creating, and dropping the database:

rake db:create

Rspec Prompts for Credentials

I was being prompted by Rspec for credentials when running my test suite. If you would like to remove this credential prompt, please read the following:

There are differences in how the PostgreSQL package is configured in Homebrew on OS X, and how it is packaged in the Ubuntu and across other distributions. One difference is in the level of security configured in the pg_hba.conf file. This file is responsible for identifying which sources using which authentication mechanisms should be allowed or denied. By default, Rspec will cause a prompt for a password even if your shell account has trusted permissions. This is because Rspec connects not as a local process, but to localhost. To allow connections to localhost to be trusted, you will need to modify the pg_hba.conf file.

Next, we can modify the pg_hba.conf file located at /etc/postgresql/<version>/main/pg_hba.conf

Comment out the lines any lines at the bottom of the file and append the following:

local   all             all                                      trust
host    all             all              127.0.0.1/32            trust
host    all             all              ::1/128                 trust

This will allow connections from the shell, as well as connections to 127.0.0.1 (localhost) using both IPv4 and IPv6.

You will need to restart PostgreSQL for the changes from this file to take affect:

/etc/init.d/postgresql restart

PostgreSQL Extensions

If you want to make use of any of the additional extensions to Postgres, including fuzzystrmatching, you will need to install the postgresql-contrib package:

apt-get install postgresql-contrib

The extensions will install to /usr/share/postgresql/<version>/extension/

Using the Postgres version 9, you can create these extensions in your database by using the new CREATE EXTENSION syntax. In the case of the fuzzystrmatch extensions, you can issue the following command from inside a PostgresSQL command prompt to load the extensions:

psql <database name>;

Once inside your database:

CREATE extension fuzzystrmatch;

8 thoughts on “PostgreSQL for Ruby on Rails on Ubuntu

  1. Hi, I am hoping you can help me a bit. I am having a heck of a time getting postgresql-9.1 on Ubuntu 11.1. Your instructions look like they should work but I run into this.

    When I run:
    sudo apt-get install postgresql-dev
    I Get:
    Package ‘postgresql-dev’ has no installation candidate
    After a little searching I found this:
    sudo apt-get install libpq-dev
    It seemed to work and I was able to run
    gem install pg
    gem list produces: pg (0.11.0)
    I thought I was free and clear….
    Now when I do this
    sudo su postgres -c psql
    Error: You must install at least one postgresql-client- package.

    I don’t know what to do. The only thing close Is on stack overflow and it’s just telling me to install 8.

    I also tried sudo apt-get install postgresql-client
    but I still get
    psql template1
    Error: You must install at least one postgresql-client- package.

    I am new to Linux but have worked with Pg8.x in the windows world. Any help would be very much appreciated.
    Thanks

  2. It looks like the postgresql-dev page has been replaced by the libpq-dev package. I have verified this on Ubuntu 11.10. I will update the article to show the new package name. Good luck!

  3. Pingback: Postgresql for Ruby on Rails on Ubuntu 12.04 | Trash@

  4. Pingback: Migrating from Bamboo to Cedar | Zone of Mr. Frosti

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">