Setup Rails with PostgreSQL

Setup Rails with PostgreSQL

In my previous Ubuntu install Rails post, I had installed Ruby on Rails environment in Ubuntu 18.04 but utilizing SQLite. Now I am trying to connect Ruby on Rails to PostgreSQL. To install PostgreSQL, refer to my Ubuntu install PostgreSQL post.

Firstly, we need to install PostgreSQL gem and log in as postgres user.

gem install pg
sudo su - postgres
psql

Create a role

From within PostgreSQL, run the command below to create a new role. The du command to check the newly created role and q to quit the PostgreSQL prompt. Lastly exit the postgres user back to ubuntu user.

create role blog with createdb login password 'password1';
\du
\q
exit

Create a project

Now we will create a project called “blog” and have it connect to the PostgreSQL database. Let’s reuse the same project folder structure from my previous Ubuntu install Rails post.

cd ~/ror
rails new blog --database=postgresql
cd blog
vim config/database.yml
setup rails with postgresql
Your configuration should look similar to this.

Setup a new database

Run the command below after successfully configure your database.yml file.

rake db:setup
setup rails with postgresql
You should be seeing similar result upon successful setup.

Create CRUD scaffold

Create a scaffold with CRUD (create, read, update, delete) to see if you are able to store into the PostgreSQL database.

rails g scaffold Post title:string body:text
rake db:migrate
rails server
setup rails with postgresql
The index page of all the posts.
setup rails with postgresql
Creating a new post.
setup rails with postgresql
Successfully created a new post.
setup rails with postgresql
Index page showing the new post.

Verification

setup rails with postgresql
Successfully querying PostgreSQL using pgAdmin to verify the post created.

 

 

Ubuntu install PostgreSQL

Ubuntu install PostgreSQL

The Ubuntu version used in this example is Ubuntu Server 18.04 in Amazon AWS EC2. Change user to root and as always prior to installation, run update first then install PostgreSQL.

sudo apt update
sudo apt install postgresql postgresql-contrib libpq-dev -y

Next add user postgres to sudo group and change user to postgres.

sudo usermod -a -G sudo postgres
sudo su - postgres

Now run psql command, change the password for postgres and quit.

psql
\password postgres
\q
exit

Connecting to PostgreSQL with pgAdmin through SSH tunnel

I assume you have pgAdmin installed. To connect, click add new server and refer to the screenshots below.

ubuntu install postgresql
Click “add new server” to connect to the database installed earlier.
ubuntu install postgresql
Give your connection a name.
ubuntu install postgresql
The host address is 127.0.0.1 and key in the password you configured earlier.
ubuntu install postgresql
The tunnel host is the Ubuntu server 18.04 hosted in Amazon AWS EC2. Enter the password to your EC2 instance.

Once click [Save]. You will be connected to your PostgreSQL database.

Connecting to PostgreSQL with pgAdmin directly

PostgreSQL by default is not listening to external connections. You’ll need to allow remote connections. To do that, change user to root and edit the configuration file. Your configuration file path might differ from mine depending on which version of Postgres you install. Mine is version 10.

sudo vim /etc/postgresql/10/main/pg_hba.conf

Modify the config file to look like mine below.

ubuntu install postgresql

sudo vim /etc/postgresql/10/main/postgresql.conf
ubuntu install postgresql
Uncomment the listen_addresses by removing the hash (#) prefix and replace the value with *
sudo service postgresql restart
ubuntu install postgresql
Whitelisting port 5432 for your IP address at AWS EC2 security group.
ubuntu install postgresql
I named this connection as direct. You can name it differently.
ubuntu install postgresql
The host address is the address of the AWS EC2 Ubuntu instance.