Scrolls
5 min read
On this page

Installation & Configuration

Installing PostgreSQL

Linux (Debian/Ubuntu)

The PostgreSQL Global Development Group maintains official APT repositories with the latest versions:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-16

This installs the server, client tools, and creates a postgres system user. The cluster initializes automatically and starts on port 5432.

Linux (RHEL/Fedora)

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

On RHEL-based systems, you must explicitly initialize the cluster and start the service.

macOS

Homebrew is the simplest path:

brew install postgresql@16
brew services start postgresql@16

Postgres.app is an alternative that provides a menu bar icon and bundles PostGIS and other extensions. It is convenient for development but not suitable for production.

Docker

docker run -d \
    --name postgres \
    -e POSTGRES_PASSWORD=secretpassword \
    -p 5432:5432 \
    -v pgdata:/var/lib/postgresql/data \
    postgres:16

The -v pgdata flag creates a named volume so data persists across container restarts. Without it, stopping the container destroys your data.

Connect from the host:

psql -h localhost -U postgres

The Data Directory

Every Postgres cluster stores its data in a single directory, commonly called PGDATA. On Debian/Ubuntu, this is typically /var/lib/postgresql/16/main. On RHEL, it is /var/lib/pgsql/16/data.

Key contents:

PGDATA/
    postgresql.conf       # Main configuration
    pg_hba.conf           # Client authentication
    pg_ident.conf         # User name mapping
    base/                 # Per-database subdirectories
    global/               # Cluster-wide tables
    pg_wal/               # Write-ahead log segments
    pg_xact/              # Transaction commit status
    postmaster.pid        # PID file for the running server

Never edit files inside base/ or pg_wal/ manually. Corruption is the guaranteed outcome.

postgresql.conf: The Settings That Matter

Postgres ships with conservative defaults designed to run on minimal hardware. A production server needs tuning. These are the settings that matter most.

shared_buffers

The amount of memory Postgres uses for caching data pages. The default (128MB) is absurdly low for any real workload.

# Set to 25% of total RAM as a starting point
shared_buffers = 4GB    # on a 16GB server

Going above 25% of RAM rarely helps because the OS page cache handles the rest. On machines with 256GB+ RAM, 8-16GB is often enough.

work_mem

Memory allocated per sort or hash operation, per query. This is not a global pool. A complex query with five hash joins uses 5x this value.

# Start conservative, increase for analytical workloads
work_mem = 64MB

Setting this too high on an OLTP system with hundreds of connections will exhaust memory. Calculate: max_connections * work_mem * average_sorts_per_query should fit in available RAM.

effective_cache_size

Tells the planner how much memory is available for caching (shared_buffers + OS cache). This does not allocate memory. It only affects query planning decisions.

# Set to 50-75% of total RAM
effective_cache_size = 12GB    # on a 16GB server

Setting this too low makes the planner favor sequential scans over index scans.

max_connections

The maximum number of concurrent connections. Each connection consumes roughly 5-10MB of RAM and runs in its own OS process.

# Most applications need far fewer than the default 100
max_connections = 200

If you think you need 500+ connections, you need a connection pooler (PgBouncer), not more connections. Postgres performance degrades with too many active backends competing for CPU and locks.

maintenance_work_mem

Memory for maintenance operations: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY.

# Can be much larger than work_mem
maintenance_work_mem = 1GB

These operations run infrequently, so a generous allocation speeds up index builds and vacuum runs.

wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

These control how WAL data is written and how frequently checkpoints occur. The defaults are reasonable for small systems but too conservative for write-heavy workloads.

Logging

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 1000   # log queries slower than 1 second
log_line_prefix = '%t [%p]: user=%u,db=%d '
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0                  # log all temp file usage

Slow query logging is one of the most valuable production settings. Start with 1000ms and lower it as you optimize.

Applying Configuration Changes

Most settings take effect after a reload (no restart required):

sudo systemctl reload postgresql

Or from within psql:

SELECT pg_reload_conf();

Some settings (shared_buffers, max_connections, wal_buffers) require a full restart:

