Setting up PostgreSQL on Ubuntu (precise/12.04), Ruby on Rails (RoR/3.2.11 ruby/1.9.3) and Heroku (gem/2.34.0)

At Sellf we are using PostgreSQL as our backend database and facing with its configuration it wasn’t so immediate as I would expect. I wrote this tutorial for Ubuntu an RoR to easily configure PostgreSQL in combo with Heroku. Some steps are directly cutted from the original guide of PostreSQL written by the Heroku team.

#1 Installation

To install Postgresql you may use the command line and type:

sudo apt-get install postgresql

#2 Basic server setup

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command:

sudo -u postgres psql postgres

Set a password for the “postgres” database role using the command:

\password postgres

and give {your-password} when prompted. The password text will be hidden from the console for security purposes. Type Control+D to exit the posgreSQL prompt.
To create the databases for the development and test environments, simply type:

sudo -u postgres createdb {your-db-dev-name}
sudo -u postgres createdb {your-db-test-name}

where {your-db-dev-name} and {your-db-test-name} are the names of the databases at your convenience.

#3 Installation of pgAdmin III

PgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

#4 Installation of pg Ruby gem

To use PostgreSQL as your database in Ruby applications you will need to include the pg gem in your Gemfile.

group :development, :test do
gem 'pg', '0.12.2'

group :production do
gem 'pg', '0.12.2'


bundle update
bundle install --without production

to download and resolve all dependencies.
To verify the presence of the installed gem type

bundle show pg

#5 Change the database.yml file (located at {rails-root}/config/)

To use PostgreSQL locally with a Rails app your database.yml should contain the following configuration:

adapter: postgresql
encoding: unicode
host: localhost
username: {your-username}
password: {your-password}
database: {your-db-dev-name}
pool: 5

adapter: postgresql
encoding: unicode
host: localhost
username: {your-username}
password: {your-password}
database: {your-db-test-name}
pool: 5

#6 Migrate the database

Once the database.yml is configured, it is necessary to migrate the database following the standard rake procedure:

bundle exec rake db:migrate

Note that PostgreSQL does not have “a single” file with all the data, structure,… basically the whole database. This is the (dis-)advantage of SQLite: it has everything in a single file that makes development very easy.
Depending on your platform, you have a postgresql folder somewhere where all data, table structures, indexes,… are stored. On Ubuntu, the location is per default /var/lib/postgresql/{pg_version}/main.

#7 Starting the Database Server

Before anyone can access the database, you must start the database server. The database server program is called postgres. The postgres program must know where to find the data it is supposed to use. First, you to start the pgAdmin III GUI, typing


You will be presented with the pgAdmin III interface. Click on the “Add a connection to a server” button (top left). In the new dialog, enter the address, a description of the server, the default databases (i.e. {your-db-dev-name} and {your-db-test-name}), {your-username} and {your-password}. With this GUI you may start creating and managing databases, query the database, execute SQl etc.

#8 Provisioning the Heroku add-on

Heroku Postgres can be attached to a Heroku application via the CLI

heroku addons:add heroku-postgresql:dev

Additionally, you can use heroku addons to see if your application already has a database provisioned and what plan it is:

heroku addons | grep POSTGRES

To see all PostgreSQL databases provisioned by your application and the identifying characteristics of each (db size, status, number of tables, PG version, creation date etc…) use the command

heroku pg:info

Now, to deploy your local database to the Heroku platform simply run

heroku run rake db:migrate

Appendix: FATAL: role “role-name”…

FATAL: role "u8akd9ajka" is not permitted to log in (PG::Error) occurs when you have de-provisioned a starter tier database but are still trying to connect to it. To resolve it, if required, provision a new database via heroku addons:add heroku-postgresql. Use heroku pg:promote HEROKU_POSTGRESQL_<new-database-color> to promote it, making it the primary database for your application.

Questo articolo è stato pubblicato in Blog da r4m . Aggiungi il permalink ai segnalibri.