Introduction

---

Previously, we have covered various ways of deploying Ruby-on-Rails applications (e.g. Rails with Unicorn & Nginx, Rails with Passenger & Nginx), setting up a scalable server structure and learned how to connect a dedicated MySQL instance to our Rails application servers.

In this installation of our DigitalOcean Scaling-Rails series, we are going to find out how to build a PostgreSQL server from scratch to use as the database persistence layer for Ruby-on-Rails web-applications. Continuing, we are going to see how to connect our Rails application servers with the database by making the necessary configuration changes.

Note: This article, as we have mentioned, is part of our Scaling-Rails series and consists of installing PostgreSQL server on a dedicated Ubuntu VPS. However, you can very well install PostgreSQL, the exact same way explained here, to use on a single virtual server together with your Rails application. In order to see how to deploy Rails on a single droplet using Unicorn or Passenger, please click the links provided on the first paragraph and then continue from here to form the database layer.

Glossary

---

1. Choosing A Database

---

2. Server Set-Up Structure

---

1. Load-Balancing Multiple Application Servers
2. The Database Server Layer

3. Adding The Database Server To The Deployment Set-Up

---

1. Preparing The Server

4. Installing PostgreSQL

---

1. Adding The PostgreSQL Software Repository
2. Installing PostgreSQL

5. Configuring PostgreSQL

---

1. Changing The Default postgres User Password
2. Creating Roles And Databases
3. Enabling Remote Connections

6. Configuring Rails Applications

---

1. Configuring database.yml For Rails
2. Getting The PostgreSQL Gem

Choosing A Database

---

Ruby on Rails application development framework provides a large array of support for database servers. For a majority of applications, a relational database management system is the way to go. However, some might require a non-relational, schema-less NoSQL database server – either instead of the relational one or both running together.

When you begin working with Rails on your own development computer, the simplest and probably the most logical way is to start with using a capable but basic database implementation [such as the SQLite library]. However, for real-world deployments, chances are SQLite would be insufficient to handle your application load [thus requiring a full-fledged RDBMS].

Depending on your needs and application type, you need to decide on a database management system (i.e. a database server) to create the database layer of your application deployment set-up.

For relational databases some of the more popular choices are:

• PostgreSQL and derivatives:

The most popular and commonly used RDBMS and related, forked projects.

• PostgreSQL:

The most advanced, SQL-compliant and open-source objective-RDBMS.

For non-relational database servers:

• Column based:

Cassandra, HBase, etc.

• Document:

MongoDB, Couchbase, etc

• Graph:

OrientDB, Neo4J, etc.

In order to make a clear and long-term decision before continuing with deploying a database server, you might be interested in reading our articles on the subject:

• Introduction To Databases:

Understanding SQL And NoSQL Databases And Different Database Models

• Relational:

A Comparison Of Relational Database Management Systems

• NoSQL:

A Comparison Of NoSQL Database Management Systems And Models

Server Set-Up Structure

---

Before we begin with building the database layer, let’s see what our final deployment set up will look like.

Load-Balancing Multiple Application Servers

---

Previously, after creating a load-balancer / reverse-proxy with multiple application servers, this is what we had in the end:

Three droplets with each having a distinct role:
------------------------------------------------
1 x Load-Balancer / Reverse-Proxy
2 x Application Servers Running Your Rails Web-Application / API

                             ---

                    DEPLOYMENT STRUCTURE

             +-------------------------------+
             |                               |
             | LOAD-BALANCER / REVERSE PROXY |
             |                               |
             +-------------------------------+
                             +
                             |
                             |
        +---------------+    |    +---------------+
        |  APP  SERVER  |    |    |  APP  SERVER  |
        |---------------|    |    |---------------|
        |               |    |    |               |
        |     RAILS     |<---+--->|     RAILS     |
        |               |         |               |
        +---------------+         +---------------+

The Database Server Layer

---

In order to have a centrally accessible database server (e.g. a RDBMS and/or NoSQL database), we will add a 4th element to our server set-up:

Four droplets:
------------------------------------------------
1 x Load-Balancer / Reverse-Proxy
2 x Application Servers Running Your Rails Web-Application / API
1 x Database Server (e.g. PostgreSQL, PostgreSQL, MongoDB etc.)

             +-------------------------------+
             |                               |
             | LOAD-BALANCER / REVERSE PROXY |
             |                               |
             +-------------------------------+
                             +
                             |
                             |
        +---------------+    |    +---------------+
        |  APP  SERVER  |    |    |  APP  SERVER  |
        |---------------|    |    |---------------|
        |               |    |    |               |
        |     RAILS     |<---+--->|     RAILS     |
        |               |         |               |
        +---------------+         +---------------+
                +                         +
                |                         |
                |  +-------------------+  |
                |  |  DATABASE SERVER  |  |
                |  |-------------------|  |
                |  |                   |  |
                |  |       PostgreSQL,      |  |
                +->|     PostgreSQL,   |<-+
                   |        etc.       |
                   |                   |
                   +-------------------+

Adding The Database Server To The Deployment Set-Up

---

In this article, we are going to create and configure a PostgreSQL database management server on a Ubuntu 13 VPS.

Let’s begin!

Preparing The Server

---

