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
- Choose a Cloud Provider: AWS, Google Cloud Platform (GCP), or Microsoft Azure.
-
Create a VM:
- AWS: Use an EC2 instance.
- GCP: Use Compute Engine.
- Azure: Use Virtual Machine service.
- Select OS: Choose an Ubuntu LTS version (e.g., Ubuntu 20.04 LTS).
Step 2: Connect to the VM
-
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
- Run the following commands to update and upgrade the system:
sudo apt update
sudo apt upgrade -y
Step 4: Install PostgreSQL
- Install PostgreSQL:
sudo apt install postgresql postgresql-contrib -y
- Start and Enable PostgreSQL:
sudo systemctl start postgresql
sudo systemctl enable postgresql
Step 5: Configure PostgreSQL
- Switch to the PostgreSQL User:
sudo -i -u postgres
- Access PostgreSQL Prompt:
psql
- Set a Password for the PostgreSQL User:
\password postgres
(Enter the new password when prompted)
- Create a New Database and User:
CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
- Exit PostgreSQL Prompt:
\q
-
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.
- 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.
- Restart PostgreSQL:
sudo systemctl restart postgresql
Step 6: Allow External Connections to PostgreSQL
-
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
-
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!
Top comments (1)
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...