Tutorial

Scaling Ruby on Rails: Setting Up A Dedicated PostgreSQL Server (Part 3)

Published on February 27, 2014
Default avatar

By O.S. Tezer

Scaling Ruby on Rails: Setting Up A Dedicated PostgreSQL Server (Part 3)

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:

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>

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors
Default avatar
O.S. Tezer

author

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
6 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

The settings in pg_hba.conf don’t allow the rails console to work from the app server. I’m trying to find out what I need to do, but haven’t found anything that clearly explains that part.

Completely agree with @ademola -

On the other hand, that would cause a bottleneck for a highly scalable app deployment (think, you need to spin-up a new app server every week).

A better setup would be to allow only access from the Private Network. At least that would block attacks from the outside world (i.e. from servers hosted outside of your DigitalOcean region - a clever enough someone could still access your droplet if they SSH from a droplet within the same region.)

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
July 22, 2014

@ravikanth.as400: Take a look at the massive_record gem.

Could you please publish how to connect to Hbase with Rails. I am working on that. It would be helpful for me.

I am worried about allowing all connections through with the rule below

TYPE DATABASE USER ADDRESS METHOD

host all all 0.0.0.0/0 md5

It would be better to indicate a typical rule than showing 0.0.0.0/0 which translates to “allow every Tom, Dick and Harry to attempt to break me”.

If the droplet IP Address is 43.23.122.2 then the address should be 43.23.122.2/32

The links for understanding databases, relational databases, and NoSQL are all breaking in the body of this guide.

They’re showing up as

https://link_to_10_3_nosql_comparison/

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel