PostgreSQL Database Setup and Optimization on VPS

PostgreSQL is a powerful, open-source relational database that powers millions of applications. This tutorial covers installation, initial configuration, backup strategies, and performance optimization for production VPS environments.

Why PostgreSQL?

PostgreSQL offers ACID compliance, advanced indexing strategies, JSON support, full-text search, and exceptional reliability. Unlike some alternatives, it scales well from small hobby projects to massive enterprise deployments while remaining free and open-source.

Prerequisites

• Linux VPS (Ubuntu 20.04+ or Debian 11+)
• At least 2GB RAM and 20GB storage
• SSH access with sudo privileges
• Basic Linux command-line knowledge

Step 1: Install PostgreSQL

Update system packages:

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

Verify installation:

psql --version

Step 2: Start and Enable PostgreSQL Service

Start the PostgreSQL service:

sudo systemctl start postgresql

Enable auto-start on boot:

sudo systemctl enable postgresql

Check service status:

sudo systemctl status postgresql

Step 3: Initial Database Setup

Connect to PostgreSQL as the default postgres user:

sudo -u postgres psql

Create a new database:

CREATE DATABASE myapp_db;

Create a dedicated user:

CREATE USER appuser WITH PASSWORD 'strong_password_here';

Grant privileges:

ALTER ROLE appuser SET client_encoding TO 'utf8';
ALTER ROLE appuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE appuser SET default_transaction_deferrable TO on;
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO appuser;

Connect to the new database and grant schema privileges:

\c myapp_db
GRANT ALL ON SCHEMA public TO appuser;

Exit psql:

\q

Step 4: Configure PostgreSQL for Remote Connections (If Needed)

Edit PostgreSQL configuration:

sudo nano /etc/postgresql/14/main/postgresql.conf

Find and modify:

listen_addresses = '*'

Edit pg_hba.conf for authentication:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add a line for your application (replace with actual IP):

host    myapp_db    appuser    203.0.113.45/32    md5

Restart PostgreSQL:

sudo systemctl restart postgresql

Step 5: Performance Tuning

Edit postgresql.conf:

sudo nano /etc/postgresql/14/main/postgresql.conf

For a 4GB VPS, apply these optimizations:

# Connection management
max_connections = 200
superuser_reserved_connections = 10

# Memory settings
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 64MB
maintenance_work_mem = 512MB

# Checkpoint settings
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

# Query planning
random_page_cost = 1.1
effective_io_concurrency = 200

Restart PostgreSQL:

sudo systemctl restart postgresql

Step 6: Create Sample Schema

Connect as your application user:

psql -h localhost -U appuser -d myapp_db

Create a users table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

Create a posts table:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

Step 7: Backup Strategy

Create a backup script:

sudo nano /usr/local/bin/pg-backup.sh

Add the following:

#!/bin/bash

BACKUP_DIR="/var/backups/postgresql"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="$BACKUP_DIR/myapp_db_$TIMESTAMP.sql.gz"

mkdir -p $BACKUP_DIR

# Full backup
sudo -u postgres pg_dump myapp_db | gzip > $BACKUP_FILE

# Keep only last 30 days
find $BACKUP_DIR -type f -mtime +30 -delete

echo "Backup completed: $BACKUP_FILE"

Make executable:

sudo chmod +x /usr/local/bin/pg-backup.sh

Schedule with crontab:

sudo crontab -e

Add daily backup at 2 AM:

0 2 * * * /usr/local/bin/pg-backup.sh

Step 8: Monitor Database Performance

Check active connections:

SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

View slow queries:

sudo nano /etc/postgresql/14/main/postgresql.conf

Enable query logging:

log_min_duration_statement = 1000  # Log queries > 1 second
log_statement = 'mod'  # Log DDL/DML
log_duration = on

Step 9: Connect from Your Application

Using Node.js with Drizzle ORM:

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
});

export const db = drizzle(pool);

// Example query
const users = await db.query.users.findMany();

Using connection pooling with PgBouncer:

sudo apt-get install pgbouncer -y

Configure /etc/pgbouncer/pgbouncer.ini:

[databases]
myapp_db = host=localhost port=5432 dbname=myapp_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

Step 10: Maintenance Commands

Analyze query planner statistics:

ANALYZE;

Vacuum to reclaim space:

VACUUM ANALYZE;

Check database size:

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

List tables by size:

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname != 'pg_catalog'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Security Best Practices

• Always use strong passwords with mixed character types
• Disable remote root access: keep postgres user local-only
• Use SSL for remote connections:

sudo nano /etc/postgresql/14/main/postgresql.conf

ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

• Implement regular automated backups to external storage
• Monitor logs regularly for suspicious activity

Useful Resources

• PostgreSQL Official Documentation
• PostgreSQL Performance Tips
• pgAdmin Web Interface
• Drizzle ORM for TypeScript

Conclusion

You've successfully set up a production-ready PostgreSQL database with optimizations, backups, and monitoring. Regularly review performance metrics, maintain your backups, and keep PostgreSQL updated for a reliable data foundation.