Update the software sources list and upgrade the dated applications:

aptitude    update
aptitude -y upgrade

Run the following command to install build-essential package:

aptitude install -y build-essential

Run the following command to install some additional, commonly used tools:

aptitude install -y cvs subversion git-core mercurial

Installing PostgreSQL

---

Adding The PostgreSQL Software Repository

---

In order to download the latest version of PostgreSQL (9.3), we need to add the repository to aptitude sources list.

Run the following command to create a sources list for PostgreSQL:

nano  /etc/apt/sources.list.d/pgdg.list

Copy-and-paste the below contents:

deb http://apt.postgresql.org/pub/repos/apt/ saucy-pgdg main

Save and exit by pressing CTRL+X and confirming with Y.

Note: We are assuming that you are working with Ubuntu 13 (saucy). If you are using a different version, run lsb_release -c to find out your distribution’s name and replace it with saucy in the instructions above.

Update the sources list to include the new additions:

aptitude    update
aptitude -y upgrade

Installing PostgreSQL

---

Since now we have access to the source, using the default package manager aptitude (or apt-get) we can directly install the latest available version of PostgreSQL.

Run the following command to install PostgreSQL v. 9.3:

aptitude install postgresql-9.3 pgadmin3 

Configuring PostgreSQL

---

Changing The Default postgres User Password

---

In order to work with the database, we need to change the default password.

Run the following command to initiate the process:

sudo -u postgres psql postgres

Once you see the prompt similar to postgres=#, type the following:

\password postgres

Enter your password, re-enter again to verify and press CTRL+Z or type \q to exit.

Creating Roles And Databases

---

Login to PostgreSQL using the following command:

sudo -u postgres psql

And run the instructions given below to create a role and a database to be used by Rails:

# Usage: CREATE USER [user name] WITH PASSWORD '[password]';
# Example:
CREATE USER rails_myapp_user WITH PASSWORD 'pwd';

# Usage: CREATE DATABASE [database name] OWNER [user name];
# Example:
CREATE DATABASE rails_myapp OWNER rails_myapp_user;

Press CTRL+Z or type \q to exit.

Note: To learn about PostgreSQL roles and management, check out the following articles:

• How To Create, Remove, & Manage Tables in PostgreSQL

• How To Use Roles and Manage Grant Permissions in PostgreSQL

Enabling Remote Connections

---

Since we need PostgreSQL server to be accessible from remote computers running the Rails application, the configuration file must be modified.

Run the following command to edit the PostgreSQL configuration postgresql.conf using the nano text editor:

nano /etc/postgresql/9.3/main/postgresql.conf

We would like to tell PostgreSQL to listen to connections from the IP address assigned to our droplet.

Scroll down the file and find the following line:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

And save and exit by pressing CTRL+X and confirming with Y.

Next, we need to tell PostgreSQL the specific connections we would like it to accept, similarly to how firewalls work.

Run the following command to edit the PostgreSQL hba file pg_hba.conf using the nano text editor:

nano /etc/postgresql/9.3/main/pg_hba.conf

Scroll down the file and find the section:

# Put your actual configuration here
# ..

After the comment block, append the following line:

# TYPE   DATABASE      USER        ADDRESS        METHOD
host        all        all        0.0.0.0/0        md5

And again, save and exit by pressing CTRL+X and confirming with Y.

Restart the PostgreSQL daemon with the following command:

service postgresql restart

#  * Restarting PostgreSQL 9.3 database server
# ...done.

Configuring Rails Applications

---

In this section, we will modify the Rails application servers so that they start working with the database server we have just set up.

Configuring database.yml For Rails

---

Database settings for Rails applications are kept inside the database.yml file in /config directory.

Run the following command to edit the database.yml file using the nano text editor:

# Make sure to enter your application deployment directory
# Example:
# cd /var/www/my_app

nano config/database.yml

Once you open up this file, you will see database settings divided by environment names. Since an application needs to run using the production environment, let’s edit the configuration for that.

Replace the production: YML code block with the following, changing the necessary bits to suit your own set-up configuration, e.g. the IP address etc.

# Example:
# production:
#   adapter: postgresql
#   encoding: utf8
#   database: [database name]
#   username: [user name]
#   password: [password]
#   host: [server IP address]
#   port: [port number]
#   protocol: [protocol]
#   pool: [connection pool]

production:
  adapter: postgresql
  encoding: utf8
  database: rails_myapp
  username: rails_myapp_user
  password: pwd
  host: 128.199.233.36
  port: 5432
  pool: 10

Note: As provided in the example above, you might need to specify the protocol.

Note: The pool argument contains the number of maximum simultaneous database connection slots (i.e. pool) available. You need to assess your needs and set a number accordingly.

Save and exit by pressing CTRL+X and confirming with Y.

Getting The PostgreSQL Gem

---

Start editing the Gemfile using nano using the following:

nano Gemfile

Add the following line to the file:

gem 'pg'

Save and exit by pressing CTRL+X and confirming with Y.

Install the new gem using bundle:

bundle install

And that’s it! From now on, your Rails application servers will be using your brand new database server for all operations.

<div class=“author”>Submitted by: <a href=“https://twitter.com/ostezer”>O.S. Tezer</a></div>