PostgreSQL Setup and Configuration#

Every PostgreSQL deployment boils down to three things: get the binary running, configure who can connect, and tune the memory settings.

Installation Methods#

Package Managers#

On Debian/Ubuntu, use the official PostgreSQL APT repository:

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

On macOS: brew install postgresql@16 && brew services start postgresql@16

On 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

Config files live at /etc/postgresql/16/main/ (Debian) or /var/lib/pgsql/16/data/ (RHEL).

Docker#

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

Connect with docker exec -it postgres psql -U postgres -d myapp. To pass custom config, mount a file and add -c 'config_file=/etc/postgresql/postgresql.conf'.

Helm in Kubernetes#

helm repo add bitnami https://charts.bitnami.com/bitnami
helm install my-pg bitnami/postgresql -n databases --create-namespace \
  --set auth.postgresPassword=adminpass \
  --set auth.database=myapp \
  --set auth.username=appuser \
  --set auth.password=apppass

For PostgreSQL 15+, you must also fix public schema ownership – see the separate article on PostgreSQL 15+ permissions.

postgresql.conf Essentials#

listen_addresses – Default is localhost. For remote connections, set to '*'.

max_connections – Default 100. With PgBouncer, keep this low. Each connection uses 5-10 MB of RAM.

shared_buffers – PostgreSQL’s page cache. Start at 25% of total RAM:

shared_buffers = 4GB       # on a 16 GB server

work_mem – Memory per sort/hash operation. Multiplied by concurrent operations, so start conservative:

work_mem = 16MB

effective_cache_size – Hint for the query planner. Set to 50-75% of total RAM:

effective_cache_size = 12GB

wal_level – For replication or PITR, use replica. For logical replication, use logical.

After changing settings, reload or restart:

sudo systemctl reload postgresql-16    # most settings
sudo systemctl restart postgresql-16   # shared_buffers, wal_level require restart

Check if a setting requires restart:

SELECT name, setting, context FROM pg_settings WHERE name = 'shared_buffers';
-- context = 'postmaster' means restart required
-- context = 'sighup' means reload is sufficient

pg_hba.conf Authentication Rules#

Controls who can connect, from where, and how. Rules are evaluated top to bottom; first match wins.

Format: 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   10.0.0.0/8        scram-sha-256
host    all   all   0.0.0.0/0         reject

Methods: peer matches OS username (local only), scram-sha-256 is password-based (preferred over md5), trust requires no password (never use in production), reject denies the connection.

Common mistake: adding host all all 0.0.0.0/0 trust for debugging and forgetting to remove it. After editing, reload: sudo systemctl reload postgresql-16.

Creating Databases and Roles#

Roles are PostgreSQL’s unified concept for users and groups. A role with LOGIN is a user.

CREATE ROLE appuser WITH LOGIN PASSWORD 'strong-password-here';
CREATE DATABASE myapp OWNER appuser;

-- PostgreSQL 15+: also fix schema ownership
\c myapp
ALTER SCHEMA public OWNER TO appuser;

Grant specific privileges instead of superuser:

CREATE ROLE readonly WITH LOGIN PASSWORD 'readonly-pass';
GRANT CONNECT ON DATABASE myapp TO readonly;
\c myapp
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

The ALTER DEFAULT PRIVILEGES line is critical – without it, the read-only role cannot see tables created after the grant.

Verifying Your Setup#

psql -U appuser -d myapp -c "SELECT version();"
psql -h 10.0.0.5 -U appuser -d myapp -c "SELECT 1;"
psql -U postgres -c "SHOW shared_buffers; SHOW max_connections; SHOW wal_level;"

If a remote connection fails, check three things in order: is PostgreSQL listening on the right interface (listen_addresses), does pg_hba.conf allow the connection, and is a firewall blocking port 5432.