Install production-ready PostgreSQL on Debian

Installing PostgreSQL takes two minutes. Making it production-ready (secured, properly sized, backed up, monitored) takes a good hour, and that hour is what most tutorials skip over. This guide takes you from apt install to a server you can hand to a real application without crossing your fingers.

It was written and tested on Debian 12 (Bookworm) and Debian 13 (Trixie) with PostgreSQL 17. Everything also applies to Ubuntu 22.04 / 24.04, give or take a few paths.

Prerequisites

• A YorkHost Linux VPS running Debian, with root or sudo access
• A working SSH connection, ideally secured with a key
• A firewall in place, see Configure UFW

Which VPS for PostgreSQL?

PostgreSQL is hungry for RAM (page cache) and IOPS (WAL writes on every commit). For a production application, start with at least a VPS-8 (4 vCores, 8 GB RAM, Enterprise SSD). If your database is your core business, the VPS Ultimate range on NVMe with Xeon Silver/Gold makes a very noticeable difference on commit latency and large VACUUM runs.

Step 1: Prepare the system

Update the system and set the time zone (your log and backup timestamps depend on it):

sudo apt update && sudo apt upgrade -y
sudo timedatectl set-timezone Europe/Paris
sudo apt install -y curl ca-certificates gnupg lsb-release

Step 2: Install PostgreSQL from the official PGDG repository

Debian ships PostgreSQL in its repositories, but at a version frozen when the release came out (PostgreSQL 15 on Debian 12). The official PGDG (PostgreSQL Global Development Group) repository gives you access to every supported version and to minor updates as soon as they are published. It is the method recommended by the PostgreSQL project itself.

# Install the repository management tool and add the PGDG repository
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

# Install PostgreSQL 17 (server + client + contrib)
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17 postgresql-contrib

Check that the cluster is running:

pg_lsclusters

You should see something like:

Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

How Debian organizes PostgreSQL

Debian has its own layout, different from other distributions:

Item	Location
Configuration files	/etc/postgresql/17/main/
Data	/var/lib/postgresql/17/main/
Logs	/var/log/postgresql/postgresql-17-main.log
systemd service	postgresql@17-main (plus postgresql, which groups them all)

The pg_lsclusters, pg_ctlcluster and pg_upgradecluster tools are Debian-specific and will make your life easier, especially for version upgrades.

Enable automatic startup (normally already done):

sudo systemctl enable --now postgresql

Step 3: First contact and superuser password

On Debian, the postgres system user connects locally without a password through peer authentication (the system checks that you really are the Unix user postgres). This is safe, and it is what you will use for administration:

sudo -u postgres psql

Still set a password for the superuser; you will need it if you ever connect over the network:

\password postgres

Then quit with \q.

Step 4: Create a role and a database for your application

Never run an application with the postgres account. Create a dedicated role that owns its database, and nothing more:

sudo -u postgres psql

-- One role per application, with a strong password
CREATE ROLE monapp WITH LOGIN PASSWORD 'un-mot-de-passe-long-et-aleatoire';

-- Its database, which it owns
CREATE DATABASE monapp_db OWNER monapp ENCODING 'UTF8' LC_COLLATE 'fr_FR.UTF-8' LC_CTYPE 'fr_FR.UTF-8' TEMPLATE template0;

Locale fr_FR.UTF-8 missing?

If the command fails with invalid LC_COLLATE locale name, the locale is not generated on the system. Run sudo dpkg-reconfigure locales, tick fr_FR.UTF-8, then run the command again. You can also simply use en_US.UTF-8 or C.UTF-8: the locale only affects string sort order, not how accented characters are stored.

Since PostgreSQL 15, the public schema is no longer writable by everyone. Because monapp owns the database, it also owns its public schema and can create tables there. If you later add a second role (read-only for a reporting tool, for example):

CREATE ROLE reporting WITH LOGIN PASSWORD 'autre-mot-de-passe';
GRANT CONNECT ON DATABASE monapp_db TO reporting;
\c monapp_db
GRANT USAGE ON SCHEMA public TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;
ALTER DEFAULT PRIVILEGES FOR ROLE monapp IN SCHEMA public GRANT SELECT ON TABLES TO reporting;

Test the application connection locally:

psql -h 127.0.0.1 -U monapp -d monapp_db

Step 5: Authentication and network access

Two files control who can connect and how: postgresql.conf (which interfaces to listen on) and pg_hba.conf (who, from where, with which method).

Check that password encryption is scram-sha-256

