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.
postgres
User Passworddatabase.yml
For RailsPostgreSQL
GemRuby 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:
The most popular and commonly used RDBMS and related, forked projects.
The most advanced, SQL-compliant and open-source objective-RDBMS.
For non-relational database servers:
Cassandra, HBase, etc.
MongoDB, Couchbase, etc
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:
Understanding SQL And NoSQL Databases And Different Database Models
A Comparison Of Relational Database Management Systems
A Comparison Of NoSQL Database Management Systems And Models
Before we begin with building the database layer, let’s see what our final deployment set up will look like.
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. |
| |
+-------------------+
In this article, we are going to create and configure a PostgreSQL database management server on a Ubuntu 13 VPS.
Let’s begin!
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
postgres
User PasswordIn 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:
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.
In this section, we will modify the Rails application servers so that they start working with the database server we have just set up.
database.yml
For RailsDatabase 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.
PostgreSQL
GemStart 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.
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.)@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/