VPS Tutorials
PostgreSQL 18 on a VPS: Docker, TLS, PgBouncer and recovery
Deploy PostgreSQL 18 with Docker, TLS/SCRAM and PgBouncer. Verify storage and file ownership, tune connections, preserve backups and test isolated recovery.

This guide builds a single-VPS PostgreSQL service with Ubuntu 24.04, PostgreSQL 18, Compose, TLS/SCRAM and PgBouncer. It covers private access, application roles, pooling, tuning, backups, restore drills and upgrades. A healthy container does not establish recoverability or high availability.
Version review, November 16, 2026: PostgreSQL 18.7 was released on November 12. The original examples below retain 18.6 for reference; replace every postgres:18.6-bookworm tag, including ownership checks, with a verified 18.7 tag/digest before deployment. PgBouncer 1.26.1 fixes CVE-2026-107350, which affects 1.17.0 through 1.26.0. The original 1.25.2 baseline is therefore insufficient: use 1.26.1 or a newer supported patched release. Read the PostgreSQL release notes and PgBouncer changelog before installing.
PostgreSQL 18 was released in September 2025, adding asynchronous I/O, UUIDv7, virtual generated columns and default checksums for newly initialized clusters. Pin a supported 18.x patch rather than latest.
- Application and database share a host/private network and a suitable cost model.
- You need deliberate control of extensions, settings and backups.
- Your workload needs SQL, transactions, JSONB, search or supported geospatial extensions.
- Your team can maintain updates, monitoring, recovery and incident response.
A managed service may suit automatic failover/PITR or limited operations capacity. This host/disk/datacenter remains a single failure domain.
| Workload | Starting budget | Storage | Planning note |
|---|---|---|---|
| Development/light site | 2 vCPU, 4 GB | 60 GB NVMe | Conservative connections with shared applications |
| Small production | 4 vCPU, 8 GB | 120 GB NVMe+ | Pooling and measured headroom |
| Larger workload | 8 vCPU, 16 GB+ | Dedicated suitable storage | Consider separate application/database hosts |
Budget 2-3 times current data size for WAL, indexes, temporary work, backups and migration. Test fio only on suitable disposable test files and inspect sustained storage metrics. Swap helps emergencies but does not replace RAM.
| Port | Purpose | Publication |
|---|---|---|
5432/TCP | PostgreSQL | Host 127.0.0.1 only |
6432/TCP | PgBouncer | 127.0.0.1 only |
22/TCP | SSH | Keys and restricted sources |
Use private routing or a verified SSH tunnel, not public 5432. Container applications need the actual shared Docker/private route, not their own loopback. See database exposure.
Install supported Docker/Compose from its official repository, then verify:
sudo apt update
sudo apt full-upgrade -y
sudo apt install -y ca-certificates curl openssl jq
docker --version
docker compose version
Schedule host upgrades/reboots appropriately. Prepare directories as an authorized administrator:
sudo install -d -m 0750 -o "$USER" -g "$USER" \
/opt/postgres18/{conf,initdb,secrets,tls,backups}
cd /opt/postgres18
umask 077
openssl rand -hex 32 > secrets/postgres_superuser_password
openssl rand -hex 32 > secrets/app_db_password
chmod 600 secrets/*
Protect secrets/, keys and backups outside Git. File-backed Compose secrets remain readable to host/Docker administrators.
Distribute ca.crt securely and include the actual private hostname in SAN. Create /opt/postgres18/tls/openssl.cnf:
[req]
distinguished_name = dn
prompt = no
req_extensions = server_ext
[dn]
CN = postgres
[server_ext]
subjectAltName = DNS:postgres,DNS:localhost,IP:127.0.0.1
keyUsage = critical,digitalSignature,keyEncipherment
extendedKeyUsage = serverAuth
cd /opt/postgres18/tls
openssl req -x509 -newkey rsa:4096 -sha256 -days 3650 -nodes \
-subj "/CN=PostgreSQL Internal CA" \
-keyout ca.key -out ca.crt
openssl req -newkey rsa:3072 -sha256 -nodes \
-config openssl.cnf -keyout server.key -out server.csr
openssl x509 -req -sha256 -days 825 \
-in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
-extfile openssl.cnf -extensions server_ext \
-out server.crt
chmod 600 ca.key server.key
chmod 644 ca.crt server.crt
sudo chown 999:999 server.key server.crt
Keep ca.key offline/protected for signing and renewal; do not mount it in PostgreSQL. Verify the image's postgres UID/GID rather than universally assuming 999. Keep server.key readable only by the intended server user.
Create /opt/postgres18/conf/postgresql.conf:
listen_addresses = '*'
port = 5432
ssl = on
ssl_cert_file = '/run/postgres-tls/server.crt'
ssl_key_file = '/run/postgres-tls/server.key'
ssl_ca_file = '/run/postgres-tls/ca.crt'
ssl_min_protocol_version = 'TLSv1.2'
password_encryption = 'scram-sha-256'
shared_preload_libraries = 'pg_stat_statements'
max_connections = 100
shared_buffers = '2GB'
effective_cache_size = '5GB'
work_mem = '16MB'
maintenance_work_mem = '512MB'
checkpoint_timeout = '15min'
max_wal_size = '4GB'
min_wal_size = '1GB'
wal_compression = on
random_page_cost = 1.1
effective_io_concurrency = 200
track_io_timing = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_min_duration_statement = 1000
deadlock_timeout = '1s'
timezone = 'UTC'
These values assume roughly an 8-GB dedicated host. shared_buffers near 25% is a starting point; work_mem can be consumed by multiple plan nodes/workers across concurrent queries. On a shared host, reduce shared_buffers toward 1 GB and measure total demand, including maintenance memory and the 6-GB container limit.
Create /opt/postgres18/conf/pg_hba.conf:
local all postgres peer
local all all scram-sha-256
hostssl all all 0.0.0.0/0 scram-sha-256
hostssl all all ::0/0 scram-sha-256
hostnossl all all 0.0.0.0/0 reject
hostnossl all all ::0/0 reject
hostssl requires TLS for TCP. Broad CIDRs here still require restricted host publication and trusted Docker peers; narrow them for cross-host application subnets. TLS/passwords alone do not authorize every source.
Create /opt/postgres18/initdb/01-app.sh:
#!/usr/bin/env bash
set -Eeuo pipefail
app_password="$(<"${APP_DB_PASSWORD_FILE}")"
psql -v ON_ERROR_STOP=1 \
--username "${POSTGRES_USER}" \
--dbname "${POSTGRES_DB}" \
--set=app_password="${app_password}" <<'EOSQL'
SELECT format('CREATE ROLE appuser LOGIN PASSWORD %L', :'app_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'appuser')
\gexec
SELECT 'CREATE DATABASE app OWNER appuser'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'app')
\gexec
REVOKE ALL ON DATABASE app FROM PUBLIC;
EOSQL
psql -v ON_ERROR_STOP=1 \
--username "${POSTGRES_USER}" \
--dbname app \
-c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;'
chmod 700 /opt/postgres18/initdb/01-app.sh
Complete ownership before startup. Mode 700 on a deploy-owned script prevents the container's postgres user from executing it, and that user must also read the custom application secret. Inspect the chosen image UID/GID; make the script executable by that user, its mounted initdb directory traversable, and non-secret config files readable. Give the application secret that user's ownership with restrictive permissions. For the Debian image, adapt:
cd /opt/postgres18
pg18_uid=$(docker run --rm --entrypoint id postgres:18.6-bookworm -u postgres)
pg18_gid=$(docker run --rm --entrypoint id postgres:18.6-bookworm -g postgres)
sudo chown "$pg18_uid:$pg18_gid" initdb/01-app.sh secrets/app_db_password
sudo chmod 700 initdb/01-app.sh
sudo chmod 400 secrets/app_db_password
sudo chmod 755 initdb
sudo chmod 644 conf/postgresql.conf conf/pg_hba.conf
After ownership changes, the deployment user cannot read the mode-400 application secret. Run subsequent credential-reading commands in an authorized root shell (sudo -i, then return to /opt/postgres18) or adapt them to read the file through sudo; do not make the secret world-readable. Init scripts run only on an empty cluster; later password changes need ALTER ROLE and coordinated application/pool updates, not file replacement alone.
Create /opt/postgres18/compose.yml:
name: postgres18
services:
postgres:
image: postgres:18.6-bookworm
environment:
POSTGRES_USER: postgres
POSTGRES_DB: postgres
POSTGRES_PASSWORD_FILE: /run/secrets/postgres_superuser_password
APP_DB_PASSWORD_FILE: /run/secrets/app_db_password
command:
- postgres
- -c
- config_file=/etc/postgresql/postgresql.conf
- -c
- hba_file=/etc/postgresql/pg_hba.conf
ports:
- "127.0.0.1:5432:5432"
volumes:
- postgres-data:/var/lib/postgresql
- ./conf/postgresql.conf:/etc/postgresql/postgresql.conf:ro
- ./conf/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
- ./initdb:/docker-entrypoint-initdb.d:ro
- ./tls/ca.crt:/run/postgres-tls/ca.crt:ro
- ./tls/server.crt:/run/postgres-tls/server.crt:ro
- ./tls/server.key:/run/postgres-tls/server.key:ro
secrets:
- postgres_superuser_password
- app_db_password
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
interval: 10s
timeout: 5s
retries: 10
start_period: 30s
shm_size: 1gb
mem_limit: 6g
restart: unless-stopped
logging:
driver: json-file
options:
max-size: 20m
max-file: "5"
secrets:
postgres_superuser_password:
file: ./secrets/postgres_superuser_password
app_db_password:
file: ./secrets/app_db_password
volumes:
postgres-data:
From image version 18, PGDATA uses a versioned subdirectory; mount the volume at /var/lib/postgresql. Do not mechanically reuse the older /var/lib/postgresql/data layout or delete an apparently missing cluster. See production Compose.
cd /opt/postgres18
docker compose config --quiet
docker compose pull
docker compose up -d
docker compose ps
docker compose logs --tail=100 postgres
docker compose exec --user postgres postgres \
psql -U postgres -d postgres -c 'SELECT version();'
docker compose exec --user postgres postgres \
psql -U postgres -d postgres -c 'SHOW ssl; SHOW data_checksums; SHOW shared_preload_libraries;'
docker compose exec --user postgres postgres \
psql -U postgres -d app -c '\dx pg_stat_statements'
Verify actual version, TLS, checksums and extension. Test the application account from the host:
sudo apt install -y postgresql-client
APP_DB_PASSWORD="$(<secrets/app_db_password)" \
PGPASSWORD="${APP_DB_PASSWORD}" \
psql "host=127.0.0.1 port=5432 dbname=app user=appuser sslmode=verify-full sslrootcert=/opt/postgres18/tls/ca.crt" \
-c 'SELECT current_user, current_database(), ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();'
ssl should be t. Keep secrets out of history and use unset APP_DB_PASSWORD PGPASSWORD after checks. pg_isready alone does not test application permissions or backup freshness.
Pooling reduces backend pressure when max_connections fills. Install through the official PostgreSQL APT repository and check that the installed release is 1.26.1 or newer with the relevant security fixes. Stop if the repository only offers the vulnerable original 1.25.2 baseline:
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt update
sudo apt install -y pgbouncer
pgbouncer --version
Create /etc/pgbouncer/pgbouncer.ini:
[databases]
app = host=127.0.0.1 port=5432 dbname=app
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 300
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
max_prepared_statements = 200
server_tls_sslmode = verify-full
server_tls_ca_file = /etc/pgbouncer/ca.crt
admin_users = postgres
stats_users = postgres
ignore_startup_parameters = extra_float_digits
log_connections = 1
log_disconnections = 1
A protected cleartext userlist permits backend SCRAM authentication in this example:
cd /opt/postgres18
APP_DB_PASSWORD="$(<secrets/app_db_password)"
printf '"appuser" "%s"\n' "${APP_DB_PASSWORD}" > secrets/pgbouncer-userlist.txt
unset APP_DB_PASSWORD
sudo install -m 0640 -o postgres -g postgres \
secrets/pgbouncer-userlist.txt /etc/pgbouncer/userlist.txt
sudo install -m 0644 -o root -g root \
tls/ca.crt /etc/pgbouncer/ca.crt
sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status --no-pager pgbouncer
Protect this file. admin_users/stats_users do not create an authenticated console account: add an appropriate dedicated control user to the auth file and those lists if you need console access, without giving ordinary applications administrator credentials.
Connect applications to 127.0.0.1:6432/app through the actual private route. transaction pooling may break session state, temporary-table assumptions or cross-transaction SET; use session for incompatible workloads. Prepared-statement support is version/configuration dependent. Check file-descriptor limits when raising max_client_conn.
APP_DB_PASSWORD="$(</opt/postgres18/secrets/app_db_password)" \
PGPASSWORD="${APP_DB_PASSWORD}" \
psql -h 127.0.0.1 -p 6432 -U appuser -d app \
-c 'SELECT current_user, now();'
unset APP_DB_PASSWORD PGPASSWORD
Backend TLS is configured; client-to-pool TLS is not in this loopback-only example. Configure and verify it before using a cross-host client route.
Use daily pg_dump plus global roles/configuration; growing workloads may need pg_basebackup, WAL archives and PITR. Do not copy a live volume as a consistent backup.
cd /opt/postgres18
backup_time="$(date -u +%Y%m%dT%H%M%SZ)"
docker compose exec -T --user postgres postgres \
pg_dump -U postgres -d app -Fc \
> "backups/app-${backup_time}.dump"
docker compose exec -T --user postgres postgres \
pg_dumpall -U postgres --globals-only \
> "backups/globals-${backup_time}.sql"
sha256sum "backups/app-${backup_time}.dump" \
"backups/globals-${backup_time}.sql" \
> "backups/SHA256SUMS-${backup_time}"
Check each command's exit status before accepting files/hashes, use restrictive permissions and protect role password hashes. Keep local short-term, offsite regional and offline/independent-account copies, with scoped storage credentials, versions/object locks and tested downloads. Daily dumps alone do not provide continuous recovery.
Run the following only after switching to a separate matching-version test instance/project. Restore required roles and extensions from reviewed globals/configuration first. Confirm the chosen dump exists and checksums match. Do not execute the final dropdb until business checks are complete:
cd /opt/postgres18
latest_dump="$(find backups -name 'app-*.dump' -type f -print | sort | tail -1)"
docker compose exec -T --user postgres postgres \
createdb -U postgres -O appuser app_restore_test
docker compose exec -T --user postgres postgres \
pg_restore -U postgres -d app_restore_test \
--clean --if-exists < "${latest_dump}"
docker compose exec --user postgres postgres \
psql -U postgres -d app_restore_test \
-c "SELECT count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema');"
docker compose exec -T --user postgres postgres \
dropdb -U postgres app_restore_test
Verify exact business rows, extensions, sequences, permissions and application connection, not just table count. Record recovery time and intended recovery point; keep production unaffected.
Monitor CPU, RAM/swap, disk space/latency, connections, transactions, locks, WAL/replication and backup age:
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
Fix leaks and idle transactions and measure pooling before increasing max_connections from 100 to 1000. EXPLAIN (ANALYZE, BUFFERS) executes the query: use carefully for representative plans. Improve indexes/query design rather than treating shared_buffers as a cure.
An 18.x patch normally needs no pg_upgrade. Replace the selected tag/digest deliberately and complete backups/testing before:
cd /opt/postgres18
: "先完成备份和恢复抽查"
docker compose pull postgres
docker compose up -d postgres
docker compose exec --user postgres postgres \
psql -U postgres -d postgres -c 'SELECT version();'
A major 18-to-19 change needs pg_upgrade or logical migration with rehearsed compatibility and rollback. The versioned PGDATA layout does not perform that migration for you.
Check actual image ownership of server.key, adapting UID 999 if needed:
sudo chown 999:999 /opt/postgres18/tls/server.key
sudo chmod 600 /opt/postgres18/tls/server.key
docker compose restart postgres
Stop writes, inspect old volumes/project name and the /var/lib/postgresql mount. Keep original volumes and inspect a copy/read-only mount before initializing anything.
Inspect pg_stat_activity and SHOW POOLS; with an authorized console account. Fix connection lifecycle before increasing default_pool_size or max_connections.
Check SAN, sslrootcert, CA chain and time. Repair verify-full errors instead of using sslmode=disable.
The service intentionally binds 127.0.0.1. The following tunnel targets PgBouncer 6432, not the raw PostgreSQL port:
ssh -N -L 15432:127.0.0.1:6432 user@your-vps
Use local 127.0.0.1:15432; match client TLS settings to that pool listener. See connection troubleshooting.
- A supported exact 18.x patch is pinned, avoiding
latest. - Storage uses the correct
/var/lib/postgresqllayout. - 5432/6432 are loopback or restricted private listeners.
- PostgreSQL TCP uses TLS/SCRAM and applications avoid superusers.
- PgBouncer is patched and its mode is application-compatible.
- Memory/connections are based on measurements.
- Offsite backups have passed a documented isolated restore.
- Patches and major migrations have maintenance/recovery plans.
- Disk, connections, queries, locks, WAL and backup freshness alerting work.
Test the real workload and controlled restart/recovery before committing production data to the service.