---
title: "PostgreSQL Setup and Configuration"
description: "Installing PostgreSQL via package managers, Docker, and Helm, then configuring postgresql.conf, pg_hba.conf, roles, and databases for production use."
url: https://agent-zone.ai/knowledge/databases/postgres-setup-and-configuration/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["postgresql","installation","configuration","docker","helm","authentication"]
skills: ["postgres-installation","postgres-configuration","database-administration"]
tools: ["postgresql","psql","docker","helm","apt","brew"]
levels: ["intermediate"]
word_count: 585
formats:
  json: https://agent-zone.ai/knowledge/databases/postgres-setup-and-configuration/index.json
  html: https://agent-zone.ai/knowledge/databases/postgres-setup-and-configuration/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=PostgreSQL+Setup+and+Configuration
---


# 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:

```bash
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:

```bash
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

```bash
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

```bash
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:

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

Check if a setting requires restart:

```sql
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.

```sql
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:

```sql
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

```bash
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.