Setting up a PostgreSQL database on an Ubuntu instance

By Sanni Kehinde, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

In this guide, we will install and set up a PostgreSQL database on an Ubuntu instance using Alibaba Cloud Elastic Compute Service (ECS).

But before we begin, it is important to know that there are different ways to set up a PostgreSQL database on any cloud provider. For Alibaba Cloud, you can create an instance and set it up manually using Elastic Compute Service (ECS) or by using ApsaraDB RDS.

So before I proceed with the guide, I would point out some few differences between using an ECS or ApsaraDB RDS for your database.

For ECS

You have an instance which you can SSH into
You are free to install any software of your choice but you are responsible for the license validity.
You have full control over the configuration of the database, including any performance tweaking you want to do.
You own the responsibility of the DB's uptime and health as it becomes an app running in the ECS.
You have to do full system administration yourself, including OS maintenance, security, patches, etc.

For ApsaraDB RDS (Relational Database Service)

You don't need to worry about the Database health or uptime, as everything is already been taken care of by Alibaba Cloud
You can't SSH into ApsaraDB RDS DB instance but you can connect to the RDS instance via any internet enabled system or application provided that the security group has been enabled
You don't need to bother about any license validity for the database.
Automatic backup is also taken care of.

Prerequisites:

To follow along with this guide, you need to have an instance installed with Ubuntu OS. You can also check this guide on How to create an instance.

Procedure

After completing the prerequisites, follow the steps below

SSH into your instance using your key pair for Linux user. For windows, you can use SSH client such as putty to connect to your instance.
We have to update & upgrade our Ubuntu to latest packages using the commands below
```
sudo apt-get update && sudo apt-get upgrade
```
Install PostgreSQL by running the command below
```
sudo apt-get install postgresql
```
To check the version of PostgreSQL installed run psql -V
We are going to edit the PostgreSQL configuration file to change the address.
To edit the configuration file(pg_hba.conf), run sudo vim /etc/postgresql/9.5/main/pg_hba.conf.

9.5 is the version of postgreSQL installed. As at the time of writing this article,

9.5 is the default installation for postgreSQL on ubuntu OS.

After opening our pg_hba.conf in vim, you can use any editor of your choice. Update the file which read something like this

by default

 # TYPE  DATABASE        USER            ADDRESS                 METHOD

 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5

 # TYPE  DATABASE        USER            ADDRESS                 METHOD

 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
 host    all             all             0.0.0.0/0               md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5

This would enable us connect to our database instance.

We need to update the postgresql.conf file to enable remote connection from anywhere(IP address).
To edit postgresql.conf file run sudo vim /etc/postgresql/9.5/main/postgresql.conf. Use :set number to enable the line numbers and look for line 59 which should be like this
```
#listen_addresses = 'localhost'
```
Update it to this
```
 listen_addresses = '*'
```
To make an effect on the changes made, we need to restart the PostgreSQL service. We can do that by running the command below
```
sudo /etc/init.d/postgresql restart
```
Now that our PostgreSQL database is ready. we can log into PostgreSQL and create a username and a database.
```
sudo - su postgres
psql
```
The psql command gives us access to the Postgres interactive terminal
Create a username by running the command below
```
CREATE USER ubuntu_user SUPERUSER;
```
We have to create a password for the user with the command below
```
ALTER USER power_user WITH PASSWORD 'password';
```

To create a database with the user created, run

CREATE DATABASE mydatabase WITH OWNER power_user;

To exit from psql shell, run \q. We can view the content of our database and make changes to it with psql. We can also connect to our database using a database client.

Connect to database via database client

We would be using a database client called postico. you can use any database client of your choices.

Click on new favourite to add your connection parameters
Fill in the field box

The field parameters in the screenshot above are explained below
1. The nickname field can be anything
2. The Host field contains the public IP address of our ECS instance
3. The User field contains the username we created earlier which is ubuntu_user
4. The password field is for the password we created for the ubuntu_user which is password
5. The database field is for the database we created earlier which is mydatabase.

Once you have successfully connect to your database instance, you should see the database we created. From the database client, you can perform basic CRUD(Create, Read, Update and Delete) operations. Irrespective of the database client you are using, the parameters would always be the same.

Conclusion

With ApsaraDB RDS you can get a database instance up and running in minutes. But if you are looking for full control such as configuration and security, then Elastic Compute Service (ECS) is definitely the way to go.

: Raja_KT February 16, 2019 at 6:03 am

Good one.... True...if you are looking for full control such as configuration and security, then Elastic Compute Service (ECS) is definitely the way to go.

1 0

: Dikky Ryan Pratama May 5, 2023 at 4:08 am

I was impressed with the depth of analysis and attention to detail in your article.

1 0

Community

Setting up a PostgreSQL database on an Ubuntu instance

Prerequisites:

Procedure

Connect to database via database client

Conclusion

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Raja_KT February 16, 2019 at 6:03 am

Dikky Ryan Pratama May 5, 2023 at 4:08 am

Alibaba Clouder

Related Products

ECS(Elastic Compute Service)

OSS(Object Storage Service)