PostgreSQL 17 uses scram-sha-256 by default. Verify it, because an old md5 lingering in an inherited configuration is a classic weakness:

sudo -u postgres psql -c "SHOW password_encryption;"

Case A: the application runs on the same VPS (recommended)

This is the simplest and safest case. PostgreSQL only listens on localhost, which is already the default setting:

sudo nano /etc/postgresql/17/main/postgresql.conf

listen_addresses = 'localhost'

In pg_hba.conf, make sure local TCP connections use scram-sha-256:

sudo nano /etc/postgresql/17/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Nothing to open in the firewall. That's it for networking.

Case B: the application is on another server

If your application runs on a second YorkHost VPS, you need to expose port 5432, but only to that IP, and only over TLS.

# postgresql.conf
listen_addresses = '*'

# pg_hba.conf: allow only the application server's IP, with TLS required
hostssl monapp_db       monapp          203.0.113.42/32         scram-sha-256

Then restrict access at the firewall level:

sudo ufw allow from 203.0.113.42 to any port 5432 proto tcp

Never expose PostgreSQL to the whole Internet

A host all all 0.0.0.0/0 md5 line in pg_hba.conf combined with ufw allow 5432 is the configuration found in almost every compromised database. Scanners probe port 5432 around the clock. YorkHost Anti-DDoS protects your server against volumetric attacks, but nothing replaces a properly restricted firewall when facing a protocol-level connection attempt that looks legitimate.

Enable TLS

Debian already generates a self-signed certificate (ssl-cert-snakeoil) and enables it by default, which is enough to encrypt traffic between two of your own servers. For a recognized certificate, use Let's Encrypt via certbot, then:

ssl = on
ssl_cert_file = '/etc/letsencrypt/live/db.example.com/fullchain.pem'
ssl_key_file = '/etc/letsencrypt/live/db.example.com/privkey.pem'
ssl_min_protocol_version = 'TLSv1.2'

The key files must be readable by the postgres user (copy them into /etc/postgresql/17/main/ with chmod 600 and chown postgres:postgres if needed).

Reload after any change to these two files:

sudo systemctl reload postgresql

Step 6: Memory and disk tuning

PostgreSQL's default configuration is designed to run on a tiny machine. On a modern VPS, it leaves most of the resources unused. Here are the settings that actually matter, with values calibrated for a YorkHost VPS-8 (4 vCores, 8 GB RAM, SSD). Scale them proportionally.

sudo nano /etc/postgresql/17/main/postgresql.conf

# ---- Memory ----
shared_buffers = 2GB                  # ~25% of RAM
effective_cache_size = 6GB            # ~75% of RAM: what the kernel + PG can cache
work_mem = 16MB                       # per sort/hash operation, per connection: stay conservative
maintenance_work_mem = 512MB          # VACUUM, CREATE INDEX
huge_pages = try

# ---- Connections ----
max_connections = 100                 # beyond this, use PgBouncer (see below)

# ---- SSD / NVMe disk ----
random_page_cost = 1.1                # 4.0 by default, calibrated for spinning disks
effective_io_concurrency = 200

# ---- WAL and checkpoints ----
wal_buffers = 64MB
min_wal_size = 1GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
checkpoint_timeout = 15min

# ---- Planner ----
default_statistics_target = 100

# ---- Parallelism (4 vCores) ----
max_worker_processes = 4
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2

A few explanations so you don't apply this blindly:

• shared_buffers is PostgreSQL's internal cache. 25% of RAM is the proven rule; above 40%, you start competing with the Linux kernel cache, with no gain.
• work_mem multiplies: a query with several sorts and 100 concurrent connections can consume 100 × several × work_mem. A modest global value, raised on demand with SET work_mem = '256MB' in reporting sessions, is the right approach.
• random_page_cost tells the planner how expensive a random read is compared to a sequential read. On SSD the difference is small, hence 1.1. Leaving it at 4.0 pushes PostgreSQL to ignore your indexes on medium-sized tables.
• A higher max_wal_size reduces checkpoint frequency, and therefore write spikes. The price: a slightly longer restart after a crash.

These parameters require a restart (not a simple reload):

sudo systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers;"

The PGTune calculator

To quickly size a different VPS, pgtune.leopard.in.ua generates a consistent set of values from the RAM, core count and workload type. Use it as a starting point, then observe.

Huge pages (optional, recommended above 8 GB)

With huge_pages = try, PostgreSQL uses them if the kernel makes them available. To reserve enough for 2 GB of shared_buffers (2 MB pages, allow some headroom):

