PostgreSQL setup for Rails development in Ubuntu 12.04

Rails comes with SQLite and it works out of the box with no configuration. That's great! but... its not suitable for a production environment so we need another database if your app is to see the light of day.

You could use SQLite in your development environment and switch it up for production, however, it makes far more sense to use the same database in development and production.

My personal preference is to use PostrgreSQL, its open source and gaining traction in the Rails world due to stability and rather nifty features. Heroku also uses PostgreSQL so its a good option if you plan on using their services.

See the excellent PostgreSQL Documentation for more information.

---

1. Install postgresql and admin tools through the package manager

sudo apt-get install postgresql libpq-dev phppgadmin pgadmin3

Change the postgresql configuration to solve this error
FATAL: Peer authentication failed for user

sudo nano /etc/postgresql/9.1/main/pg_hba.conf

Replace local peer with local md5 in the file. Then reload postgresql to apply the change.

sudo /etc/init.d/postgresql reload

---

2. Setup your databases from the command line

a) Login to postgresql prompt as the postgres user

sudo su postgres -c psql

b) Create a postgresql user for your project

create user username with password 'password';

TIP: If you are running tests, then to ease permission issues setup your postgres user with the same name and password as your Ubuntu user and make him a postgres superuser ( \du lists users with permissions).

alter user username superuser;

c) Create the development and test databases

create database projectname_development;
create database projectname_test;

d) Give permissions to the user on the databases

grant all privileges on database projectname_development to username;
grant all privileges on database projectname_test to username;

e) Finish your postgresql session

\q

Other useful commands in a postgresql session

Update password

alter user username with password ‘new password’;

Destroy database

drop database projectname;

Connect to specific database

\c databasename

Common MySQL commands with postgresql shortcut

MySQL	Postresql
SHOW DATABASES	\l
SHOW TABLES	\d
SHOW COLUMNS	\d table

---

3. Place a database configuration file in your rails project

Save the code below as app/config/database.yml and rake db:migrate your project

development:
  adapter: postgresql
  encoding: unicode
  database: projectname_development
  pool: 5
  username: username
  password: password

test:
  adapter: postgresql
  encoding: unicode
  database: projectname_test
  pool: 5
  username: username
  password: password

---

4. Misc Errors encountered

If you are importing data (or using a seed file) then you may run across this error.
PG::Error: ERROR: duplicate key value violates unique constraint "table_name_pkey"

Your import didn’t use the sequence so we need to reset it to be in sync again.

Note when replacing table_name use a plural.

sudo su postgres -c psql
\c database_name
select setval('table_name_id_seq', (select max(id) + 1 from table_name));