How to Install PostgreSQL 16 on Ubuntu 24.04 — Production-Ready Database Setup

PostgreSQL is the database of choice for teams that want ACID guarantees, first-class SQL, and a genuinely extensible engine that scales from a side project to petabyte-scale analytics. This guide walks you through a complete, production-oriented install of PostgreSQL 16 on Ubuntu 24.04: from adding the official PGDG apt repository through streaming replication, encrypted backups with pgBackRest, connection pooling with PgBouncer, and essential extensions like pgvector, TimescaleDB and PostGIS.

By the end, you will have a hardened primary with a hot-standby replica, automated point-in-time backups, query statistics collection, and a pooler in front of it all. No managed-service lock-in, no per-IOPS surprise bill.

Skip the manual work? Our Professional VPS plan ships with NVMe storage, private networking for replica traffic, and generous RAM for buffer cache. Launch a Professional VPS in under 60 seconds.

Table of Contents

Why Self-Host PostgreSQL vs. RDS or Supabase?

Managed Postgres services like AWS RDS, Google Cloud SQL, Supabase, and Neon are convenient, but they trade away control for that convenience. For teams running a VPS or a small fleet, self-hosting remains the more economical and flexible path.

• Predictable, flat-rate cost — A Professional VPS is a fixed monthly bill regardless of queries per second, storage IOPS, or egress. RDS bills for instance hours, provisioned IOPS, cross-AZ traffic, backup storage beyond the included tier, and outbound bandwidth. A modest workload that costs EUR 29/month on your own VPS routinely runs USD 200-400/month on RDS.
• Full tuning freedom — On managed Postgres you get a parameter group with a curated subset of settings. Self-hosting lets you change shared_buffers, huge_pages, kernel vm.overcommit_memory, filesystem mount options, and transaction log placement. You can move pg_wal to a separate disk, enable io_uring, or ship WAL to an object store of your choice.
• Any extension, any version — Managed providers ship a fixed list of extensions. Self-hosting lets you install pgvector, TimescaleDB, PostGIS, pg_partman, pg_cron, citus, hypopg, and in-house extensions compiled from source. You also control the exact minor version and when you upgrade.
• No vendor lock-in — Your data lives on a filesystem you control. You can move to any other Postgres host (managed or self-hosted) with a standard pg_dump or pgBackRest restore. No proprietary wire protocol, no API quirks.
• GDPR and data residency — Self-hosting on an EU-hosted VPS keeps all data within a single, known jurisdiction — no cross-region replicas, no control-plane metadata leaving the region.
• No noisy-neighbour tax — Multi-tenant managed services throttle IOPS and CPU during bursts. A dedicated VPS with NVMe gives you deterministic performance.

Cost Comparison: Self-Hosted vs. Managed Postgres

Prerequisites

Before you begin, make sure you have:

• A VPS running Ubuntu 24.04 LTS with root or sudo access.
• SSH access to your server.
• At least 8 GB of RAM for a production workload (16 GB+ recommended so shared_buffers can sit around 25% of RAM with headroom to spare).
• At least 50 GB of free disk space on fast NVMe storage. Databases are I/O bound — do not skimp here.
• A second VPS if you intend to set up streaming replication (same region, private networking recommended).

Recommended Plan: Professional

For a production Postgres primary with a replica and healthy headroom for extensions and backups, we recommend the Professional VPS:

- 6 vCPU cores

- 16 GB RAM

- 200 GB NVMe SSD

- Unmetered bandwidth

Pair it with a second, equivalently sized node for the replica and a small storage VPS or S3-compatible bucket for off-box backups.

Connect to your server:

ssh root@your-server-ip

Step 1: Update System Packages

Bring the system up to date so dependency resolution for the PGDG packages is clean.

sudo apt update && sudo apt upgrade -y

Expected (abbreviated):

Reading package lists... Done
Building dependency tree... Done
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

If a new kernel was installed, reboot before continuing:

sudo reboot

Step 2: Add the PGDG Apt Repository

Ubuntu ships PostgreSQL in its own archive, but those packages trail the upstream release by months and rarely receive minor-version bumps promptly. The PostgreSQL Global Development Group (PGDG) maintains an official apt repository that publishes every supported major version within days of release.

Install the helper packages and add the repo:

sudo apt install -y curl ca-certificates gnupg lsb-release

sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -fsSL -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc

echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  | sudo tee /etc/apt/sources.list.d/pgdg.list