echo "vm.nr_hugepages = 1100" | sudo tee /etc/sysctl.d/90-postgresql.conf
sudo sysctl --system
sudo systemctl restart postgresql

Step 7: Useful logging

The default logs are too quiet to diagnose a performance problem. Enable at least the following:

log_min_duration_statement = 500      # log every query > 500 ms
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0                    # log sorts that spill to disk
log_autovacuum_min_duration = 1000
log_line_prefix = '%m [%p] %u@%d %h '
log_timezone = 'Europe/Paris'

And install pg_stat_statements, the extension that tells you which queries are eating your server:

shared_preload_libraries = 'pg_stat_statements'

sudo systemctl restart postgresql
sudo -u postgres psql -d monapp_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

To see the top 10 most expensive queries:

SELECT calls, round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Step 8: Backups

This is what separates a server that "works" from a production server. Two complementary levels.

Level 1: daily logical dump with pg_dump

A logical dump is portable, readable, and lets you restore a single table. Create a script:

sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo nano /usr/local/bin/pg-backup.sh

#!/bin/bash
set -euo pipefail

BACKUP_DIR=/var/backups/postgresql
DATE=$(date +%Y-%m-%d_%H%M)
RETENTION_DAYS=14

# Dump each database in "custom" format (compressed, selective restore possible)
for DB in $(psql -Atc "SELECT datname FROM pg_database WHERE datistemplate = false AND datname <> 'postgres';"); do
  pg_dump -Fc -Z 6 -f "$BACKUP_DIR/${DB}_${DATE}.dump" "$DB"
done

# Roles and global settings (not included in pg_dump)
pg_dumpall --globals-only > "$BACKUP_DIR/globals_${DATE}.sql"

# Rotation
find "$BACKUP_DIR" -type f -mtime +$RETENTION_DAYS -delete

echo "PostgreSQL backup completed: $DATE"

sudo chmod +x /usr/local/bin/pg-backup.sh
sudo -u postgres /usr/local/bin/pg-backup.sh   # test

Schedule it every night at 3 AM in the postgres user's crontab:

sudo -u postgres crontab -e

0 3 * * * /usr/local/bin/pg-backup.sh >> /var/log/postgresql/backup.log 2>&1

To restore a database:

sudo -u postgres createdb monapp_db_restore
sudo -u postgres pg_restore -d monapp_db_restore /var/backups/postgresql/monapp_db_2026-09-08_0300.dump

A dump on the same disk is not a backup

If the VPS is lost, the dump is lost with it. Ship these files off the server every night. The guide Automatic offsite backup with Restic or Borg explains how to do it in a few minutes to a YorkHost Storage VPS. Also note that the 7-day rolling backups included with your YorkHost VPS are full-disk snapshots: very handy for rolling back after a bad system change, but a snapshot taken mid-write is not guaranteed to be consistent for a database. The two mechanisms complement each other.

Level 2: physical backup and Point-In-Time Recovery

A daily dump means you can lose up to 24 hours of data. If that is not acceptable, you need to archive the WAL continuously so you can restore to any given second. The reference tool is pgBackRest:

sudo apt install -y pgbackrest

Minimal configuration, with a local repository (to be replicated off-site with Restic/Borg afterwards):

sudo nano /etc/pgbackrest.conf

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=une-phrase-secrete-longue
compress-type=zst
log-level-console=info

[main]
pg1-path=/var/lib/postgresql/17/main

In postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica

Then:

sudo mkdir -p /var/lib/pgbackrest && sudo chown postgres:postgres /var/lib/pgbackrest && sudo chmod 750 /var/lib/pgbackrest
sudo systemctl restart postgresql
sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check
sudo -u postgres pgbackrest --stanza=main --type=full backup

Schedule a weekly full and a daily diff in the postgres crontab. The pgBackRest documentation describes point-in-time restore (--type=time).

Step 9: Connections and pooling with PgBouncer

Every PostgreSQL connection is a process that costs several MB of RAM. A modern web application opening 200 connections on a VPS-8 will bring it to its knees, not because of the queries but because of the connections themselves. PgBouncer pools the connections:

sudo apt install -y pgbouncer

sudo nano /etc/pgbouncer/pgbouncer.ini

[databases]
monapp_db = host=127.0.0.1 port=5432 dbname=monapp_db

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20

Generate the authentication line (the SCRAM hash as stored by PostgreSQL):

