PostgreSQL installation and setup

PostgreSQL listens only on localhost out of the box and ships with settings meant for a small machine. Below: two ways to install the server (a distro package and Docker), how to create a cluster, configure access through pg_hba.conf and set base parameters for load, with a check after every step

Context: Debian or Ubuntu, a single machine, PostgreSQL installed both as a system package and as a container. Commands differ on other distros, and the differences are called out

Symptom

A fresh instance answers psql as the local postgres user, but an application on another host is refused. Under load the picture changes: sorts spill to disk and the connection count hits the limit. Both symptoms come from the defaults: the configuration targets a minimal machine and local access

$ sudo -u postgres psql -Atc "show listen_addresses;"
localhost
$ sudo -u postgres psql -Atc "show shared_buffers;"
128MB

An application on another host cannot connect to a server like this, and on a machine with 16 GB of RAM, 128 MB of page cache means very little. These are not install errors but factory values

Diagnosis

First check that the server is installed and which cluster it serves

psql --version
pg_lsclusters
systemctl status postgresql --no-pager

pg_lsclusters comes only with the postgresql-common packages (Debian, Ubuntu). It prints the version, cluster name, port, status, owner, data directory and log. RHEL and its derivatives use different commands: the cluster is created by postgresql-setup --initdb and its state is read through systemctl status postgresql

If pg_lsclusters is missing, that is a separate case: linux: "command not found"

Next, find out which config the running server reads. The path depends on the install method, so do not guess it, ask the server

sudo -u postgres psql -Atc "show config_file;"
sudo -u postgres psql -Atc "show hba_file;"
sudo -u postgres psql -Atc "show data_directory;"

config_file is postgresql.conf, hba_file is pg_hba.conf, data_directory is the directory that holds the databases. On Debian and Ubuntu the configs usually live in /etc/postgresql/<version>/main/; in Docker they are inside the data directory

What the server really listens on is shown by ss

sudo ss -ltnp | grep postgres

If the output only has 127.0.0.1:5432 or [::1]:5432, TCP access is limited to the local machine. If there is no line at all, the server may work through a Unix socket only

Cause

The apt package creates a cluster automatically on install, and initdb applies minimal defaults. listen_addresses = 'localhost' binds the server to the loopback interface, because a PostgreSQL reachable from outside without access rules in place would be a hole

The second layer is pg_hba.conf (host-based authentication, access rules by connection type). The file is read top-down and the first matching rule wins. Authentication methods: peer compares the OS user name with the role name and works over a Unix socket only, md5 verifies the password against an MD5 hash, scram-sha-256 uses the modern SCRAM scheme. PostgreSQL 14 and newer use scram-sha-256 by default

The third layer is memory. shared_buffers sets the server's own page cache and is small by default. max_connections caps the number of connections, and in PostgreSQL every connection is a separate process with its own memory. An instance on defaults therefore does not scale under load even though it formally works

Fix

First, install with apt on Debian and Ubuntu

sudo apt update
sudo apt install -y postgresql postgresql-contrib

The postgresql-contrib package adds extensions such as pg_stat_statements and pgcrypto. The install creates a cluster and starts the service right away

Check the result

pg_lsclusters

The output should have a line with the version, the main cluster name, port 5432 and online status. If there is no cluster, create one manually

sudo pg_createcluster <version> main --start

The second path is Docker

docker run -d --name pg \
  -e POSTGRES_USER=app \
  -e POSTGRES_DB=app \
  -e POSTGRES_PASSWORD='<password>' \
  -p 127.0.0.1:5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16

On first start the official image runs initdb itself and creates the user, the database and the access files. Publishing the port as -p 127.0.0.1:5432:5432 keeps the database reachable from the host only; change the address deliberately if you need external access

A cluster can also be created manually, with initdb

sudo -u postgres initdb -D /var/lib/postgresql/data
sudo -u postgres pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb creates the data directory and the base configs. It must not run as root, so the command goes through sudo -u postgres

Network parameters and the port are set in postgresql.conf at the path from show config_file. The default listen_addresses = 'localhost' is exactly why a client from another host is refused, so for external access it is changed to a specific interface or to all addresses:

# postgresql.conf
listen_addresses = '*'    # the default 'localhost' binds the loopback interface only
port = 5432

listen_addresses is the list of interfaces on which the server accepts TCP connections. 'localhost' keeps the loopback interface only, '*' listens on all addresses. Opening '*' is safe only behind a firewall or on a private network: the parameter itself does not limit who can connect. External access also requires a host line for the application subnet in pg_hba.conf and a matching firewall rule