Refresh the package index so apt sees the new repository:

sudo apt update

Expected output:

Get:1 https://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease [129 kB]
Fetched 129 kB in 1s (187 kB/s)
Reading package lists... Done

Step 3: Install PostgreSQL 16

Install the server, contrib modules (pg_stat_statements, btree_gin, etc.), and the client tools:

sudo apt install -y postgresql-16 postgresql-contrib-16 postgresql-client-16

The package post-install hook performs several things automatically on Debian-family systems:

Verify the service is running:

sudo systemctl status postgresql@16-main

Expected:

● [email protected] - PostgreSQL Cluster 16-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime)
     Active: active (running) since Thu 2026-04-16 09:00:00 UTC; 30s ago
   Main PID: 4321 (postgres)
      Tasks: 7 (limit: 18823)
     Memory: 20.4M

Check the version from the client:

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

Expected:

version
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.4 (Ubuntu 16.4-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu ...) ...

Step 4: Cluster Layout and initdb Paths

On Debian/Ubuntu the paths differ slightly from a generic initdb install. Commit these to memory — you will reference them constantly:

sudo pg_createcluster 16 analytics --port=5433 --start

To list clusters:

pg_lsclusters

Expected:

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

Step 5: Tune postgresql.conf

The default postgresql.conf is intentionally conservative — it assumes 1 GB of RAM and spinning disks. On a Professional VPS (16 GB RAM, NVMe) you are leaving massive performance on the table if you leave the defaults.

Edit the config:

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

Apply these settings, sized for 16 GB RAM. Adjust proportionally for larger or smaller machines.

# ------------ Connections ------------
listen_addresses = '*'               # or 'localhost,10.0.0.5' for private nets
max_connections = 200                # front with PgBouncer if you need more
------------ Memory ------------
shared_buffers = 4GB                 # ~25% of RAM
effective_cache_size = 12GB          # ~75% of RAM — hint to planner
work_mem = 32MB                      # per-sort, per-hash; multiply by concurrent queries
maintenance_work_mem = 1GB           # VACUUM, CREATE INDEX, ALTER TABLE
wal_buffers = 16MB                   # 1/32 of shared_buffers, capped at 16MB
------------ Checkpoints & WAL ------------
wal_level = replica                  # 'replica' for streaming rep, 'logical' for CDC
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
------------ Planner ------------
random_page_cost = 1.1               # NVMe/SSD — keep low (default 4.0 is for HDD)
effective_io_concurrency = 200       # NVMe can service many in-flight reads
default_statistics_target = 100
------------ Parallelism ------------
max_worker_processes = 6             # match vCPU count
max_parallel_workers = 6
max_parallel_workers_per_gather = 3
max_parallel_maintenance_workers = 3
------------ Logging ------------
log_min_duration_statement = 500ms   # log slow queries
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_line_prefix = '%m [%p] %q%u@%d '
------------ Replication (prepare for Step 9) ------------
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on

Sizing the Memory Knobs

