Why “production-ready” PostgreSQL on a VPS is different from local installs

A local PostgreSQL install is usually optimized for convenience: wide-open local access, default settings, minimal security, and little attention to backups or monitoring. On a VPS, “production-ready” means:

Only authorized connections can reach the database.
Disk, memory, and CPU are tuned for predictable performance.
SSL encryption is enabled (often required by compliance).
Backups are automatic and tested.
Monitoring and logs help you detect issues early.

This guide assumes a Linux VPS (Ubuntu/Debian-like), PostgreSQL 14+ (concepts apply broadly), and that your app connects over the network or locally.

---

Installation and basic system prep

Update packages and install PostgreSQL

```bash sudo apt update sudo apt -y upgrade sudo apt -y install postgresql postgresql-contrib ```

Check the service:

```bash sudo systemctl status postgresql ```

Verify version and connect locally

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

At this stage PostgreSQL is running and listening on a local socket by default (good for security). Next we’ll create a database and a user for your application.

---

Create database, roles, and a safe authentication model

Create a dedicated database and role for your app

Never use the `postgres` superuser for your application.

```bash sudo -u postgres psql ```

Inside `psql`:

```sql CREATE ROLE app_user WITH LOGIN PASSWORD 'use-a-strong-generated-password' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;

CREATE DATABASE app_db OWNER app_user;

-- Optional: tighten default public permissions REVOKE ALL ON DATABASE app_db FROM PUBLIC; GRANT CONNECT ON DATABASE app_db TO app_user; ```

Exit with `\q`.

Use strong password auth for network connections

PostgreSQL’s access control is split into:

`postgresql.conf` (network listening, TLS settings, performance)
`pg_hba.conf` (who can connect, from where, and how)

On Debian/Ubuntu these are typically in:

```bash /etc/postgresql//main/ ```

For example:

```bash ls /etc/postgresql/ ```

---

Network access: bind carefully and restrict the firewall

Decide whether you need remote access at all

If your app runs on the same VPS, prefer local connections via Unix socket (more secure, faster). If your app runs elsewhere (another VPS, Kubernetes, managed app host), you’ll need TCP access.

Configure listening address (if remote access is needed)

Edit `postgresql.conf`:

```bash sudo nano /etc/postgresql/*/main/postgresql.conf ```

Set:

```conf listen_addresses = 'localhost' ```

If you must accept remote connections, you can bind to the VPS private IP (preferred) or all interfaces (least preferred):

```conf listen_addresses = '10.0.0.10' # example private IP

or: listen_addresses = '*'

```

Then restrict access in `pg_hba.conf` and at the firewall.

Restrict clients with `pg_hba.conf`

Edit:

```bash sudo nano /etc/postgresql/*/main/pg_hba.conf ```

Add a rule for your application host/subnet (example: only one app server IP):

```conf

Allow app server to connect to app_db as app_user using password auth

hostssl app_db app_user 203.0.113.10/32 scram-sha-256 ```

Notes:

`hostssl` forces SSL/TLS.
`scram-sha-256` is preferred over `md5`.
Put more specific rules above more general ones.

Reload configuration:

```bash sudo systemctl reload postgresql ```

Firewall basics (example with UFW)

If the database is local-only, do not open port 5432.

If remote access is needed, only allow from your app server IP:

```bash sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp sudo ufw enable sudo ufw status ```

---

Enable SCRAM passwords and verify authentication settings

In `postgresql.conf` ensure:

```conf password_encryption = scram-sha-256 ```

If you already created the user before setting this, reset the password to ensure it’s stored as SCRAM:

```bash sudo -u postgres psql -c "ALTER ROLE app_user PASSWORD 'new-strong-password';" ```

---

SSL/TLS encryption (recommended even inside private networks)

Generate or install certificates

For production you often want certificates from your organization/PKI. For a simple VPS deployment, you can use a self-signed cert (clients must trust it explicitly).

Create a directory and generate key/cert:

```bash sudo mkdir -p /etc/postgresql/ssl sudo openssl req -new -x509 -days 3650 -nodes \ -out /etc/postgresql/ssl/server.crt \ -keyout /etc/postgresql/ssl/server.key sudo chmod 600 /etc/postgresql/ssl/server.key sudo chown -R postgres:postgres /etc/postgresql/ssl ```

Enable SSL in PostgreSQL

Edit `postgresql.conf`:

```conf ssl = on ssl_cert_file = '/etc/postgresql/ssl/server.crt' ssl_key_file = '/etc/postgresql/ssl/server.key' ```

Reload:

```bash sudo systemctl restart postgresql ```

Now connections matching `hostssl` entries in `pg_hba.conf` will require TLS.

---

Performance tuning: memory and connection strategy

Default settings are conservative and may underutilize VPS resources. Two of the most impactful settings for beginners are `shared_buffers` and `work_mem`.

Understand what these do

`shared_buffers`: PostgreSQL’s main cache for data pages. Too low wastes RAM; too high can starve the OS file cache.
`work_mem`: per-operation memory for sorts/hashes (per query node, per connection). Too high can cause RAM blowups under concurrency.

A practical starting point

Assume a small VPS (2 vCPU, 4 GB RAM) running PostgreSQL plus an application.

Edit `postgresql.conf`:

```conf

Memory

shared_buffers = 1GB effective_cache_size = 3GB

Work memory (be conservative; it's per operation)

work_mem = 16MB

Maintenance operations (vacuum, create index)

maintenance_work_mem = 256MB

Connections: keep sane limits

max_connections = 100 ```

