Post

PostgreSQL - Database Engine

Posted Updated
Preview Image
By Scott
3 min read
PostgreSQL - Database Engine

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

This stack also includes pgAdmin which is the most popular and feature rich Open Source administration and development platform for PostgreSQL

I am using tensorchord/pgvecto-rs for Immich

Kubernetes Manifest (PostgreSQL)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: postgresql
    app.kubernetes.io/instance: postgresql
    app.kubernetes.io/name: postgresql
  name: postgresql
  namespace: postgresql
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: postgresql
  template:
    metadata:
      labels:
        app: postgresql
        app.kubernetes.io/name: postgresql
    spec:
      nodeSelector:
        worker: "true"
      containers:
        - image: tensorchord/pgvecto-rs:pg16-v0.2.0
          name: postgresql
          ports:
            - containerPort: 5432
              hostPort: 5432
              protocol: TCP
          env:
            - name: PGADMIN_CONFIG_SERVER_MODE
              value: "false"
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
            - name: POSTGRES_PASSWORD
              value: [REDACTED]
            - name: POSTGRES_USER
              value: admin
            - name: TZ
              value: Europe/London
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: data
      hostname: postgresql
      restartPolicy: Always
      volumes:
        - name: data
          persistentVolumeClaim:
            claimName: postgresql
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: postgresql
  name: postgresql
  namespace: postgresql 
spec:
  type: LoadBalancer
  loadBalancerIP: 10.0.10.201
  ports:
    - name: "postgres"
      port: 5432
      targetPort: 5432
  selector:
    app: postgresql

Kubernetes Manifest (PGAdmin)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: pgadmin
    app.kubernetes.io/instance: pgadmin
    app.kubernetes.io/name: pgadmin
  name: pgadmin
  namespace: postgresql
spec:
  replicas: 1
  strategy:
    type: Recreate
  selector:
    matchLabels:
      app: pgadmin
  template:
    metadata:
      labels:
        app: pgadmin
        app.kubernetes.io/name: pgadmin
    spec:
      nodeSelector:
        worker: "true"
      containers:
        - image: dpage/pgadmin4
          name: pgadmin
          securityContext:
            runAsUser: 0
            runAsGroup: 0
          env:
            - name: PGADMIN_DEFAULT_EMAIL
              value: [REDACTED]
            - name: PGADMIN_DEFAULT_PASSWORD
              value: [REDACTED]
          volumeMounts:
            - mountPath: /var/lib/pgadmin
              name: pgadmin
      volumes:
        - name: pgadmin
          persistentVolumeClaim:
            claimName: pgadmin
---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: pgadmin
  name: pgadmin
  namespace: postgresql 
spec:
  ports:
    - name: "pgadmin"
      port: 80
      targetPort: 80
  selector:
    app: pgadmin
---
apiVersion: traefik.containo.us/v1alpha1
kind: IngressRoute
metadata:
  name: pgadmin
  namespace: postgresql
  annotations: 
    kubernetes.io/ingress.class: traefik-external
spec:
  entryPoints:
    - websecure
  routes:
    - match: Host(`pgadmin.f9.casa`)
      kind: Rule
      services:
        - name: pgadmin
          port: 80
      middlewares:
        - name: default-headers
          namespace: default
        - name: authentik
          namespace: authentik
  tls:
    secretName: f9-casa-tls

Docker Compose

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

version: '3.2'
services:
  postgresql:
    image: tensorchord/pgvecto-rs:pg16-v0.2.0
    hostname: postgresql
    networks:
      - traefik-public
    environment:
      - TZ=Europe/London
      - POSTGRES_PASSWORD=[REDACTED]
      - POSTGRES_USER=admin
      - POSTGRES_DB=postgres
      - PGDATA=/var/lib/postgresql/data
      - PGADMIN_CONFIG_SERVER_MODE=false
    ports:
      - 5432:5432/tcp
    volumes:
      - type: bind
        source: /srv/cephfs/docker/appdata/postgresql16
        target: /var/lib/postgresql/data
    deploy:
      mode: replicated
      placement:
        constraints: [node.role == manager]
        
  pgadmin:
    image: dpage/pgadmin4
    hostname: pgadmin
    networks:
      - traefik-public
    environment:
      PGADMIN_DEFAULT_EMAIL: [REDACTED]
      PGADMIN_DEFAULT_PASSWORD: [REDACTED]
    volumes:
      - /srv/cephfs/docker/appdata/pgadmin:/var/lib/pgadmin
    deploy:
      labels:
        - "traefik.enable=true"
        - "traefik.http.routers.pgadmin.rule=Host(`pgadmin.f9.casa`)"
        - "traefik.http.services.pgadmin.loadbalancer.server.port=80"
        - "traefik.http.routers.pgadmin.entrypoints=https"
        - "traefik.http.routers.pgadmin.tls=true"
        - "traefik.http.routers.pgadmin.tls.certresolver=letsencrypt"
        - "traefik.http.routers.pgadmin.middlewares=authentik@docker"
      mode: replicated
      placement:
        constraints: [node.role == manager]
        
networks:
  traefik-public:
    external: true
services, database
homelab database postgres postgresql
This post is licensed under CC BY 4.0 by the author.