sudo -u postgres psql -Atc "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname = 'monapp';" | sudo tee /etc/pgbouncer/userlist.txt
sudo systemctl enable --now pgbouncer

Then point your application at port 6432 instead of 5432. In transaction mode, avoid client-side prepared statements (or enable support on the PgBouncer side, version 1.21 or later, with max_prepared_statements = 100).

Step 10: Autovacuum and maintenance

PostgreSQL does not rewrite rows in place: every UPDATE leaves behind an old version that autovacuum cleans up. It is enabled by default and must never be disabled. On very busy tables, simply make it more aggressive:

autovacuum_vacuum_scale_factor = 0.05     # 0.2 by default: too late on large tables
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_limit = 1000       # lets vacuum work faster on SSD

Monitor bloat (dead space) with this query:

SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Step 11: Updates

Minor updates (17.x → 17.y)

They fix bugs and vulnerabilities without changing the data format. Apply them without hesitation:

sudo apt update && sudo apt upgrade -y

The service is restarted automatically. Expect a few seconds of downtime; warn your users if necessary.

Major upgrades (17 → 18)

The data format changes. Debian makes the operation remarkably simple with pg_upgradecluster:

# 1. Full backup first, always
sudo -u postgres /usr/local/bin/pg-backup.sh

# 2. Install the new version (creates an empty 18/main cluster on port 5433)
sudo apt install -y postgresql-18

# 3. Remove the empty cluster created automatically
sudo pg_dropcluster --stop 18 main

# 4. Migrate 17/main to 18/main ("upgrade" method = pg_upgrade, fast)
sudo pg_upgradecluster -m upgrade 17 main

# 5. Check that everything works on the new cluster, then remove the old one
pg_lsclusters
sudo pg_dropcluster 17 main
sudo apt purge -y postgresql-17

Run ANALYZE on your databases after the migration (planner statistics are not carried over):

sudo -u postgres vacuumdb --all --analyze-in-stages

Production checklist

• PostgreSQL installed from the PGDG repository, supported version
• Dedicated application role, postgres never used by the application
• listen_addresses restricted, pg_hba.conf without 0.0.0.0/0, scram-sha-256 method
• Port 5432 closed or limited to specific IPs in UFW
• TLS enabled for every remote connection
• shared_buffers, effective_cache_size, random_page_cost sized for the VPS
• log_min_duration_statement and pg_stat_statements enabled
• Daily dump tested and copied off the server
• WAL archiving (pgBackRest) if losing 24 hours of data is unacceptable
• Restore tested at least once on an empty database
• Minor updates applied regularly

Troubleshooting

FATAL: password authentication failed for user

The password is wrong, or the matching pg_hba.conf line uses peer while you are connecting over TCP (or the other way round). Check with -h 127.0.0.1 to force TCP, and read /var/log/postgresql/postgresql-17-main.log: PostgreSQL reports which pg_hba.conf line was used.

could not connect to server: Connection refused

The server is not listening on that address. ss -tlnp | grep 5432 shows the listening interfaces; check listen_addresses, then the firewall.

FATAL: sorry, too many clients already

max_connections has been reached. Don't raise it indefinitely: set up PgBouncer (step 9) and hunt for forgotten connections with SELECT count(*), state FROM pg_stat_activity GROUP BY state;.

The server no longer starts after a config change

sudo -u postgres /usr/lib/postgresql/17/bin/postgres -D /var/lib/postgresql/17/main -C shared_buffers tests whether the configuration can be read, and journalctl -u postgresql@17-main -n 50 shows the exact error. The classic case: shared_buffers too large for the RAM, or huge_pages = on with no pages reserved.

Disk full

PostgreSQL refuses to write and may end up in a degraded state. Free up space (old dumps, logs), then check that WAL is not piling up: a failing archive_command prevents WAL segments from being deleted, and pg_wal/ grows until the disk is full. See Managing disk space.

Going further

A single PostgreSQL server, even perfectly tuned, remains a single point of failure. If your business cannot tolerate an outage for the time it takes to restore a dump, the logical next step is high availability: replication, automatic failover and connection routing. We describe what that really involves in PostgreSQL high availability: Patroni, etcd and automatic failover.

Would you rather not run the database yourself?

That is exactly what YorkHost Managed Infrastructure does: a Patroni + etcd PostgreSQL cluster spread across several French datacenters, verified backups, CVEs patched within 48 hours, and a named engineer who knows your database rather than a ticket queue. Hosted and operated in France, under French law.