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

Step 4: Install PostgreSQL

1. Install PostgreSQL:

   sudo apt install postgresql postgresql-contrib -y

1. Start and Enable PostgreSQL:

   sudo systemctl start postgresql
   sudo systemctl enable postgresql

Step 5: Configure PostgreSQL

1. Switch to the PostgreSQL User:

   sudo -i -u postgres

1. Access PostgreSQL Prompt:

   psql

1. Set a Password for the PostgreSQL User:

   \password postgres

(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;

1. Exit PostgreSQL Prompt:

   \q

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

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