Ruby on Rails 2.3 and PostgreSQL on Ubuntu Hardy 8.04 LTS and 10.04 LTS Server

Update: A few changes for 10.04 LTS, using PostgreSQL 8.4

When running rails (other than rails –version), I got the error “No such file to load: net/https”. That was fixed by installing libopenssl-ruby, as in:
aptitude install libopenssl-ruby
This should probably be done before installing rails, although installing it after rails was installed fixed the problem

gem update –system produces a message that “gem update –system is disabled on Debian. RubyGems can be updated using the official Debian repositiries by aptitude or apt-get”

Instead, I found the following at https://help.ubuntu.com/community/RubyOnRails

sudo gem install rubygems-update
sudo update_rubygems note: this will clean out your gems!

Note: I had to reinstall rails after update_rubygems, which I ran after I had installed rails. I would probably do this before installing rails.

The “-y” flag is now the default, and if you use it you get a message to that effect

irb and apache2 were already installed by the time I got to those steps. There is an apache2 metapackage that I would probably use instead of the apache 2.2 packages noted below if I still needed to install apache2.

Before you can run the programs you have installed with gem (e.g. Rails), you will need to add:
export PATH=/var/lib/gems/1.8/bin:$PATH

When I ran the Passenger installation, I got a message to install three more packages:

aptitude install apache2-prefork-dev
aptitude install libapr1-dev
aptitude install libaprutil1-dev

However, only the first one of those actually did anything. Since the passenger installation gives good diagnostics, it is reasonable to let that tell you what still needs to be installed.

Following the instructions on the Passenger install for configuring Apache, the sample configuration included some inline comments with ‘#’ — these caused an error in Apache2 and had to be moved to a separate line.

Passenger may need a file named .htaccess to be installed in the /public directory of your rails app, with the following two lines:

PassengerEnabled on
PassengerAppRoot /full/path/to/the/root/of/your/rails/app

The PassengerAppRoot should NOT be your rails app’s public directory, but the .htaccess file needs to be in that public directory. The Passenger docs incorrectly state that the PassengerAppRoot is assumed to be the parent of the public directory, but that is only true if the public directory is named in DocumentRoot, and not if you are using an alias.

Also, if you are using an alias and the Rails app is not in the root of the website, you may need config.action_controller.relative_url_root = "/test" in your config/environment.rb file

Also note that, except where noted, the installation commands need to be run as root (sudo su -) or with sudo.

There has been much confusion and consternation about setting up Ruby on Rails with PostgreSQL

