Tutorial

How To Use PostgreSQL with Your Ruby on Rails Application on Ubuntu 14.04

How To Use PostgreSQL with Your Ruby on Rails Application on Ubuntu 14.04
Not using Ubuntu 14.04?Choose a different version or distribution.
Ubuntu 14.04

Introduction

Ruby on Rails uses sqlite3 as its default database, which works great in many cases, but may not be sufficient for your application. If your application requires the scalability, centralization, and control (or any other feature) that is provided by a client/server SQL database, such as PostgreSQL or MySQL, you will need to perform a few additional steps to get it up and running.

This tutorial will show you how to set up a development Ruby on Rails environment that will allow your applications to use a PostgreSQL database, on an Ubuntu 14.04 server. First, we will cover how to install and configure PostgreSQL. Then we’ll show you how to create a rails application that uses PostgreSQL as its database server.

Prerequisites

This tutorial requires that have a working Ruby on Rails development environment. If you do not already have that, you may follow the tutorial in this link: How To Install Ruby on Rails with rbenv on Ubuntu 14.04.

You will also need to have access to a superuser, or sudo, account, so you can install the PostgreSQL database software.

Once you’re ready, let’s install PostgreSQL.

Install PostgreSQL

If you don’t already have PostgreSQL installed, let’s do that now.

First, update apt-get:

  1. sudo apt-get update

Then install PostgreSQL and its development libraries:

  1. sudo apt-get install postgresql postgresql-contrib libpq-dev

PostgreSQL is now installed but you should create a new database user, that your Rails application will use.

Create Database User

Create a PostgreSQL superuser user with this command (substitute the highlighted word with your own username):

  1. sudo -u postgres createuser -s pguser

If you want to set a password for the database user, enter the PostgreSQL console with this command:

  1. sudo -u postgres psql

The PostgreSQL console is indicated by the postgres=# prompt. At the PostgreSQL prompt, enter this command to set the password for the database user that you created:

  1. \password pguser

Enter your desired password at the prompt, and confirm it.

Now you may exit the PostgreSQL console by entering this command:

  1. \q

Let’s create a Rails application now.

Create New Rails Application

Create a new Rails application in your home directory. Use the -d postgresql option to set PostgreSQL as the database, and be sure to substitute the highlighted word with your application name:

  1. cd ~
  2. rails new appname -d postgresql

Then move into the application’s directory:

  1. cd appname

The next step is to configure the application’s database connection.

Configure Database Connection

The PostgreSQL user that you created will be used to create your application’s test and development databases. We need to configure the proper database settings for your application.

Open your application’s database configuration file in your favorite text editor. We’ll use vi:

  1. vi config/database.yml

Under the default section, find the line that says “pool: 5” and add the following lines under it. It should look something like this (replace the highlighted parts with your PostgreSQL user and password):

config/database.yml excerpt
  host: localhost
  username: pguser
  password: pguser_password

Save and exit.

Create Application Databases

Create your application’s development and test databases by using this rake command:

  1. rake db:create

This will create two databases in your PostgreSQL server. For example, if your application’s name is “appname”, it will create databases called “appname_development” and “appname_test”.

If you get an error at this point, revisit the previous subsection (Configure Database Connection) to be sure that the host, username, and password in database.yml are correct. After ensuring that the database information is correct, try creating the application databases again.

Test Configuration

The easiest way to test that your application is able to use the PostgreSQL database is to try to run it.

For example, to run the development environment (the default), use this command:

  1. rails server

This will start your Rails application on your localhost on port 3000.

If your Rails application is on a remote server, and you want to access it through a web browser, an easy way is to bind it to the public IP address of your server. First, look up the public IP address of your server, then use it with the rails server command like this (substitute it for the highlighted part):

  1. rails server --binding=server_public_IP

Now you should be able to access your Rails application in a web browser via the server’s public IP address on port 3000:

Visit in a web browser:
http://server_public_IP:3000

If you see the “Welcome aboard” Ruby on Rails page, your application is properly configured, and connected to the PostgreSQL database.

Conclusion

You’re now ready to start development on your Ruby on Rails application, with PostgreSQL as the database, on Ubuntu 14.04!

Good luck!

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

Learn more about our products

About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 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!

I’ve written an article on installing PostgreSQL, Rails 5 and Ubuntu, updated for 2019, on Mac. Maybe some of the details could be useful, feel free to drop me a line via Twitter.

If you have a firewall set up, make sure the port your server is running on is open.; otherwise, you won’t be able to connect.

If you’re using ufw and the default rails server port (3000), run the following command:

sudo service ufw allow 3000/tcp

You can check which ports are open with

sudo ufw status

I am wondering if Rails can use PostgreSQL without setting the superuser password and if this password can and ought to be different from my Ubuntu account password: why in other words it should be important a superuser username matching my Ubuntu login name and on the other hand a different password? database.yml records exactly this kind of sensitive information and is pushed to a Git repository hosting web site and to Heroku.

Also, according to Heroku it is necessary “to export the DATABASE_URL environment variable for your app to connect to it when running locally”, with:

export DATABASE_URL=postgres:///$(whoami)

Is that really necessary?

You didnt mention that we should change from the default sqlite adapter.

After I install postgresql from APT and try creating a new database user I get the following message:

PG::ConnectionBad could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?

To make it work, I have to run the following command manually every time.

sudo service postgresql start

Is there anyway to avoid entering the command every time ?

Thanks!!! , good explained and works

It really works, thank you for your helping and it works in centOS or fedora too. It isn’t just for ubuntu or debian.

After

sudo apt-get install postgresql postgresql-contrib libpq-dev

I get the following error

The PostgreSQL server failed to start. Please check the log output:
2015-08-13 07:26:07 EDT FATAL:  could not map anonymous shared memory: Cannot allocate memory
2015-08-13 07:26:07 EDT HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 147783680 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.```

I run rails server --binding=server_public_IP when I try to access my server from a browser, nothing happens, but there is an error message in the console: Cannot render console from [IP_ADDRESS]! Allowed networks: 127.0.0… i add it as exceptions: class Application < Rails::Application config.web_console.whitelisted_ips = ‘10.0.2.2’ end

then there was an error: ERROR Errno::ECONNRESET: Connection reset by peer @ io_fillbuf - fd:12

I saw a comment there should be gem ‘thin’ as a server. I have installed it.

but after that there still nothing happens in the browser, but the console shows requests and that everything is working well. Have someone faced such a problem?


UPDATE

Somehow it does not work on the port 3000, but works on the port 80! =)

After I install postgresql from APT and try creating a new database user I get the following message:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Can somebody help with this? I had the same problem on my local machine though I don’t remember how I fixed it.

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!

Featured on Community

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