123 lines
4.1 KiB
YAML
123 lines
4.1 KiB
YAML
apiVersion: v1
|
|
kind: Service
|
|
metadata: { name: postgres, namespace: db }
|
|
spec:
|
|
ports: [{ port: 5432, targetPort: 5432 }]
|
|
selector: { app: postgres }
|
|
---
|
|
apiVersion: v1
|
|
kind: ConfigMap
|
|
metadata: { name: pg-init-sql, namespace: db }
|
|
data:
|
|
00_extensions.sql: |
|
|
-- enable common extensions in the default DB and template1 so future DBs inherit them
|
|
\connect gitea
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
CREATE EXTENSION IF NOT EXISTS vector;
|
|
CREATE COLLATION IF NOT EXISTS arabic (provider = icu, locale = 'ar', deterministic = false);
|
|
CREATE EXTENSION IF NOT EXISTS tablefunc;
|
|
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
|
|
|
|
CREATE EXTENSION IF NOT EXISTS postgis_topology;
|
|
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
CREATE EXTENSION IF NOT EXISTS hstore;
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS citext;
|
|
CREATE EXTENSION IF NOT EXISTS unaccent;
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
-- PL/Python (optional; requires image with plpython3u, postgis image has it)
|
|
DO $$ BEGIN
|
|
CREATE EXTENSION IF NOT EXISTS plpython3u;
|
|
EXCEPTION WHEN undefined_file THEN
|
|
RAISE NOTICE 'plpython3u not available in this image';
|
|
END $$;
|
|
|
|
-- Also on template1 for new DBs:
|
|
\connect template1
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
CREATE EXTENSION IF NOT EXISTS hstore;
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
CREATE EXTENSION IF NOT EXISTS citext;
|
|
CREATE EXTENSION IF NOT EXISTS unaccent;
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- Arabic-friendly ICU collation (PostgreSQL >= 13)
|
|
-- Non-deterministic collation helps proper case/diacritics comparisons
|
|
DO $$
|
|
BEGIN
|
|
PERFORM 1 FROM pg_collation WHERE collname='arabic';
|
|
IF NOT FOUND THEN
|
|
CREATE COLLATION arabic (provider = icu, locale = 'ar', deterministic = false);
|
|
END IF;
|
|
END$$;
|
|
|
|
-- Example: ensure gitea DB uses UTF8; Arabic text search often needs unaccent + custom dictionaries.
|
|
-- You can create additional DBs with: CREATE DATABASE mydb TEMPLATE template1 ENCODING 'UTF8';
|
|
|
|
01_tune.sql: |
|
|
-- small safe defaults; adjust later
|
|
ALTER SYSTEM SET shared_buffers = '1GB';
|
|
ALTER SYSTEM SET work_mem = '32MB';
|
|
ALTER SYSTEM SET maintenance_work_mem = '512MB';
|
|
ALTER SYSTEM SET max_connections = 200;
|
|
SELECT pg_reload_conf();
|
|
---
|
|
apiVersion: apps/v1
|
|
kind: StatefulSet
|
|
metadata: { name: postgres, namespace: db }
|
|
spec:
|
|
serviceName: postgres
|
|
replicas: 1
|
|
selector: { matchLabels: { app: postgres } }
|
|
template:
|
|
metadata: { labels: { app: postgres } }
|
|
spec:
|
|
nodeSelector:
|
|
node: hetzner-2
|
|
securityContext:
|
|
fsGroup: 999 # Debian postgres user/group in postgis image
|
|
fsGroupChangePolicy: OnRootMismatch
|
|
initContainers:
|
|
- name: fix-perms
|
|
image: busybox:1.36
|
|
command: ["sh","-c","chown -R 999:999 /var/lib/postgresql/data || true"]
|
|
securityContext: { runAsUser: 0 }
|
|
volumeMounts: [{ name: data, mountPath: /var/lib/postgresql/data }]
|
|
containers:
|
|
- name: postgres
|
|
image: postgres:16-3.4
|
|
env:
|
|
- name: POSTGRES_PASSWORD
|
|
valueFrom: { secretKeyRef: { name: postgres-auth, key: POSTGRES_PASSWORD } }
|
|
- { name: POSTGRES_USER, value: gitea }
|
|
- { name: POSTGRES_DB, value: gitea }
|
|
- name: POSTGRES_INITDB_ARGS
|
|
value: "--encoding=UTF8 --locale=C.UTF-8"
|
|
ports: [{ containerPort: 5432 }]
|
|
volumeMounts:
|
|
- { name: data, mountPath: /var/lib/postgresql/data }
|
|
- { name: init, mountPath: /docker-entrypoint-initdb.d }
|
|
volumeClaimTemplates:
|
|
- metadata: { name: data }
|
|
spec:
|
|
accessModes: ["ReadWriteOnce"]
|
|
storageClassName: local-ssd-hetzner
|
|
resources: { requests: { storage: 80Gi } }
|
|
---
|
|
# Mount the init scripts
|
|
apiVersion: apps/v1
|
|
kind: StatefulSet
|
|
metadata:
|
|
name: postgres
|
|
namespace: db
|
|
spec:
|
|
template:
|
|
spec:
|
|
volumes:
|
|
- name: init
|
|
configMap:
|
|
name: pg-init-sql
|
|
defaultMode: 0444
|