Setup PostgreSQL for rails

, , Leave a comment

I was working on Ubuntu 16 and faced some real problem to setup postgresql and connect it with my rails application.

Here is what I have done.

PostgreSQL Installation

Ubuntu’s default repository by default contains Postges package. We can easily install postgrs in command line using apt. Moreover, we will install posgresql-contrib which will add additional features.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Postgres Roles

This is an important concept while working with postgres. Postgres uses roles for authentication.

If there is a role in present in postgres, the ubuntu user with the same name will be able to signin as that role to postgres.

While I installed postgres from command line, it automatically created a user named postgres and linked it to the postgres role of the database.

Now we can switch over to postgres user by writing the following line in the terminal –

sudo -i -u postgres

Now we can access the postgresql command line by typing

psql

It will show a command line with postgres=#

Exit the postgres command line with – 

\q

Creating a new role for the ruby on rails application

At first, make sure the postgres user is logged in. If not, use the mentioned command to log in with postgres user in the terminal.

Now create the user with the following command – 

createuser --interactive

It will ask the username. I did put it as ‘kimran’.

It will ask will it be a superuser (y/n). I did put it as ‘y’.

Create database for the new user

By default, postgres will assume there is a database named same as the user name.

For example, If the kimran user tries to connect to postgres, it will automatically try to connect with a database named ‘kimran’.

Let’s do it by the following command –

createdb kimran

Create ubuntu user according to the new role

We have created the role named ‘kimran’ and a database named ‘kimran’.

Now, we will have to create an ubuntu user with the same name i.e. ‘kimran’. 

sudo adduser kimran

Now switch to that new user and connect to the new poostgres prompt.

sudo -i -u kimran
psql

Add a password for the kimran role. Otherwise you will not be able to connect to the rials application. Do it by the following command – 

\password kimran
Enter new password:

Now check your connection status – 

\conninfo

It will show something like this – 

You are connected to database "kimran" as user "kimran" via socket in "/var/run/postgresql" at port "5432".

Set up databse.yml in your Ruby on Rails App

Now we will have to modify database.yml file according to the information we got from the ‘conninfo’ command in prompt.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5

development:
  <<: *default
  database: test_project_development
  username: kimran
  password: password
  socket: /var/run/postgresql
  host: localhost
  port: 5432

test:
  <<: *default
  database: test_project_test
  username: khaled
  password: password
  socket: /var/run/postgresql
  host: localhost
  port: 5432

production:
  <<: *default
  database: test_project
  username: kimran
  password: password
  socket: /var/run/postgresql
  host: localhost
  port: 5432

Create the project Database

Now we can just create the database from tarminal. Go to the root of the project.

cd test_project

Now run the rake command to create the database

rake db:create

It should now create the database in postgresql.

Now run the migration files – 

rake db:migrate

Conclusion

Well, now you are ready to go with Postgres for your rails application. Do you think it can be done in a better way? Please comment if you think so.

 

Leave a Reply