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!