• shared_buffers — Postgres's own buffer cache. 25% of RAM is the battle-tested default on Linux. Going higher can backfire because the kernel page cache also buffers the same data; double buffering wastes memory.
• effective_cache_size — Not an allocation, just a hint to the query planner about how much data it can expect to be cached (Postgres's own + the OS page cache combined). Set to ~75% of RAM.
• work_mem — Memory available per sort or hash operation. A single query can use multiples of this (one per operator). With 200 connections and work_mem = 32MB, peak worst-case is roughly 200 N_ops 32MB — a reason to sit PgBouncer in front and keep max_connections modest.
• maintenance_work_mem — Used by VACUUM, index builds, and ALTER TABLE. Big values dramatically speed up index creation.
• wal_buffers — WAL write buffer. 16MB is the upper useful limit for most workloads.

sudo systemctl restart postgresql@16-main

Verify the running configuration:

sudo -u postgres psql -c "SHOW shared_buffers; SHOW work_mem; SHOW effective_cache_size;"

Step 6: Configure Authentication in pg_hba.conf

pg_hba.conf ("host-based authentication") decides, for every connection attempt, which authentication method is required. The rules are evaluated top-to-bottom and the first match wins — order matters.

Edit the file:

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

A sensible production baseline:

# TYPE  DATABASE    USER         ADDRESS           METHOD
local   all         postgres                       peer
local   all         all                            scram-sha-256
host    all         all          127.0.0.1/32      scram-sha-256
host    all         all          ::1/128           scram-sha-256
host    replication replicator   10.0.0.0/24       scram-sha-256
host    all         all          10.0.0.0/24       scram-sha-256
Reject everything else explicitly
host    all         all          0.0.0.0/0         reject

Authentication Methods Explained

• peer — Only valid for Unix-socket (local) connections. Postgres checks the OS username and matches it against a Postgres role of the same name. This is why sudo -u postgres psql works without a password.
• scram-sha-256 — The modern password method. Passwords are stored as salted SCRAM verifiers; the actual password never travels the wire, and stolen password hashes cannot be replayed. This is the correct default for anything Postgres 10+.
• md5 — Legacy password method. Still accepted but weaker. Use only for legacy clients that do not speak SCRAM. To force all users to upgrade, set password_encryption = scram-sha-256 in postgresql.conf (default in 16) and have users re-set their passwords.
• trust — No password required. Never use on host rules. Useful only for local Unix-socket bootstrapping.
• reject — Explicitly deny. A trailing reject on 0.0.0.0/0 is a good belt-and-braces practice.
• cert — Require client TLS certificate. Highest assurance for service-to-service.

sudo systemctl reload postgresql@16-main

Migrating Legacy md5 Users to SCRAM

Check which users still have md5 hashes:

SELECT rolname, substring(rolpassword, 1, 3) AS algo
FROM pg_authid
WHERE rolpassword IS NOT NULL;

Values starting with md5 need upgrading. Have each user set their password again while password_encryption = scram-sha-256 is in effect:

ALTER ROLE alice PASSWORD 'new-strong-password';

The new hash is automatically written as SCRAM.

Step 7: Basics — createdb, createuser, psql

With the server running and authentication configured, create your first database and user.

Set the postgres Superuser Password

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'a-very-long-passphrase';"

Create an Application User and Database

Postgres ships two convenience wrappers around SQL:

sudo -u postgres createuser --interactive --pwprompt app

Enter name of role to add: app
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

sudo -u postgres createdb --owner=app appdb

The equivalent SQL, if you prefer explicit control, is:

CREATE ROLE app LOGIN PASSWORD 'app-password';
CREATE DATABASE appdb OWNER app;

Connect with psql

From the postgres OS user:

sudo -u postgres psql -d appdb

From any user over TCP:

psql "host=127.0.0.1 port=5432 dbname=appdb user=app"

Useful psql meta-commands:

• \l — list databases
• \du — list roles
• \dt — list tables in current database
• \d table_name — describe a table
• \c dbname — switch database
• \x — toggle expanded (row-per-column) output
• \timing — show query duration
• \q — quit

CREATE TABLE items (id serial PRIMARY KEY, name text NOT NULL, created_at timestamptz DEFAULT now());
INSERT INTO items (name) VALUES ('first'), ('second');
SELECT * FROM items;

Step 8: Role Management and Privileges

Postgres uses roles for both users and groups — there is no separate GROUP concept. A role with the LOGIN attribute is effectively a user; a role without it behaves like a group.

Create a Group Role and Grant to Users

CREATE ROLE readonly;                         -- group role, no LOGIN
CREATE ROLE reporter LOGIN PASSWORD 'x';      -- user role
GRANT readonly TO reporter;

Fine-Grained Privileges

-- Schema access
GRANT USAGE ON SCHEMA public TO readonly;
-- Read all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
-- Read all future tables created by role 'app'
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

Revoke the Public-Schema Footgun

Historically, the public schema was writable by any role. In Postgres 15+ the default was changed so only the database owner can create in public, but you should verify and re-apply explicitly on older clusters migrated in:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Useful Inspection Queries

-- List all roles and their attributes
SELECT rolname, rolsuper, rolcanlogin, rolreplication, rolconnlimit
FROM pg_roles
ORDER BY rolname;
-- Which roles are members of which?
SELECT r.rolname AS member, g.rolname AS group_role
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.member
JOIN pg_roles g ON g.oid = am.roleid
ORDER BY g.rolname;

Step 9: Streaming Replication — Primary + Replica

A hot-standby replica gives you read-scaling and a ready-to-promote failover target. PostgreSQL's built-in streaming replication ships WAL records from primary to replica over a long-lived TCP connection.

Assume:

• Primary — 10.0.0.10, already set up with the tuned postgresql.conf above.
• Replica — 10.0.0.11, a fresh Ubuntu 24.04 VPS with PostgreSQL 16 installed identically (same major version is required).

On the Primary — Create the Replication Role

sudo -u postgres psql <<'SQL'
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replication-secret';
SELECT * FROM pg_create_physical_replication_slot('replica1');
SQL

The replication slot prevents the primary from recycling WAL the replica has not consumed yet — crucial for replicas that occasionally disconnect.

The pg_hba.conf rule added in Step 6 already permits this:

host    replication replicator   10.0.0.0/24       scram-sha-256

Make sure wal_level = replica, max_wal_senders, and max_replication_slots are set (they are in the tuned config above), then reload:

sudo systemctl reload postgresql@16-main

On the Replica — Take a Base Backup

Stop Postgres on the replica and wipe the data directory:

sudo systemctl stop postgresql@16-main
sudo -u postgres rm -rf /var/lib/postgresql/16/main

Clone the primary with pg_basebackup:

sudo -u postgres pg_basebackup \
  --host=10.0.0.10 \
  --username=replicator \
  --pgdata=/var/lib/postgresql/16/main \
  --write-recovery-conf \
  --slot=replica1 \
  --wal-method=stream \
  --progress \
  --checkpoint=fast \
  --verbose

You will be prompted for the replication password. The --write-recovery-conf flag writes two files:

• standby.signal — a zero-byte file that tells Postgres on startup to come up as a hot standby.
• postgresql.auto.conf — amended with the primary_conninfo and primary_slot_name settings.

sudo systemctl start postgresql@16-main

Verify Replication

On the primary:

SELECT client_addr, state, sync_state, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Expected:

client_addr | state     | sync_state | lag_bytes
-------------+-----------+------------+-----------
 10.0.0.11   | streaming | async      |         0

On the replica:

SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp() AS replay_lag;

Expected:

pg_is_in_recovery |    replay_lag
-------------------+------------------
 t                 | 00:00:00.012345

The replica is read-only. Direct read traffic at it with target_session_attrs=read-only in your connection string, or route SELECTs at the application layer.

Promoting a Replica (Failover)

If the primary is lost:

sudo -u postgres pg_ctl -D /var/lib/postgresql/16/main promote

The replica exits recovery and becomes writable. Re-point applications at the new primary and rebuild the old one as a fresh replica.

Step 10: Backups with pgBackRest

pg_dump is fine for small databases and schema snapshots, but a production database needs physical, incremental, point-in-time-restore (PITR) capable backups. pgBackRest is the community standard.

Install pgBackRest

On both the Postgres host and the dedicated backup host (if separate):

sudo apt install -y pgbackrest

Configure a Repository

This example uses a local repository at /var/lib/pgbackrest. For production, point at a second VPS over SSH or an S3-compatible bucket.

sudo mkdir -p /var/lib/pgbackrest /var/log/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest /var/log/pgbackrest
sudo chmod 750 /var/lib/pgbackrest /var/log/pgbackrest

Edit /etc/pgbackrest/pgbackrest.conf:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=CHANGE-ME-TO-A-LONG-RANDOM-STRING
compress-type=zst
compress-level=6
process-max=3
log-level-console=info
log-level-file=detail
start-fast=y
[main]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
pg1-user=postgres

Enable Archiving in postgresql.conf

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

Restart Postgres so the archive_mode change takes effect:

sudo systemctl restart postgresql@16-main

Create the Stanza and Take the First Backup

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main --type=full backup

Schedule Backups with cron

Edit the postgres user's crontab:

sudo -u postgres crontab -e

# Full backup Sunday 02:00
0 2   0 pgbackrest --stanza=main --type=full backup
Incremental backup every other day 02:00
0 2   1-6 pgbackrest --stanza=main --type=incr backup

Restore

To restore to a specific point in time:

sudo systemctl stop postgresql@16-main
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pgbackrest --stanza=main \
  --type=time --target="2026-04-15 14:00:00" restore
sudo systemctl start postgresql@16-main

Test restores in a staging environment monthly. A backup you have never restored is not a backup.

Step 11: Query Statistics with pg_stat_statements

pg_stat_statements is the single most valuable extension for any production Postgres deployment. It records aggregate execution statistics for every normalised query the server has executed — number of calls, total and mean execution time, rows returned, shared-buffer hits and reads, and WAL bytes generated.

Enable it by preloading the shared library:

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

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

Restart (required for shared_preload_libraries):

sudo systemctl restart postgresql@16-main

Create the extension in every database you want to profile:

CREATE EXTENSION pg_stat_statements;

Find the slowest queries:

SELECT
  substring(query, 1, 80) AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Reset statistics after a tuning change to see fresh numbers:

SELECT pg_stat_statements_reset();

Step 12: Essential Extensions (pgvector, TimescaleDB, PostGIS)

PostgreSQL's extensibility is what separates it from every other mainstream relational database. Three extensions are worth installing on almost every cluster.

pgvector — Vector Similarity Search

pgvector turns Postgres into a vector database suitable for semantic search, RAG pipelines, and recommendation systems. It supports exact and approximate nearest-neighbour search with ivfflat and hnsw indexes.

Install from the PGDG repo:

sudo apt install -y postgresql-16-pgvector

CREATE EXTENSION vector;
CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)   -- OpenAI ada-002 dimensionality
);
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);
-- Find 5 nearest neighbours
SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;

