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
Install PostgreSQL
Update your system packages first:
sudo apt update
sudo apt upgrade -y
Then install PostgreSQL with the following command
sudo apt install postgresql postgresql-contrib -y
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
Log in to PostgreSQL instance
Switch to the default postgres user to use the PostgreSQL command-line interface:
sudo -i -u postgres
Launch the PostgreSQL interactive terminal:
psql
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
Create a new user:
CREATE USER lux WITH PASSWORD '****';
Give the user superuser privileges:
ALTER USER lux WITH SUPERUSER;
Exit the PostgreSQL shell:
\q
Exit back to your regular Linux user:
exit
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/
Find your PostgreSQL version directory and navigate to it. Then locate the main configuration directory. Then open the postgresql.conf.
sudo nano postgresql.conf
Find the line:
listen_addresses = 'localhost'
Change it to:
listen_addresses = '*'
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)