DEV Community

vishalpaalakurthi
vishalpaalakurthi

Posted on

23

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!

Image of Datadog

The Future of AI, LLMs, and Observability on Google Cloud

Datadog sat down with Google’s Director of AI to discuss the current and future states of AI, ML, and LLMs on Google Cloud. Discover 7 key insights for technical leaders, covering everything from upskilling teams to observability best practices

Learn More

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...

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more