Guidelines:

`shared_buffers`: often 20–30% of RAM on a mixed-use VPS; higher (up to ~40%) on a dedicated DB server.
`effective_cache_size`: estimate of OS cache + shared buffers; often 50–75% of RAM.
`work_mem`: start 4–32MB depending on query patterns; increase carefully after observing real workload.
`max_connections`: large values can hurt memory and performance. Use connection pooling when needed.

Use a connection pooler (recommended)

If your app opens many connections, consider PgBouncer (lightweight pooler). It reduces overhead and makes `max_connections` manageable. Typical approach: set PostgreSQL `max_connections` to something modest (e.g., 100–300 depending on RAM), then let PgBouncer handle many client connections.

---

Logging and monitoring essentials

Enable useful logs

In `postgresql.conf`:

```conf logging_collector = on log_line_prefix = '%m [%p] %u@%d %r ' log_checkpoints = on log_lock_waits = on log_min_duration_statement = 500ms ```

This logs slow queries (over 500ms). Tune threshold later.

Restart:

```bash sudo systemctl restart postgresql ```

Basic health checks

Run:

```bash sudo -u postgres psql -c "SELECT now(), count(*) FROM pg_stat_activity;" sudo -u postgres psql -c "SELECT * FROM pg_stat_database;" ```

Track database size and top tables

```bash sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('app_db'));" ```

For table sizes (run inside `app_db`):

```bash sudo -u postgres psql app_db -c " SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;" ```

Consider exporting metrics

For production, pair PostgreSQL with:

`postgres_exporter` + Prometheus + Grafana, or
a hosted monitoring service.

Even basic CPU/RAM/disk monitoring plus slow query logs can prevent surprises.

---

Backups: automate, verify, and keep multiple copies

Logical backups with `pg_dump` (simple and common)

Create a backup directory:

```bash sudo mkdir -p /var/backups/postgresql sudo chown postgres:postgres /var/backups/postgresql ```

Create a script (run as `postgres`):

```bash sudo -u postgres nano /var/backups/postgresql/backup_app_db.sh ```

Example content:

```bash #!/bin/bash set -euo pipefail TS=$(date +%F_%H%M) pg_dump -Fc -d app_db > /var/backups/postgresql/app_db_${TS}.dump find /var/backups/postgresql -name "app_db_*.dump" -mtime +7 -delete ```

Make executable:

```bash sudo -u postgres chmod +x /var/backups/postgresql/backup_app_db.sh ```

Schedule via cron:

```bash sudo crontab -u postgres -e ```

Add (daily at 02:15):

```cron 15 2 * /var/backups/postgresql/backup_app_db.sh ```

Critical: Copy backups off the VPS (object storage, another server). If the VPS disk dies, local backups die with it.

Test restore (non-negotiable)

On a staging server or separate database:

```bash createdb restore_test pg_restore -d restore_test /var/backups/postgresql/app_db_YYYY-MM-DD_HHMM.dump ```

If you never test restores, you don’t have reliable backups.

Physical backups and PITR (advanced but worth planning)

For stricter RPO/RTO, look into:

WAL archiving + base backups (Point-In-Time Recovery),
tools like `pgBackRest` or `barman`.

These are more complex but enable restoring to a specific time (e.g., just before an accidental delete).

---

Security hardening checklist

Keep PostgreSQL and OS updated

Security patches matter. Regularly:

```bash sudo apt update && sudo apt -y upgrade ```

Principle of least privilege

App role should not be superuser.
Create separate roles for migrations vs runtime if possible.
Don’t grant broad permissions on `public` schema unless needed.

Example: restrict schema usage and only grant needed privileges:

```sql REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT USAGE ON SCHEMA public TO app_user; ```

(Then grant table privileges via migrations or explicit GRANTs.)

Disable unused features and reduce exposure

Bind to private IP or localhost.
Restrict `pg_hba.conf` to known IPs/subnets.
Avoid opening 5432 publicly.
Use `hostssl` rules for remote access.

Protect against brute force and credential leaks

Use long, unique passwords (or client certificates).
Consider fail2ban for SSH; database brute force is usually mitigated by firewalling.
Store credentials in environment variables or secrets manager, not in code repos.

System-level protections

Ensure data directory permissions are tight (owned by `postgres`).
Keep enough disk space; PostgreSQL behaves poorly when disks fill.
Consider separate volume for `/var/lib/postgresql` if you want easier resizing and isolation.

---

Final practical validation steps

Confirm PostgreSQL is listening only where expected

```bash sudo ss -ltnp | grep 5432 ```

Confirm SSL is used (remote)

From a client:

```bash psql "host=yourdb.example.com dbname=app_db user=app_user sslmode=require" ```

Inside `psql`:

```sql \conninfo ```

It should indicate SSL in use.

Confirm authentication and access rules

Try connecting from an unauthorized IP (should fail). This is one of the simplest and most valuable security tests you can do.

---

A sensible “minimum production” baseline

If you do only a few things, prioritize these:

1. Create a dedicated app role (non-superuser) and database. 2. Restrict network exposure (localhost/private IP + firewall + strict `pg_hba.conf`). 3. Enable SSL for any remote connections (`hostssl` + certs). 4. Tune basic memory (`shared_buffers`, conservative `work_mem`) and limit connections. 5. Implement automated backups and test restores. 6. Turn on slow query logging and basic monitoring.

With these in place, you’ll have a PostgreSQL setup that’s not just running on a VPS, but is meaningfully safer, more maintainable, and ready for real production traffic.