For a full walkthrough including embedding pipelines and hybrid lexical + vector search, see our pgvector guide.

TimescaleDB — Time-Series Superpowers

TimescaleDB adds automatic partitioning ("hypertables"), continuous aggregates, columnar compression, and data-retention policies — everything you need to manage high-ingest time-series workloads in regular Postgres.

Install from the Timescale apt repo (full instructions and tuning tips in our TimescaleDB install guide):

sudo apt install -y postgresql-16-timescaledb
sudo timescaledb-tune --quiet --yes
sudo systemctl restart postgresql@16-main

CREATE EXTENSION timescaledb;
CREATE TABLE metrics (time timestamptz NOT NULL, device_id int, value double precision);
SELECT create_hypertable('metrics', 'time');

PostGIS — Geospatial

PostGIS adds geometry/geography types, spatial indexes (GiST, SP-GiST), and a vast library of ST_* functions for distance, intersection, transformation, and routing.

sudo apt install -y postgresql-16-postgis-3

CREATE EXTENSION postgis;
CREATE TABLE stores (
  id serial PRIMARY KEY,
  name text,
  location geography(Point, 4326)
);
CREATE INDEX stores_location_idx ON stores USING GIST (location);
-- All stores within 5 km of a point
SELECT name
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(2.3522, 48.8566)::geography, 5000);

