DEV Community

vishalpaalakurthi
vishalpaalakurthi

Posted on

How to Set Up a PostgreSQL Server on a Virtual Machine

In this tutorial, we'll walk through setting up a PostgreSQL server on a Virtual Machine (VM). We'll use Ubuntu as the operating system for the VM and cover steps for popular cloud providers like AWS, Google Cloud, and Azure. Let's get started!

Step 1: Set Up the VM

  1. Choose a Cloud Provider: AWS, Google Cloud Platform (GCP), or Microsoft Azure.
  2. Create a VM:
    • AWS: Use an EC2 instance.
    • GCP: Use Compute Engine.
    • Azure: Use Virtual Machine service.
  3. Select OS: Choose an Ubuntu LTS version (e.g., Ubuntu 20.04 LTS).

Step 2: Connect to the VM

  1. Access the VM: Use SSH to connect to the VM.

    • Example:
     ssh -i your-key.pem username@your-vm-ip-address
    

Step 3: Update and Upgrade the System

  1. Run the following commands to update and upgrade the system:
   sudo apt update
   sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

Step 4: Install PostgreSQL

  1. Install PostgreSQL:
   sudo apt install postgresql postgresql-contrib -y
Enter fullscreen mode Exit fullscreen mode
  1. Start and Enable PostgreSQL:
   sudo systemctl start postgresql
   sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

Step 5: Configure PostgreSQL

  1. Switch to the PostgreSQL User:
   sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode
  1. Access PostgreSQL Prompt:
   psql
Enter fullscreen mode Exit fullscreen mode
  1. Set a Password for the PostgreSQL User:
   \password postgres
Enter fullscreen mode Exit fullscreen mode

(Enter the new password when prompted)

  1. Create a New Database and User:
   CREATE DATABASE mydatabase;
   CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
   GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
Enter fullscreen mode Exit fullscreen mode
  1. Exit PostgreSQL Prompt:
   \q
Enter fullscreen mode Exit fullscreen mode
  1. Edit PostgreSQL Configuration to Allow Remote Connections:

    • Open the PostgreSQL configuration file:
     sudo nano /etc/postgresql/12/main/postgresql.conf
    
  • Find the line listen_addresses and set it to '*':

     listen_addresses = '*'
    
  • Save and close the file.

    1. Configure Client Authentication:
  • Open the pg_hba.conf file:

     sudo nano /etc/postgresql/12/main/pg_hba.conf
    
  • Add the following line to allow remote connections:

     host    all             all             0.0.0.0/0            md5
    
  • Save and close the file.

    1. Restart PostgreSQL:
   sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Step 6: Allow External Connections to PostgreSQL

  1. Update Firewall Rules:
    • AWS: Edit the Security Group to allow inbound traffic on port 5432.
    • GCP: Edit the Firewall rules to allow traffic on port 5432.
    • Azure: Edit the Network Security Group to allow inbound traffic on port 5432.

Step 7: Connect to PostgreSQL Remotely

  1. Use a PostgreSQL Client: Tools like psql, DBeaver, or pgAdmin can connect to your PostgreSQL server remotely using the VM's public IP address and the credentials you set up.

Example Connection Command

psql -h your-vm-ip-address -U myuser -d mydatabase
Enter fullscreen mode Exit fullscreen mode

(Enter the password when prompted)

Final Notes

  • Ensure your VM's firewall settings allow inbound traffic on port 5432.
  • Secure your PostgreSQL server by following best practices, such as using strong passwords, enabling SSL, and configuring proper firewall rules.

With this setup, you now have a basic PostgreSQL server running on a VM, ready for development or production use. Happy coding!

Top comments (1)

Collapse
 
huzaifi0604 profile image
Muhammad Huzaifa

Very Informative. 👏
I created an article a while back regarding VMs on Azure using SSH as well as RDP for GUI based VMs and hosting websites cost free on them. Do check that out as well.

huzzaifaasim.medium.com/a-beginner...