Install PostgreSQL 16 on Ubuntu, create a user and database, tune shared_buffers and work_mem for 2, 4 and 8 GB servers, and set up daily pg_dump backups with systemd.
A default PostgreSQL install is tuned for a 1990s workstation. On a 2–8 GB VPS a few settings change everything - here is the short version, with real numbers.
apt update && apt install -y postgresql postgresql-contrib systemctl enable --now postgresql sudo -u postgres psql -c "SELECT version();"
Ubuntu ships PostgreSQL 16 on 24.04. That version is modern enough for anything you are likely to run.
sudo -u postgres psql <<'SQL' CREATE USER app WITH PASSWORD 'strong-password'; CREATE DATABASE appdb OWNER app; SQL psql "postgresql://app:strong-password@localhost/appdb" -c "SELECT 1;"
Never use the postgres superuser in an application. One user per app, one database per project.
Edit `/etc/postgresql/16/main/postgresql.conf` and set values based on server size:
| Setting | 2 GB VPS | 4 GB VPS | 8 GB VPS |
|---|---|---|---|
| shared_buffers | 512MB | 1GB | 2GB |
| effective_cache_size | 1GB | 2.5GB | 5GB |
| work_mem | 16MB | 32MB | 48MB |
| maintenance_work_mem | 128MB | 256MB | 512MB |
| max_connections | 50 | 100 | 150 |
systemctl restart postgresql sudo -u postgres psql -c "SHOW shared_buffers;"
If your app opens hundreds of connections, put PgBouncer in front instead of raising max_connections - each connection costs memory.
cat > /usr/local/bin/pg-dump-all <<'EOF' #!/bin/sh set -e DIR=/var/backups/postgres mkdir -p "$DIR" sudo -u postgres pg_dumpall --globals-only > "$DIR/globals.sql" for db in $(sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false"); do sudo -u postgres pg_dump -Fc "$db" > "$DIR/$db.dump" done find "$DIR" -type f -mtime +7 -delete EOF chmod +x /usr/local/bin/pg-dump-all
Add it to cron with `crontab -e`:
30 3 * * * /usr/local/bin/pg-dump-all
Dumps are consistent snapshots; file copies of a live data directory are not. Then ship the dumps off-site - our restic guide covers that part.
sudo -u postgres createdb appdb_restore sudo -u postgres pg_restore -d appdb_restore /var/backups/postgres/appdb.dump
Better not. Keep it on localhost and let the app talk to it directly, or use an SSH tunnel. If you must expose it, allow only your IP in the firewall and require TLS.
2 GB RAM is fine for development and small apps, 4 GB is comfortable for a production website, and 8 GB handles a busy database with caching. NVMe storage matters more than extra cores here.
Enable the pg_stat_statements extension and sort by mean time. It usually shows one or two missing indexes that explain most of the load.
A comfortable production database starts at Core VPS 6, €16/mo.