Learn how to use Post­gr­eSQL with your Ruby on Rails ap­plic­a­tion, instead of the default SQLite database. SQLite is an easy-to-configure, light­weight product which ships with Ruby on Rails by default. However, Post­gr­eSQL is a more robust solution which provides more advanced features, scaling, and stability, which may make it more suitable for your Ruby on Rails project.

Re­quire­ments

  • A Cloud Server running Linux (Ubuntu 16.04)
  • Post­gr­eSQL installed and running.
  • Ruby on Rails installed and running.
  • A basic fa­mili­ar­ity with Ruby on Rails.
Note

All of the commands in this tutorial must be issued as the Rails user. This is the user account which you used to install and run Ruby on Rails.

Create a Post­gr­eSQL user

Create a Post­gr­eSQL user so that your Ruby on Rails ap­plic­a­tion will be able to connect to the Post­gr­eSQL database:

sudo -u postgres createuser -s [username]
Note

This should be the same username which you used to install and run Ruby on Rails.

For example, to create the Post­gr­eSQL username jdoe the command is:

sudo -u postgres createuser -s jdoe

To set a password for this user, log in to the Post­gr­eSQL command line client:

sudo -u postgres psql

At the Post­gr­eSQL prompt, enter the following command to set the password:

\password [username]

For example, to set the password for jdoe the command is:

\password jdoe

Enter and confirm the password. Then exit the Post­gr­eSQL client:

\q
VPS Hosting
VPS hosting at un­beat­able prices on Dell En­ter­prise Servers
  • 1 Gbit/s bandwidth & unlimited traffic
  • Minimum 99.99% uptime & ISO-certified data centres
  • 24/7 premium support with a personal con­sult­ant

Configure the Rails Ap­plic­a­tion

The next step is to enable Post­gr­eSQL support in your Ruby on Rails ap­plic­a­tion.

Create the Ap­plic­a­tion

First, create the ap­plic­a­tion using the -d post­gr­esql flag:

rails new [application name] -d postgresql

For example, the command to create an ap­plic­a­tion named my-app is:

rails new my-app -d postgresql

The -d flag tells Ruby on Rails that you will be using Post­gr­eSQL for this ap­plic­a­tion.

Add the Post­gr­eSQL username and password

Next, move into the directory which Ruby on Rails created for the ap­plic­a­tion:

cd my-app

Edit the config/database.yml file:

nano config/database.yml

Scroll down to the section which reads:

# The specified database role being used to connect to postgres.
# To create additional roles in postgres see `$ createuser --help`.
# When left blank, postgres will use the default role. This is
# the same name as the operating system user that initialized the database.
#username: my-app2

Delete the # in the last line to un-comment it, and change the username to the one you created:

# The specified database role being used to connect to postgres.
# To create additional roles in postgres see `$ createuser --help`.
# When left blank, postgres will use the default role. This is
# the same name as the operating system user that initialized the database.
username: jdoe

In the next section, delete the # to un-comment the last line, and add the password for the jdoe user:

# The password associated with the postgres role (username).
password: XPmMxZf

Save and exit the file.

Create the new ap­plic­a­tion databases

Use the following rake command to create the databases for your ap­plic­a­tion:

rake db:create

Test the con­fig­ur­a­tion

To test the con­fig­ur­a­tion, simply start the rails ap­plic­a­tion and check it in a browser.

From the ap­plic­a­tion's directory, use the command:

bin/rails s --binding=0.0.0.0
Note

Binding the server to 0.0.0.0 allows you to view the ap­plic­a­tion using your server's public IP address.

The server should respond with:

[user@localhost my-app]$ bin/rails server
=> Booting Puma
=> Rails 5.0.0.1 application starting in development on http://localhost:3000
=> Run `rails server -h` for more startup options
Puma starting in single mode...
* Version 3.6.0 (ruby 2.3.1-p112), codename: Sleepy Sunday Serenity
* Min threads: 5, max threads: 5
* Environment: development
* Listening on tcp://localhost:3000
Use Ctrl-C to stop

Switch to a browser and visit "http://your-ip-address:3000". For example, if your IP address is 198.162.0.1 you would go to http://198.162.0:3000.

If all is well and Rails is able to connect to Post­gr­eSQL, you will see the default Rails welcome page.

Go to Main Menu