PostgreSQL — DevOps Notes

PostgreSQL tuning for a small VPS

Out of the box, PostgreSQL is configured to run on very limited hardware. On a modern VPS with 2–8GB of RAM you can get significantly better performance by adjusting a handful of settings in postgresql.conf.

Memory settings

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB

shared_buffers is Postgres's own cache — set it to 25% of RAM. effective_cache_size is a hint to the query planner about total available memory — set it to 75% of RAM.

Write performance

wal_buffers = 16MB
checkpoint_completion_target = 0.9
default_statistics_target = 100

Connection settings

max_connections = 100

Use pgtune.leopard.in for a starting point — enter your server specs and it generates a complete config.

Backing up and restoring PostgreSQL databases

pg_dump is the standard tool for PostgreSQL backups. It produces a consistent snapshot of a database without locking tables.

Simple dump and restore

pg_dump -U postgres mydb > mydb.sql
psql -U postgres mydb < mydb.sql

Compressed dump

pg_dump -U postgres -Fc mydb > mydb.dump
pg_restore -U postgres -d mydb mydb.dump

Automated daily backups

#!/bin/bash
DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/postgres"

mkdir -p "$BACKUP_DIR"

for DB in $(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datistemplate = false"); do
    pg_dump -U postgres -Fc "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"
done

find "$BACKUP_DIR" -name "*.dump" -mtime +30 -delete