Install PostgreSQL on a Linux VPS

PostgreSQL is a reliable relational database for web apps, analytics, and mixed workloads. This guide covers installation on Ubuntu/Debian, creating a database, and basic security.

Prerequisites

• Ubuntu 20.04+ or Debian 11+ with sudo
• At least 1 GB RAM (2 GB+ for heavier loads)

Installation

1. Update and install the server

Bash
sudo apt update && sudo apt upgrade -y
sudo apt install -y postgresql postgresql-contrib

2. Enable and check the service

Bash
sudo systemctl status postgresql
sudo systemctl enable postgresql

By default PostgreSQL listens on localhost (127.0.0.1), which fits apps on the same VPS.

Create an application user and database

Switch to the postgres system user and open psql:

Bash
sudo -u postgres psql

In psql (adjust names and password):

SQL
CREATE USER myapp WITH PASSWORD 'strong_password_here';
CREATE DATABASE myapp_db OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;
\q

Test connection:

Bash
psql -h 127.0.0.1 -U myapp -d myapp_db

Typical connection string

TEXT
postgresql://myapp:[email protected]:5432/myapp_db

Security essentials

1. Strong password for the application role.
2. Avoid exposing PostgreSQL to the internet unless strictly required. If you must: strict firewall, listen_addresses, pg_hba.conf, preferably VPN or tunnel.
3. Combine with UFW and Fail2ban for public services.

Remote access (avoid unless needed)

Edit postgresql.conf (path varies, often /etc/postgresql/*/main/):

Config
listen_addresses = '*'

In pg_hba.conf, add a restrictive line (example: single trusted IP):

TEXT
host    myapp_db    myapp    203.0.113.50/32    scram-sha-256

Then:

Bash
sudo systemctl restart postgresql

Backups

Logical dump:

Bash
sudo -u postgres pg_dump myapp_db > backup_myapp.sql

Restore:

Bash
sudo -u postgres psql myapp_db < backup_myapp.sql

Schedule backups (cron) to external storage.

Troubleshooting

• Peer authentication failed: use -h 127.0.0.1 for TCP, or adjust pg_hba.conf.
• Connection refused: check service status and port (5432 by default).