PostgreSQL is the pragmatic default database for anything you're building today — modern web apps, SaaS backends, analytics workloads, ML feature stores. This guide walks through the correct installation from scratch and the tuning that actually matters for a small production database.

Ubuntu / Debian install

Use the official PGDG repository, not the (often outdated) distro packages:

```bash

Add the PGDG repository

apt install curl gnupg

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | \

gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg

echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | \

tee /etc/apt/sources.list.d/pgdg.list

apt update

apt install postgresql-16 postgresql-client-16

```

Verify:

```bash

systemctl status postgresql

sudo -u postgres psql -c "SELECT version();"

```

CentOS / Rocky / RHEL install

```bash

Add the PGDG repository

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable the built-in module (which conflicts)

dnf -qy module disable postgresql

Install

dnf install -y postgresql16-server postgresql16

Initialize and start

/usr/pgsql-16/bin/postgresql-16-setup initdb

systemctl enable --now postgresql-16

```

Create your first database and user

```bash

sudo -u postgres psql

```

At the psql prompt:

```sql

CREATE USER myapp_user WITH PASSWORD 'a-strong-password-please';

CREATE DATABASE myapp OWNER myapp_user;

GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;

\q

```

Test the connection:

```bash

psql -h localhost -U myapp_user -d myapp

```

If it fails with authentication error, edit /etc/postgresql/16/main/pg_hba.conf and change:

```

local all all peer

```

to:

```

local all all md5

```

Restart: systemctl restart postgresql.

Tune for your VPS size

Postgres's default config assumes tiny memory. On a 4 GB VPS:

Edit /etc/postgresql/16/main/postgresql.conf:

```

shared_buffers = 1GB # 25% of RAM

effective_cache_size = 3GB # ~75% of RAM

maintenance_work_mem = 256MB

work_mem = 32MB

max_connections = 100

random_page_cost = 1.1 # for SSD/NVMe

effective_io_concurrency = 200 # for SSD/NVMe

wal_buffers = 16MB

default_statistics_target = 100

```

For an 8 GB VPS, double shared_buffers and effective_cache_size. For 16 GB, quadruple.

Or use the online tuner at pgtune.leopard.in.ua — enter your specs, copy the output.

Restart: systemctl restart postgresql.

Allow remote connections (only if needed)

For a Postgres accessible from your app on a different VPS:

Edit /etc/postgresql/16/main/postgresql.conf:

```

listen_addresses = '*'

```

Edit /etc/postgresql/16/main/pg_hba.conf — add:

```

host myapp myapp_user <your-app-vps-ip>/32 md5

```

Restart Postgres. Open port 5432 in your firewall — but only for the specific IP:

```bash

ufw allow from <your-app-vps-ip> to any port 5432

```

Never expose Postgres to the open internet without VPN or SSH tunnel.

Backup script

Create /usr/local/bin/pg-backup:

```bash

#!/bin/bash

BACKUP_DIR=/root/pg-backups

DATE=$(date +%Y%m%d)

mkdir -p $BACKUP_DIR

Dump each database

for db in $(sudo -u postgres psql -tc "SELECT datname FROM pg_database WHERE datistemplate = false;"); do

sudo -u postgres pg_dump -F c "$db" | gzip > "$BACKUP_DIR/$db-$DATE.dump.gz"

done

Keep 14 days

find $BACKUP_DIR -mtime +14 -delete

```

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

Cron:

```

0 3 * /usr/local/bin/pg-backup

```

Better: sync the backups off-server. See our automated backups guide.

Monitor Postgres

pg_stat_statements for slow query tracking:

```sql

CREATE EXTENSION pg_stat_statements;

```

Add to postgresql.conf:

```

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = all

```

Restart. Now you can query:

```sql

SELECT calls, total_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

```

For per-second monitoring: install Netdata (see our monitoring guide) — it auto-detects Postgres and dashboards it.

Common gotchas

"could not connect to server: Connection refused"

• Postgres isn't running. systemctl start postgresql.

"FATAL: password authentication failed"

• Wrong password, or pg_hba.conf set to peer for the connection method you're using.

"FATAL: too many connections"

• max_connections exceeded. Use PgBouncer for connection pooling, or increase the limit (but watch RAM).

Postgres eating disk from WAL logs

• checkpoint_timeout too high, or archive settings wrong. Set to sane defaults.

Backup taking forever

• Use pg_dump -F c (custom format) with -j for parallel workers on a big database.

Connection pooling with PgBouncer

For any Postgres serving more than a handful of concurrent connections, PgBouncer is the honest addition. It multiplexes many client connections onto fewer real Postgres backends, so a Rails or Node app with 50 workers doesn't need 50 real Postgres backend processes.

```bash

apt install pgbouncer

```

Edit /etc/pgbouncer/pgbouncer.ini — key settings for a small VPS:

```

[databases]

myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]

listen_addr = 127.0.0.1

listen_port = 6432

auth_type = md5

auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction

max_client_conn = 200

default_pool_size = 20

```

transaction pool mode is safest for most web apps; session mode is required if your app uses prepared statements or long-lived connections. Read the docs before choosing.

Point your app at port 6432 instead of 5432. PgBouncer keeps ~20 real Postgres connections open regardless of how many app connections you have.

When to move to managed Postgres

Self-hosted Postgres is fine for many small workloads. When to move to a managed service (DigitalOcean Managed Postgres, Neon, Supabase):

• You've had one incident where restore-from-backup was harder than you expected.
• You need automatic failover with a standby replica.
• You want Point-in-Time Recovery.
• Your DB is critical enough that "postgres process ate all the RAM" incidents cost you real money.
• You want somebody else to handle Postgres major version upgrades.

For a small MVP under $100 in monthly revenue, self-hosted on a $7.60 Hetzner CX32 is often fine. Over that, evaluate whether the $15-60/mo managed option is worth the ops headache reduction.

Version choice

Postgres 16 is the current stable and what most tutorials assume. Postgres 17 (released 2024) is fine for new deploys. Postgres 12 and older are past end-of-life and should not be used for new work. A version upgrade path takes minutes to hours depending on data size; plan it as its own project rather than something you do during an incident.

Related guides

• Best hosting for a Postgres database server — the hosting options in detail.
• How to migrate a database between VPS providers — moving your Postgres.
• How to set up automated backups on any VPS — better backup strategy.