(e.g., see: http://joshkim.org/2008/10/26/postgresql-ruby-and-rails-i-quit)

There seems to be a lot of support for running this on a Mac, but less so for running it on modern Ubuntu. There are several moving parts here, so once I had figured them out, I wanted to record my notes to save others some of the same aggravation.

Note that there are some other issues and differences between MySQL and PostgreSQL – for example see: http://blog.tiagocardoso.eu/rubyonrail/2008/02/20/porting-to-postgres-on-rails/

In particular, one difference noted there between PostgreSQL and other SQL’s is that PostgreSQL is stricter about the difference between single and double quotes.  Double quotes are for “delimited identifiers”, such as table and column names, and prevent them from being mistaken for keywords.  For example, “SELECT” could be the name of a table or column or variable, whereas SELECT is an SQL keyword.  Single quotes are for string constants.  Use two adjacent single quotes for a literal single quote, as in ‘Dianne”s horse’.  Where this will get you is if you use double quotes in :conditions=>”” and :joins=>””, which will work in MySQL but not PostgreSQL.  Another difference is that “like” may need to be changed to “ilike” in PostgreSQL if you want case insensitive queries.

This post doesn’t attempt to address all issues, but just to get a system from a base Ubuntu Hardy (8.04 LTS) to a working Ruby on Rails 2.2/PostgreSQL 8.3 system.  This will also install working sqlite3 and postgresql drivers, and will test the installation as we proceed.

It also doesn’t attempt to address migration of data; do a web search on “mysql postgresql yml” to see several alternatives here.

(Some of these installation instructions are modified from Agile Web Development with Rails, third edition beta, which I assume you already have)

apt-get update
apt-get upgrade
aptitude install build-essential

if aptitude is not installed, that will cause an error.  Install with:

apt-get install aptitude

Now:

aptitude install ruby rubygems ruby1.8-dev libsqlite3-dev
gem update --system

At the end of a lot of output, was the notice that

RubyGems installed the following executables:

/usr/bin/gem1.8
If 'gem' was installed by a previous RubyGems installation, you may need to
remove it by hand

In my case, I did have to remove the old ‘gem’ file by hand:

mv /usr/bin/gem /usr/bin/gem.old
mv /usr/bin/gem1.8 /usr/bin/gem

If you get the error about the uninitialized constant Gem::GemRunner(NameError), this is your problem

Then:

gem install -y rails

if you get an error that “could not find rails (>0) in any repository”, simply try again

gem install -y rails

To use irb, you need:

aptitude install irb

if you want git:

aptitude install git-core git-doc

if you want apache:

aptitude install apache2.2-common

For passenger:

gem install passenger
passenger-install-apache2-module

You may get some instructions about additional software to install for the passenger apache2 module to be compiled.  You will also get some instructions for configuring passenger to work under apache2.  Be aware that, with Ubuntu, you are encouraged NOT to edit the apache2.conf file, which may need updating with a new version of Ubuntu, but rather to edit other files included by apache2.conf, such as httpd.conf and the sites-available files (linked into sites-enabled when you want them to be enabled).

To use sqlite3 (e.g., for initial testing)

gem install sqlite3-ruby

For PostgreSQL:

aptitude install postgresql postgresql-client

Now, in order to access PostgreSQL, you need to have a PostgreSQL user defined, as well as a PostgreSQL database defined.

The PostgreSQL installation creates the ‘postgres’ Linux user, the ‘postgres’ PostgreSQL user, and the ‘postgres’ database, so to get into the database, you can just (from root):

su postgres
psql

and poke around (psql has pretty good help – use l to list databases, du to list users, ? for help, and q to quit.)

Exit psql with ‘q’

To create a PostgreSQL user so you can test rails with PostgreSQL (in my case, I created user ‘nachbar’, since that is my Linux username) FROM THE SHELL (not from psql):

su postgres
createuser nachbar

(answer ‘y’ to the question about being a superuser)

If you get an error that, for example ‘Ident authentication failed for user “xxxx” ‘, that means you forgot the ‘su postgres’.  Ident authentication means that PostgreSQL will allow Linux user ‘postgres’ in because there is also a PostgreSQL user ‘postgres’

Once you have created your user (in my case, ‘nachbar’), AS THAT USER, try:

psql postgres

Here, ‘postgres’ is the DATABASE name to which you are connecting.  If you don’t specify a database name, psql will try to connect to a database with the same name as your username, which does not exist.  (try just ‘psql’ here to see that error)

Once you have psql working and your user set up in PostgreSQL, create a test rails application and test sqlite3 — as your own user (i.e., not root):

rails test
cd test
script/generate model product title:string
rake db:create
rake db:migrate
script/console
t=Product.first
(that should return nil, since there are no products saved yet)
p=Product.new
p.title="My Title"
p.save
t=Product.first
t.title

The last command should read you  back “My Title” from your saved Product

Now, exit the console, and switch your app to PostgreSQL

exit

edit config/database.yml:

under development:, change adapter to ‘postgresql’ and database to ‘test_development’.  No need to set a username, password, or anything else

Install the postgresql adaptor (as root)

First: install the postgreSQL header files:

aptitude install libpq-dev
gem install postgres

Then, test it:

irb
require 'rubygems'
require 'postgres'

Now, (back as your own user, not root, and in the rails test project directory): create the PostgreSQL database:

rake db:create
rake db:migrate

Test that these were created in PostgreSQL:

psql test_development
l  (to list databases)
dt  (to list tables - should include the products table)
q  (to exit psql)

Run the same “script/console” test above, which should give the same results as it did with sqlite3.

Check the PostgreSQL database:

psql test_development
select * from products;
(don’t forget the semicolon.  Should show your your “My Title” product, now in PostgreSQL)
q

Rails is running with PostgreSQL!

Note that we did not set a user or password in database.yml, because we had created the ‘nachbar’ user as a PostgreSQL superuser, and that was the user that script/console and rake were running as.  We used ‘Ident’ authentication in this case.  There are several choices here, including creating another PostgreSQL user under which Rails will run.  Since ‘nachbar’ is now a PostgreSQL superuser, you can run the createuser command as ‘nachbar’ or ‘postgres’, but not as root!  In PostgreSQL, if the password is null, password authentication will always fail.

Other miscellaneous notes

PostgreSQL configuration notes:

PostgreSQL is set up to allow multiple “clusters”.  Installation creates a single cluster, “main”, which will probably be all you need.  In the following, “main” could refer to multiple directories if you have multiple clusters.  Also “8.3” is my PostgreSQL version number.  Other versions will, of course, have different directory names.

PostgreSQL configuration goes into /etc/postgresql/8.3/main and /etc/postgresql-common

PostgreSQL bin is in /usr/lib/postgresql/8.3/bin .  That directory is NOT added to the PATH, but appropriate links for psql, createuser, etc. are placed into /usr/bin.  Other commands, such as pg_ctl may not be in the path.  The base path for the Ubuntu bash shell is set in /etc/login.defs file in the ENV_SUPATH and ENV_PATH vars

The data directory is /var/lib/postgresql/8.3/main — see /var/lib/postgresql/8.3/main/postmaster.opts

According to /etc/init.d/postgresql-8.3, environment vars are set in /etc/postgresql/8.3/<cluster>/environment

possible options to /etc/init.d/postgresql-8.3 are:

start, stop, restart, reload, force-reload, status, autovac-start, autovac-stop, autovac-restart

(the functions are sourced from /usr/share/postgresql-common/init.d-functions)

On init, the init.d script looks for directories in /etc/postgresql/<version> (by default, ‘main’ exists there) then, in those directories, look for postgresql.conf, which is the file that sets the data directory (/var/lib/postgresql/8.3/main), and the hba_file and ident_file (in /etc/postgreql/8.3/main), port, etc., as well as all sorts of configuration FOR THE SERVER

start.conf determines whether the specific server gets started on bootup

to backup:

pg_dumpall > outputfile

to stop the server:

pg_ctl stop

some samples in

/usr/share/postgresql/8.3

Rake and Rails data on PostgreSQL

The ‘postgresql’ database driver supports rake commands such as


rake db:drop
rake db:create
rake db:schema:load RAILS_ENV=production

Be aware that PostgreSQL does not use autoincrement fields, but rather implements a more structured system using PostgreSQL sequences. Rails will create these for you, and will tell you about it. Thus, the rake db:schema:load will produce messages like:


-- create_table("appts", {:force=>true})
NOTICE: CREATE TABLE will create implicit sequence "appts_id_seq" for serial column "appts.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "appts_pkey" for table "appts"

Those notices tell you that this mechanism is working properly.

Rails, Passenger, and PostgreSQL users

As indicated in the Passenger docs, passenger will run as the user that owns config/environment.rb, but that can be changed as indicated in the User Switching section of the Passenger docs, and can be modified by the PassengerUserSwitching and PassengerDefaultUser options in your httpd.conf or apache2 site files. Whichever user Passenger runs as must have a PostgreSQL user with the appropriate rights. Options include making that user a PostgreSQL superuser, or instituting better access controls with SQL GRANT commands.

In addition, options include other than the “Ident” mechanism of logging into PostgreSQL that we have discussed above. See the PostgreSQL website for details.

As one example, you can create the group and user “passenger” to use for passenger to run as, including the PostgreSQL user.:

adduser --system --group passenger

Change the group for the railsapp directory files by cd to the railsapp directory and issuing

chgrp -R passenger *

Change the mode for the log files and directory, so that the group (now ‘passenger’) can change those

cd log
chmod g+w .
chmod g+w *

Create the PostgreSQL user passenger:

su postgres
createuser passenger

(answer ‘n’ to all three questions: superuser, create databases, create roles)

Grant access to the passenger PostgreSQL user:

su postgres
psql
c myrailsapp_production
grant all on audits, sessions, users, mymodel1s, mymodel2s to passenger;
grant all on sequence audits_id_seq, sessions_id_seq, users_id_seq, mymodel1s_id_seq, mymodel2s_id_seq to passenger;
q

Either change the owner of config/environment.rb to passenger, or set PassengerDefaultUser to passenger

Now Passenger will run as the ‘passenger’ user, and will also set the effective group to the default group of the ‘passenger’ user (also passenger, in this setup). It will access PostgreSQL as the PostgreSQL passenger user, as well, using ident authentication.  Of course, ident authentication works only within a single machine.  To access PostgreSQL from another machine, set the hostname, username, password, and port in Rails.

touch tmp/restart.txt to restart Passenger on the next request.

Setting timezone on Ubuntu (different than setting it for your Rails app)

ln -sf /usr/share/zoneinfo/America/Phoenix /etc/localtime

Setting up the mail server on Ubuntu so Action Mailer Works:

Mail: exim4 was already running, but would not deliver except locally.  Make changes to /etc/exim4/update-exim4.conf.conf – esp change configtype to ‘internet’ (so mail can go out to the internet) but leave local_interfaces at ‘127.0.0.1’ so mail will be accepted only from the local system.  Also change readhost to ‘myhostnamehere.com’ so headers show that as the origin, and hide_mailname so readhost works.  Also, change /etc/mailname to mydomainnamehere.com, to indicate the domain of the user sending the mail.

The Virtual Server

To reproduce what I have done, I Actually implemented the above on the 1&1 VPS I Linux package imaged to Ubuntu 8.04 LTS (64 bit). I think you can get a discount on that if you click this link:
Banner

Happy Hacking!

11 thoughts on “Ruby on Rails 2.3 and PostgreSQL on Ubuntu Hardy 8.04 LTS and 10.04 LTS Server

  1. Thanks for the article!
    When I tried to su postgres I had authentication error cause of wrong password.
    To fix it you need to do sudo passwd postgres and then type your new password twice.
    Now you can simply change user to postgres by command su postgres and typing your password.
    Maybe someone will find it helpful.

  2. Yes, mihserf: you can use su to change to the postgres account either from root (without a password) or from another user (with a password). There are pros and cons to doing it either way; use whichever you are more comfortable with.

  3. I have a problem with postgresql ident authentication.
    I installed apache, ruby on rails and passenger.
    I use ident authentication and i got “MIdent authentication failed for user “httpd”.
    I added httpd user to postgresql users. But it does not help.
    Here is my database.yml
    development:
    adapter: postgresql
    encoding: unicode
    database: testp_development
    pool: 5
    username: httpd
    password: 12345
    I know that problem is in ident authentication.
    Here is pg_hba.conf

    TYPE DATABASE USER CIDR-ADDRESS METHOD

    # “local” is for Unix domain socket connections only
    local all all ident sameuser
    # IPv4 local connections:
    host all all 127.0.0.1/32 ident sameuser
    # IPv6 local connections:
    host all all ::1/128 ident sameuser

    Please help me.

  4. Alex: To troubleshoot, try su’ing to the httpd user, and then, as the httpd user, try accessing your database with “psql testp_development” . Try the psql commands above to list databases, etc. Don’t forget to GRANT the privileges to the httpd user that you need. Reread the sections above on PostgreSQL users, and the psql commands.

Leave a Reply

Your email address will not be published. Required fields are marked *