sudo systemctl restart postgresql

Check which settings need a restart:

SELECT name, setting, context
FROM pg_settings
WHERE context = 'postmaster';

pg_hba.conf: Client Authentication

The pg_hba.conf file controls who can connect, from where, and how they authenticate. Each line is a rule, evaluated top to bottom. First match wins.

# TYPE    DATABASE    USER    ADDRESS         METHOD
local     all         all                     peer
host      all         all     127.0.0.1/32    scram-sha-256
host      all         all     ::1/128         scram-sha-256
host      myapp       myapp   10.0.0.0/8      scram-sha-256
host      all         all     0.0.0.0/0       reject

Common authentication methods:

  • peer: OS user must match database user. Only for local (Unix socket) connections.
  • scram-sha-256: Password-based, cryptographically strong. Use this for network connections.
  • md5: Legacy password method. Weaker than scram-sha-256. Migrate away from it.
  • cert: TLS client certificate authentication. Strongest option for production.
  • reject: Explicitly deny the connection.

After editing pg_hba.conf, reload:

sudo systemctl reload postgresql

A common mistake is putting a broad trust or reject rule above more specific rules. Rules are evaluated in order. Put specific rules first.

Starting, Stopping & Reloading

systemd (Most Linux Systems)

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql
sudo systemctl status postgresql

pg_ctl (Direct Control)

pg_ctl -D /var/lib/postgresql/16/main start
pg_ctl -D /var/lib/postgresql/16/main stop -m fast
pg_ctl -D /var/lib/postgresql/16/main reload
pg_ctl -D /var/lib/postgresql/16/main status

Stop modes:

  • smart: Wait for all clients to disconnect. Can hang indefinitely.
  • fast: Roll back active transactions and disconnect clients. The normal way to stop.
  • immediate: Abort all processes. Requires crash recovery on next start. Emergency only.

macOS (Homebrew)

brew services start postgresql@16
brew services stop postgresql@16
brew services restart postgresql@16

psql Basics

psql is the standard command-line client. It is the tool every Postgres user should learn first.

Connecting

psql -h localhost -p 5432 -U myuser -d mydb

Or with a connection string:

psql "postgresql://myuser:mypassword@localhost:5432/mydb?sslmode=require"

Essential Commands

\l          -- list databases
\c mydb     -- connect to a different database
\dt         -- list tables in current schema
\d users    -- describe a table (columns, indexes, constraints)
\di         -- list indexes
\du         -- list roles/users
\df         -- list functions
\q          -- quit

Running SQL

SELECT version();

                          version
------------------------------------------------------------
 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by ...

Creating a Database & User

CREATE USER myapp WITH PASSWORD 'strong_password_here';
CREATE DATABASE myappdb OWNER myapp;
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myapp;

Common Pitfalls

  • Running with default shared_buffers (128MB). This is never appropriate for production. Even a small server should allocate at least 1GB.
  • Setting max_connections to 1000. Each connection is a process. Use PgBouncer for connection pooling instead.
  • Using trust authentication in pg_hba.conf. Trust means no password required. Acceptable for local development, dangerous in production.
  • Editing the wrong postgresql.conf. On Debian/Ubuntu, configuration lives in /etc/postgresql/16/main/, not in the data directory. Check SHOW config_file; to find the active file.
  • Forgetting to reload after configuration changes. Changes to postgresql.conf and pg_hba.conf do not take effect until you reload or restart.
  • Not setting up logging from the start. Slow query logging catches performance problems early. Enable it on day one.

Key Takeaways

  • Install from official repositories to get the latest version and clean upgrade paths.
  • The four most impactful settings are shared_buffers, work_mem, effective_cache_size, and max_connections. Tune these first.
  • pg_hba.conf controls authentication. Rules are evaluated top to bottom. Use scram-sha-256 for network connections.
  • Most configuration changes only require a reload, not a restart. Check the context column in pg_settings.
  • psql is the essential client tool. Learn the backslash commands before reaching for a GUI.
  • Always set up slow query logging in production. It is the single most useful diagnostic tool.