Setting up PostgreSQL on Ubuntu

In this post, we will setup postgres on Ubuntu machine running on Azure Cloud.

Introduction

There are a few different ways to spin up an PostgreSQL database in Microsoft Azure or other cloud providers. You can use managed services, use docker containers etc.

One option is to spin up a Linux VM and install PostgreSQL directly. This is a good option if you are only going to install PostgreSQL and don’t need a complete stack installed on a machine. In this post, I am going to take this route.

I have an Ubuntu VM running on Azure which I will be using and will install PostgreSQL on it.

I am assuming you are familiar with Ubuntu and how to SSH to it and execute bash commands. You can read about these basics in my previous posts on Ubuntu and Bash which will give you some background about those topics. If you are new to postgres, then I’ve written few posts about it as well.

Without further ado, lets start by installing the postgres database server. First, connect with VM using SSH and then we can run apt-get to install postgres as explained next:

Installing PostgreSQL

You can install postgres using following command:

sudo apt-get -y install postgresql
sudo apt-get -y install postgresql-12

and you will see some output on the terminal during the installation:

Cool. installation is done and lets do a small test to confirm that it is working.

I can switch over to postgres account and use PSQL as shown below:

Exit out of the PostgreSQL prompt by typing:

\q

So, installation of postgres is done and we can use psql tool to work with it. But I was doing it from within the VM itself. I can not connect to it from outside, yet.

Also, you can update password for postgres user as follows:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'sasa';"

However, I would like to connect and access postgres from outside of the VM and work with it e.g. using pgAdmin tool or Azure Data Studio. In order to do that, we have to do some more work and the following sections in this blog post will focus on that part.

Edit postgresql.conf file

You need to open a remote port if you want to access data in the database from a client running on a different computer in the network.

Please check if PostgreSQL is listening on a public port:

The results above (including “127.0.0.1:5432“) shows that PostgreSQL is listening only for connections originating from the local computer, so we will have to edit the “postgresql.conf” configuration file. A result including “0.0.0.0:5432” indicates that PostgreSQL is already listening for remote connections.

Open the file using nano editor:

sudo nano /etc/postgresql/9.5/main/postgresql.conf

scroll to listen_addresses section as show below:

Change from localhost to 0.0.0.0 and also uncomment the line by removing #character in the beginning of line:

With these changes either restart postgresql service by using following command or reboot the ubuntu VM using sudo reboot command:

Restart postgreSQL

sudo service postgresql restart

or
sudo reboot

we also need to make one more configuration change which we will do next.

Authentication Configuration using pg_hba.conf

pg_hba.conf is located in the same directly as postgresql.conf. You can open it using nano editor and please add the following two lines at the end of the file:

This means that remote access is allowed using IP v4 and IP v6 to all databases and all users using the “md5” authentication protocol.

Please “restart postgresql” again.

Open Linux Firewall Port

We also need open the port 5432 and we can do this from Azure portal in Networking tab (Inbound port rules) of our ubuntu virtual machine:

Update postgres password

In order to set the PostgreSQL password for the user “postgres” (or whatever user…) you need to connect locally to the database using an account with administration rights.

Please follow the steps shown earlier during installation test and run the following command to set the password for postgres user:

alter user postgres with password 'yourpassword'; 

Connecting

With all this in place, you can now connect to your database server using pgAdmin or similar tools. I am using Azure Data Studio as shown below:

Click the Advanced… button and enter the IP address and Port of Ubuntu VM:

Now click the Connect button and your connection will be made:

Create a Database

Let’s create a database using ADS and we can do this by running following command:

and here is the output showing that database is created:

Summary

In this post, we setup a postgres database server in an ubuntu virtual machine running on azure cloud. Most of the steps are same with other cloud providers or if you want to setup it on your on hardware. You can also check few other related articles from the links below. If you have any comments or questions, feel free to ask. Till next time, happy coding.

Uninstall Postgres

You can unistall it using following command:

sudo apt-get --purge remove postgresql

This will also prompt you to remove that software that depends on Postgres, which in this case it appears you would like to do.

Related Posts

• https://hexquote.com/getting-started-with-ubuntu-and-bash-shell/
• https://hexquote.com/fun-with-sql-using-postgres-and-azure-data-studio/
• https://hexquote.com/postgresql/

My Recent Books