How to Set Up PostgreSQL on a Cheap Linux VPS

Running your own database server sounds more complicated than it is. PostgreSQL on a cheap Linux VPS is straightforward to set up, and for most small-to-medium applications it's more than capable. Here's how to do it.

Install PostgreSQL

apt update && apt install -y postgresql postgresql-contrib
systemctl enable postgresql
systemctl start postgresql

Initial setup

# Switch to the postgres system user
su - postgres

# Open the psql shell
psql

# Create a database and user
CREATE DATABASE myapp;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'strongpassword';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;
q
exit

Configure PostgreSQL for remote access

By default, PostgreSQL only listens on localhost. To allow your application server (or your local machine) to connect:

nano /etc/postgresql/15/main/postgresql.conf

Find and change:

listen_addresses = 'localhost'

To:

listen_addresses = '*'

Then edit pg_hba.conf to allow your app's IP:

nano /etc/postgresql/15/main/pg_hba.conf

# Add at the end:
host    myapp    myuser    YOUR_APP_IP/32    scram-sha-256

systemctl restart postgresql

Connect through your NAT port

PostgreSQL uses port 5432. On your NAT VPS, forward one of your assigned ports to 5432 inside the container — this is already handled by NATBox's iptables rules. Your connection string looks like:

postgresql://myuser:[email protected]:YOUR_ASSIGNED_PORT/myapp

Basic security hardening

Never use the postgres superuser for your app — always create a dedicated user with only the permissions it needs
Use strong passwords or preferably certificate authentication
Restrict pg_hba.conf to specific IPs, not 0.0.0.0/0
Keep PostgreSQL updated: apt upgrade postgresql

Automated backups

nano /etc/cron.daily/pg-backup

#!/bin/bash
su -c "pg_dump myapp | gzip > /opt/backups/myapp_$(date +%Y%m%d).sql.gz" postgres
find /opt/backups -mtime +7 -delete

chmod +x /etc/cron.daily/pg-backup
mkdir -p /opt/backups

This dumps your database daily and keeps 7 days of backups. Adjust the retention period as needed.

How much RAM does PostgreSQL use?

PostgreSQL's base process uses around 20–30MB. Under load, shared buffers and working memory push this higher. For small apps, the Micro plan (512MB) is the right choice — it gives PostgreSQL room to cache without starving your application. The Small plan (1GB) is comfortable for medium workloads.