PostgreSQL on a VPS: Installation and Optimization

PostgreSQL (Postgres) is a powerful DBMS for complex applications: it supports JSON, full-text search, window functions, and much more. The preferred choice of Laravel, Django, and Ruby on Rails developers.

Installing PostgreSQL 16

sudo apt update
sudo apt install -y postgresql postgresql-contrib

sudo systemctl enable postgresql
sudo systemctl start postgresql

# Check the version
psql --version

Creating a Database and User

# Log in as postgres
sudo -u postgres psql

-- Create a user
CREATE USER myuser WITH PASSWORD 'StrongPassword';

-- Create the database
CREATE DATABASE myapp OWNER myuser;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

\q

Configuring Remote Access

# Allow connections
sudo nano /etc/postgresql/16/main/postgresql.conf
# listen_addresses = '*'

# Configure authentication
sudo nano /etc/postgresql/16/main/pg_hba.conf
# Add at the end:
# host  all  all  0.0.0.0/0  md5

sudo systemctl restart postgresql
sudo ufw allow 5432/tcp

Basic postgresql.conf Optimization

shared_buffers = 256MB          # 25% RAM
effective_cache_size = 768MB    # 75% RAM
work_mem = 16MB
maintenance_work_mem = 64MB
max_connections = 100

Tuning postgresql.conf

# /etc/postgresql/16/main/postgresql.conf
# For a server with 8 GB RAM

max_connections = 100
shared_buffers = 2GB            # 25% RAM
effective_cache_size = 6GB      # 75% RAM
work_mem = 10MB                 # RAM / max_connections / 2
maintenance_work_mem = 512MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1          # SSD
effective_io_concurrency = 200  # SSD

Useful psql commands

Installing PostgreSQL

sudo apt update
sudo apt install -y postgresql postgresql-contrib

# Start and enable autostart
sudo systemctl enable --now postgresql

# Check
sudo systemctl status postgresql

Basic Operations via psql

# Log in as postgres (superuser)
sudo -u postgres psql

-- Create a user
CREATE USER myapp WITH PASSWORD 'SecurePass123!';

-- Create the DB
CREATE DATABASE myapp_db OWNER myapp;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp;

-- Exit
\q

Connecting and Navigation

# Connect to a specific database
psql -U myapp -d myapp_db -h localhost

-- List DBs
\l

-- List tables
\dt

-- Describe a table
\d tablename

-- Run SQL from a file
\i /path/to/script.sql

Backup and Restore

# Backup a single database
pg_dump -U myapp myapp_db > backup_$(date +%Y%m%d).sql

# Backup all databases
sudo -u postgres pg_dumpall > all_databases.sql

# Restore
psql -U myapp -d myapp_db < backup.sql