PostgreSQL

applications.postgresql = {
  namespace = "database";
  createNamespace = true;
  
  helm.releases.postgresql = {
    chart = charts.bitnami.postgresql;
    values = {
      auth = {
        username = "devuser";
        password = "devpass";
        database = "postgres";
      };
      primary = {
        initdb.scripts = {
          "create-databases.sql" = ''
            CREATE DATABASE auth_db;
            CREATE DATABASE lang_db;
            CREATE DATABASE greeter_db;
            CREATE DATABASE caller_db;
            CREATE DATABASE gateway_db;
            GRANT ALL PRIVILEGES ON DATABASE auth_db TO devuser;
            GRANT ALL PRIVILEGES ON DATABASE lang_db TO devuser;
            GRANT ALL PRIVILEGES ON DATABASE greeter_db TO devuser;
            GRANT ALL PRIVILEGES ON DATABASE caller_db TO devuser;
            GRANT ALL PRIVILEGES ON DATABASE gateway_db TO devuser;
          '';
        };
        persistence = {
          enabled = true;
          size = "2Gi";
        };
      };
      metrics = {
        enabled = true;
        serviceMonitor = {
          enabled = true;
          namespace = "database";
        };
      };
    };
  };
};

postgresql://devuser:[email protected]:5432/<database_name>

CREATE DATABASE auth_db;
CREATE DATABASE lang_db;
CREATE DATABASE greeter_db;
CREATE DATABASE caller_db;
CREATE DATABASE gateway_db;
GRANT ALL PRIVILEGES ON DATABASE auth_db TO devuser;
-- ...

primary:
  persistence:
    enabled: true
    size: 2Gi
    accessModes: [ReadWriteOnce]

metrics:
  enabled: true
  serviceMonitor:
    enabled: true
    namespace: database

apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: postgresql
  namespace: database
spec:
  selector:
    matchLabels:
      app.kubernetes.io/name: postgresql
  endpoints:
    - port: metrics

# Connection count
pg_stat_database_numbackends

# Query rate
rate(pg_stat_database_xact_commit[5m])

# Database size
pg_database_size_bytes

dsn := "postgresql://devuser:[email protected]:5432/auth_db"
conn, err := pgx.Connect(context.Background(), dsn)

const { Client } = require('pg');
const client = new Client({
  host: 'postgresql.database',
  port: 5432,
  user: 'devuser',
  password: 'devpass',
  database: 'lang_db'
});
await client.connect();

primary.replicas: 1
readReplicas.replicas: 0

initdb.scripts = {
  "01-create-extensions.sql" = ''
    CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    CREATE EXTENSION IF NOT EXISTS "pg_trgm";
  '';
  "02-create-databases.sql" = ...;
};

kubectl exec -n database postgresql-0 -- \
  pg_dump -U devuser auth_db > auth_db.sql

kubectl exec -i -n database postgresql-0 -- \
  psql -U devuser auth_db < auth_db.sql

kubectl logs -n database postgresql-0

kubectl exec -it -n database postgresql-0 -- \
  psql -U devuser -d postgres

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database;

SELECT count(*) FROM pg_stat_activity;

resources:
  requests:
    memory: 256Mi
    cpu: 250m
  limits:
    memory: 512Mi