Port 5432 is changed when another instance already holds it. After changing listen_addresses and port you need a restart, not a config reload

sudo systemctl restart postgresql

Access rules are set in pg_hba.conf

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

Rule order matters: the server walks the file top-down and stops at the first match, so a broad rule above a narrow one overrides it. The trust method lets clients in without a password and is not acceptable in production. peer is handy for local administration: sudo -u postgres psql works without a password because the OS user name matches the role name

An edit to pg_hba.conf only needs a config reload

sudo systemctl reload postgresql

No restart is required: access rules are re-read on the fly

Next, create a dedicated role and database

sudo -u postgres psql

CREATE ROLE app LOGIN PASSWORD '<password>';
CREATE DATABASE app OWNER app;

The password does not end up in psql_history in plain text if you use \password app: the command prompts for it and applies password_encryption from the config. For an application keep the password in a secrets manager or in ~/.pgpass with mode 600

Check the connection as the new role

psql "postgresql://app@127.0.0.1:5432/app" -c "select current_user, current_database();"

The postgresql:// connection string is role, host, port and database. The password is prompted separately or taken from ~/.pgpass

Load parameters start with memory

shared_buffers is roughly 25% of RAM, effective_cache_size is an estimate of available cache (around 50–75% of RAM) for the planner, and work_mem is the memory per sort or hash operation within one connection

# postgresql.conf, example for a machine with 16 GB RAM
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 8MB
maintenance_work_mem = 512MB
max_connections = 200

effective_cache_size allocates no memory: it is a planner hint about how much data fits in the server and OS cache. work_mem is allocated per sort or hash operation, and there can be many connections, so the upper bound on consumption is max_connections × work_mem × concurrent operations. maintenance_work_mem is used by VACUUM, CREATE INDEX and other maintenance operations

shared_buffers, max_connections and the port require a restart; work_mem, effective_cache_size and maintenance_work_mem apply on reload

sudo systemctl reload postgresql

A logical copy is taken with pg_dump and does not block reads

pg_dump -Fc -f /backup/app.dump "postgresql://app@127.0.0.1:5432/app"

The -Fc flag writes a compressed archive that pg_restore unpacks

pg_restore -d app /backup/app.dump

Roles and cluster-wide privileges are not part of a single database; use pg_dumpall --globals-only for them. Never copy the data directory on a running server: the result would be an inconsistent snapshot

Verify

pg_isready -h 127.0.0.1 -p 5432

pg_isready reports whether the server accepts connections. The accepting connections response is the working case

sudo -u postgres psql -c "show shared_buffers; show max_connections;"

Make sure the parameters took effect and the expected values come back

psql "postgresql://app@127.0.0.1:5432/app" -c "\conninfo"

\conninfo prints the role, database, host and port of the current connection. If an application in a container cannot see the database, check that port publishing and listen_addresses agree

Test a restore on a separate database rather than over the working one

createdb app_restore && pg_restore -d app_restore /backup/app.dump

Pitfalls

• A broad pg_hba.conf rule overrides a narrow one. The first match wins, so host all all 0.0.0.0/0 md5 above the application rule disables the check you configured below
• listen_addresses = '*' without a firewall exposes the database to the network. The parameter does not filter clients; only pg_hba.conf together with network policy does
• peer does not work over TCP. The method compares the OS user, which a network connection does not have, so remote clients need scram-sha-256
• The role password does not change with POSTGRES_PASSWORD in the container. The variable is processed on the first initdb only; for an existing cluster run ALTER ROLE ... PASSWORD
• work_mem multiplies per connection. A large value on a server with hundreds of connections leads to memory pressure; cap it with a connection pool such as PgBouncer
• pg_dump is not a replacement for WAL archiving. Point-in-time recovery needs a stream of physical copies; a logical dump gives a snapshot as of its start
• Distro differences. pg_lsclusters and pg_createcluster are available on Debian and Ubuntu; on RHEL the cluster is created by postgresql-setup --initdb

Takeaway

Installing PostgreSQL comes down to three layers: the package or container creates the cluster and the service, listen_addresses and pg_hba.conf decide who connects and how, and the memory and connection parameters decide behaviour under load. Check state with pg_lsclusters and pg_isready, and take copies with pg_dump

If the server is running but connections are refused, that is a separate case: postgresql: connection refused

For tuning to a specific workload and query auditing there are the PostgreSQL consulting and backend development services