DEV Community

Eric Kahindi
Eric Kahindi

Posted on

Setting Up PostgreSQL on a Virtual Machine

In this guide, we’ll walk through the steps to set up a PostgreSQL server on a virtual machine (VM). This is ideal for anyone learning backend development, data engineering or deploying small projects in a cloud environment.

Creating the Virtual Machine

Choose Your Cloud Provider

Before installing PostgreSQL, we need a VM running on a cloud provider.
While you can use any cloud provider of your choice (AWS, Google Cloud, DigitalOcean, etc.), we'll focus on Microsoft Azure for this tutorial.
Azure provides a generous free tier to test out their products, with a special offer to students who get about $100 in free credit for a whole year.

Create your account

  • Visit the Azure for Students page
  • Sign up using your student email address
  • Verify your student status
  • Complete the registration process

Create a Virtual Machine

  • Log into the Azure Portal
  • Click "Create a resource"
  • Search for "Virtual Machine" and select it
  • Choose your preferred Linux distribution (Ubuntu 20.04 LTS recommended)
  • Select an appropriate VM size (B1s or B2s for testing purposes)
  • Configure authentication (SSH public key recommended)
  • Configure networking settings
  • Review and create your VM

Connect and set up PostgreSQL

Once your VM is running, you can connect to it using SSH:

ssh username@your-vm-public-ip
Enter fullscreen mode Exit fullscreen mode

Install PostgreSQL

Update your system packages first:

sudo apt update
sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

Then install PostgreSQL with the following command

sudo apt install postgresql postgresql-contrib -y
Enter fullscreen mode Exit fullscreen mode

This will install PostgreSQL along with the contrib package for additional functionality that allows us to customise our PostgreSQL:

Start PostgreSQL

First, we start the instance of PostgreSQL, then we enable the instance so that it starts automatically as the server boots, then we check the status to ensure that our changes have taken effect.

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

Log in to PostgreSQL instance

Switch to the default postgres user to use the PostgreSQL command-line interface:

sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode

Launch the PostgreSQL interactive terminal:

psql
Enter fullscreen mode Exit fullscreen mode

Navigate and Use PostgreSQL

Now that we're logged into psql, we can execute commands or sql queries
Here is a list of common ones:
List all available databases

\l
Enter fullscreen mode Exit fullscreen mode

Create a new user:

CREATE USER lux WITH PASSWORD '****';
Enter fullscreen mode Exit fullscreen mode

Give the user superuser privileges:

ALTER USER lux WITH SUPERUSER;
Enter fullscreen mode Exit fullscreen mode

Exit the PostgreSQL shell:

\q
Enter fullscreen mode Exit fullscreen mode

Exit back to your regular Linux user:

exit
Enter fullscreen mode Exit fullscreen mode

Configure PostgreSQL for Remote Access

By default, PostgreSQL only accepts connections from localhost. Let’s change that.

Navigate to the PostgreSQL configuration directory:

cd /etc/postgresql/
Enter fullscreen mode Exit fullscreen mode

Find your PostgreSQL version directory and navigate to it. Then locate the main configuration directory. Then open the postgresql.conf.

sudo nano postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Find the line:

listen_addresses = 'localhost'
Enter fullscreen mode Exit fullscreen mode

Change it to:

listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode

This allows any device to connect to the instance of PostgreSQL; however, you can list out the specific IPs you may want to use if you have a preference
Save and exit the file (Ctrl + X in nano).

Done

You now have a fully functioning PostgreSQL server running on a virtual machine.

Top comments (0)