PostgreSQL on a VPS: install, tune, back up

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.

1. Install

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.

2. Create a database and a user

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.

3. Tune for your RAM

Edit /etc/postgresql/16/main/postgresql.conf and set values based on server size:

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.

4. Daily dumps with rotation

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.

5. Restore a dump

sudo -u postgres createdb appdb_restore
sudo -u postgres pg_restore -d appdb_restore /var/backups/postgres/appdb.dump

Quick answers

Can I open PostgreSQL to the internet?

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.

Which plan do I need for PostgreSQL?

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.

How do I check what queries are slow?

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 Buran-2, €13/mo.