Sergey Zyablitsky

Developing for web with Rails and Node.js

Using PostgreSQL in Rails Projects

| Comments

PostgreSQL is a mature and time-proven database. Also it is fast, secure and stable. And after all it’s free. So why not to use it in your next Rails project instead of default SQLite? The main drawback is that installing and setting up this database is a bit tricky. I’ve read many tutorials on the web which propose too many movements. But there is much easier way to accomplish this task. Let’s see how we can do this on Ubuntu 12.04.

Installation

First we need to install Postgres server. The easiest way to do this on Ubuntu is installing from package manager.

1
2
$ sudo apt-get upgrade
$ sudo apt-get install postgresql postgresql-contrib libpq-dev

If everything goes well, we’ll have a fully configured and running database server.

Database configuration

To create new user and database we should enter database console as default user postgres.

1
$ psql -U postgres

If above does not work, try this:

1
2
$ sudo su postgres
$ psql

Now crate user and database.

Production server
1
2
3
postgres=# create user your_app_prod with password 'your_password';
postgres=# create database your_app_prod owner your_app_prod;
postgres=# \q
Development machine
1
2
3
postgres=# create database your_app_dev;
postgres=# create database your_app_test;
postgres=# \q

On development machine you should also lower security settings to simplify yor life a bit. Do not do this in production! Cahange your /etc/postgresql/9.1/main/pg_hba.conf to this.

/etc/postgresql/9.1/main/pg_hba.conf
1
2
3
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

Then reload your database server to apply changes.

1
$ sudo service postgresql reload

Rails application configuration

To use newly created Postgres database we should replace config/database.yml content with this.

config/database.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
connection: &connection
  adapter: postgresql
  encoding: unicode
  host: localhost
  reconnect: false
  pool: 5

development:
  <<: *connection
  database: your_app_dev
  username: postgres

test: &test
  <<: *connection
  database: your_app_test
  username: postgres

production:
  <<: *connection
  database: your_app_prod
  username: your_app_prod
  password: <%= ENV['YOUR_APP_DB_PASSWORD'] %>

cucumber:
  <<: *test

Replace gem 'sqlite3' with gem 'pg' in your Gemfile and run bundle install.

Then initialize your databases.

1
2
$ rake db:migrate
$ rake db:migrate RAILS_ENV=test

Deployment

Deploy your application to your server. SSH to your server and set environment variable with database password inside your user’s .profile.

1
$ echo "export YOUR_APP_DB_PASSWORD=\"your_password\"" >> ~/.profile

Then initialize your production database.

1
$ rake db:migrate RAILS_ENV=production

After restart your Rails application should use newly created Postgres database.

Some facts

  • Postgres 9.1 on Ubundu by default permits only local connections. See /etc/postgresql/9.1/main/pg_hba.conf for proof. So we do not need to worry about external hacking of our DB.
  • Unicode equals UTF8 in Postgres.

Comments