PostgreSQL Setup

https://www.postgresql.org/download/linux/
https://www.postgresql.org/docs/current/app-initdb.html
https://www.postgresql.org/docs/current/server-start.html
https://www.postgresql.org/docs/current/runtime-config-connection.html
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
https://github.com/bitnami/charts/tree/main/bitnami/postgresql
https://github.com/zalando/postgres-operator
https://github.com/cloudnative-pg/cloudnative-pg

# Debian / Ubuntu example
sudo apt-get update
sudo apt-get install -y postgresql postgresql-contrib

production note:
    prefer PostgreSQL official repository when you need a specific major version
    pin major version for production
    test major upgrade before changing packages

common directories:
    data:
        /var/lib/postgresql/<major>/main

    config:
        /etc/postgresql/<major>/main/postgresql.conf
        /etc/postgresql/<major>/main/pg_hba.conf

    log:
        depends on distro / journald config

sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql

sudo -u postgres psql

CREATE ROLE dba_order LOGIN PASSWORD 'change-me';
CREATE ROLE order_app LOGIN PASSWORD 'change-me';

CREATE DATABASE orders
  OWNER dba_order
  ENCODING 'UTF8'
  TABLESPACE pg_default
  CONNECTION LIMIT 200;

journalctl -u postgresql -n 200 --no-pager

docker run --rm --name pg-dev -e POSTGRES_DB=orders -e POSTGRES_USER=dba_order -e POSTGRES_PASSWORD=dba_password -p 5432:5432 -v pg-dev-data:/var/lib/postgresql/data postgres:16

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: orders
      POSTGRES_USER: dba_order
      POSTGRES_PASSWORD: dba_password
    ports:
      - "5432:5432"
    volumes:
      - pg-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U dba_order -d orders"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  pg-data:

docker notes:
    good for local dev
    do not use simple single-container setup as production HA
    do not bake password into image
    mount persistent volume

Bitnami PostgreSQL chart:
    useful for dev / small internal setup
    production usage needs careful review:
        backup
        replication
        security context
        persistence
        upgrade behavior
        monitoring

helm repo add bitnami https://charts.bitnami.com/bitnami
helm repo update

helm install orders-postgres bitnami/postgresql --set auth.database=orders --set auth.username=order_app --set auth.password=change-me --set primary.persistence.size=100Gi

production on Kubernetes:
    prefer PostgreSQL operator when DB must run in cluster

common options:
    CloudNativePG
    Zalando Postgres Operator

operator should handle:
    cluster bootstrap
    replication
    backup
    failover
    rolling restart
    major upgrade workflow

tradeoff:
    managed cloud PostgreSQL is usually simpler operationally
    in-cluster PostgreSQL requires strong storage / backup / SRE maturity

HA baseline:
    primary + at least one standby
    streaming replication
    WAL archive if PITR required
    automatic failover tool / managed service failover
    client connection routing after failover

backup is not HA:
    backup restores data after loss
    HA keeps service available during node failure
    you need both

failure scenarios:
    primary down
    standby lag too high
    disk full
    split brain in self-managed failover
    application keeps connecting to old primary

psql "postgresql://dba_order@localhost:5432/orders"
pg_isready -h localhost -p 5432

SELECT version();
SELECT current_database();
SELECT current_user;

SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE datname = current_database();

sudo systemctl reload postgresql

SELECT pg_reload_conf();

pg_dump --format=custom --file=orders.dump "postgresql://dba_order@localhost:5432/orders"

createdb orders_restore

pg_restore --dbname=orders_restore orders.dump

operations checklist:
    backup can restore
    monitor disk usage
    monitor replication lag
    review long transactions
    test config reload in staging
    document major upgrade path