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

1. Preparing The Server

1. Adding The PostgreSQL Software Repository
2. Installing PostgreSQL

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

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

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     |
        |               |         |               |
        +---------------+         +---------------+

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.       |
                   |                   |
                   +-------------------+

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

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

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 

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.

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

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.

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.

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>