Step 13: Connection Pooling with PgBouncer

PostgreSQL allocates a process per backend connection. At the scale of a few hundred connections this is fine; past that, memory pressure and context-switching overhead start to hurt. PgBouncer is a lightweight, transaction-level connection pooler that sits between your application and Postgres, multiplexing thousands of client connections onto a small pool of server connections.

Install it:

sudo apt install -y pgbouncer

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
appdb = host=127.0.0.1 port=5432 dbname=appdb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
reserve_pool_size = 5
server_idle_timeout = 600
server_tls_sslmode = prefer
admin_users = postgres
stats_users = postgres

Build userlist.txt with the SCRAM verifier from Postgres:

sudo -u postgres psql -tAc \
  "SELECT '\"'||rolname||'\" \"'||rolpassword||'\"' FROM pg_authid WHERE rolname IN ('app','postgres');" \
  | sudo tee /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 640 /etc/pgbouncer/userlist.txt

Start PgBouncer:

sudo systemctl enable --now pgbouncer

Point your application at port 6432 instead of 5432. Verify:

psql "host=127.0.0.1 port=6432 dbname=appdb user=app"

Pool Modes at a Glance

• session — One server connection per client session. Compatible with everything, pools least aggressively.
• transaction — Server connection released at end of each transaction. Recommended default; blocks session-scoped features like SET, LISTEN, and prepared statements (use prepared_statements = false in libpq clients, or Postgres 16's max_prepared_statements support in PgBouncer 1.21+).
• statement — Released after each statement. Most aggressive; blocks multi-statement transactions entirely.

Troubleshooting

Log Location

sudo tail -f /var/log/postgresql/postgresql-16-main.log

For systemd-level issues:

sudo journalctl -u postgresql@16-main -f

FAQ

Should I use the Ubuntu-shipped postgresql package or PGDG?

Use PGDG. Ubuntu 24.04 ships a recent Postgres, but PGDG gives you every supported major version (12 through 17 at time of writing), faster minor-version updates with security fixes, and parallel installability of multiple major versions on the same host — essential when you plan in-place upgrades with pg_upgrade. Mixing PGDG and Ubuntu-shipped packages on the same host can cause conflicts; pick one and stay with it.

How much RAM should I actually give to shared_buffers?

The classic advice of 25% of RAM is a solid default and almost always the right starting point. On machines with more than 64 GB of RAM you can often push to 40% if you have verified with pg_buffercache that the working set is large and mostly hot. Going much higher than that rarely helps because the Linux page cache is doing useful work with the same pages — you end up double-buffering. On machines under 4 GB of RAM the proportional approach breaks down; set at least 512 MB.

Do I need connection pooling if I already have client-side pools?

Usually yes. Client-side pools (HikariCP, pg-pool, ActiveRecord) are scoped per application process. If you run a dozen app containers each with a pool of 20 connections, Postgres sees 240 connections even when only a handful are actively running queries — and each idle backend still consumes ~10 MB of RAM. PgBouncer in transaction mode reduces that footprint by 10x or more and lets you raise total application concurrency without raising max_connections on the server.

How do I upgrade from Postgres 16 to 17 with minimal downtime?

For small databases (< 50 GB) pg_upgrade --link on the same host completes in seconds. For larger or zero-downtime requirements, use logical replication: set wal_level = logical on the old cluster, publish all tables, install the new major version in parallel on the same host (PGDG supports side-by-side installs), subscribe, wait for replication to catch up, switch traffic, and decommission the old cluster. The pg_createcluster 17 main --port=5433 command will run 17 alongside 16 cleanly.

Can PostgreSQL replace MariaDB or MySQL for my application?

In almost every case, yes — and usually with better feature coverage. Postgres has stricter SQL standards compliance, richer types (JSONB, arrays, ranges, geometry), true MVCC without table-level locks on DDL in most cases, and a more extensible engine. The main migration friction comes from MySQL-specific SQL (backtick identifiers, ON DUPLICATE KEY UPDATE — use INSERT ... ON CONFLICT in Postgres), case-insensitive collations (Postgres 12+ supports ICU collations with any case sensitivity), and auto-incrementing columns (SERIAL or GENERATED ... AS IDENTITY). If you are weighing the two, see our MariaDB install guide for the comparison.

What is the practical difference between a warm standby, a hot standby, and a streaming replica?

All three are physical replicas. A warm standby receives WAL (usually via log shipping) but does not accept queries. A hot standby receives WAL and serves read-only queries. Streaming replication is the transport mechanism: WAL is shipped over a persistent TCP connection rather than file-by-file. Modern deployments use streaming + hot standby together, which is what Step 9 sets up. Synchronous replication (synchronous_standby_names) adds durability at the cost of write latency — useful for financial workloads, overkill for most SaaS.

How often should I run VACUUM, and do I need to worry about transaction ID wraparound?

In normal operation you should not run VACUUM manually — the autovacuum daemon handles it. What you should do is monitor that autovacuum is keeping up: SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;. If dead tuples grow faster than autovacuum can clean them, tune autovacuum_vacuum_cost_limit upward (default 200 is conservative; 2000 is reasonable on NVMe) or raise autovacuum_max_workers. Transaction ID wraparound is a real concern on very high-write-rate clusters — monitor with SELECT datname, age(datfrozenxid) FROM pg_database; and ensure the maximum stays well below 2 billion.

Next Steps

Now that you have a production-ready PostgreSQL 16 cluster, here are recommended follow-ups:

• Add monitoring — Deploy pgwatch2 or Prometheus's postgres_exporter to graph connections, replication lag, cache hit ratio, and autovacuum activity. Alerts on replication lag and disk usage are the bare minimum.
• Turn on TLS — Generate certificates with Let's Encrypt or your internal CA, set ssl = on in postgresql.conf, and require TLS for all non-local pg_hba.conf entries (hostssl instead of host).
• Add a vector store — Install pgvector to support semantic search and RAG workloads directly in your existing database.
• Go time-series native — Add TimescaleDB for ingest-heavy telemetry and analytics.
• Read the official docs — The PostgreSQL documentation is exceptional and is the authoritative reference for every setting in this guide.
• Compare with MySQL-family — If you are still deciding on an engine, our MariaDB install guide walks through the equivalent setup for the MySQL-family world.

Run PostgreSQL on the Right Hardware

Databases are unforgiving on slow disks and skinny RAM. Our Professional VPS ships with NVMe storage, generous memory for shared_buffers, and private networking for replica traffic — everything a production Postgres needs.

- 6 vCPU cores

- 16 GB RAM

- 200 GB NVMe SSD

- Unmetered bandwidth

- Private network for replication

Launch a Professional VPS and run the install above in the next 30 minutes.