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'
end

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

Run

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:

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

test:
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

pgadmin3

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 127.0.0.1, 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.
  • rcdub

    This is a horrible guide. Why the hell wouldn’t you use ident authentication.

    • Could you kindly argue why it is so horrible?
      Regarding the ident auth, the pwd auth is easily configurable into the Rails database.yml file. I do not see why I should shift to ident auth locally.

      • rcdub

        Firstly, I must apologize for the tone of that statement. Using ident auth is always preferred for local installations because it removes external requirements on your configuration, and is more secure because it only allows access to that database user locally.

        Usually the only argument against ident auth is that if someone gets on to that user account, then your database is compromised. That being said there should be measures in place to prevent that. Duplicate authentication for a local database and a local application ran by its’ own user violates the DRY principle and is redundant. Also setting up ident auth is really really easy. Whereas this (at least as its written) is confusing.

        I got to your guide from someone asking a question on why x isnt working when they were attempting to follow it on #rubyonrails on irc.freenode.net

        Setting up psql is as simple as this (assuming your on a distro with sudo) to allow access to an application running on user “disqus”. E.g. this is a user set up for the sole purpose of running the disqus application, therefore only it should need access to the database, excluding debugging purposes in which case all superusers/developers have access anyway.

        Logged in as disqus, do the following:

        sudo su postgres
        createuser disqus
        createdb disqus
        logout
        psql

        a database.yml file would look like:

        development:
        adapter: postgresql
        database: disqus
        min_messages: warning

        If disqus has the create database permission, they can create whatever dbs they need with rake db:create

        That’s it. The last psql command ran by disqus will allow access to the database according to the privileges defined during the createuser step and removes having to change database.yml/use hacky env variables.

        I hope this explains my position better.

        • rcdub, thank you for your clarifications. You give a useful contribute to whom are gonna read through this blog post. Cheers

  • Connor James Leech

    Could you please provide more details on using the pgadmin interface?

    I have a database, user and password all with the same name

    • Hi James, as you are showing in the attached screenshot, the first step to do is creating a new server. To do that it is enough to set the name of the server, e.g. “my local server” and then the host, which is 127.0.0.1, the localhost. After that you have to connect to the server to create the corresponding databases. You can do that right-clicking onto the “Database” tree section that appears when you start the server. You have to create the two databases that I mentioned in the blog post, i.e. {your-db-dev-name} and {your-db-test-name} associating them to your postgres account.

      • Connor James Leech

        Thank you!