postgresql

prepare

1. k8s is ready
2. argocd is ready and logged in
3. ingress is ready
   • only required by pgadmin4 in the tests
4. cert-manager is ready
   • the clusterissuer named self-signed-ca-issuer is ready
   • only required by pgadmin4 in the tests

installation

1. prepare postgresql.yaml

   • apiVersion: argoproj.io/v1alpha1
     kind: Application
     metadata:
       name: postgresql
     spec:
       syncPolicy:
         syncOptions:
         - CreateNamespace=true
       project: default
       source:
         repoURL: https://charts.bitnami.com/bitnami
         chart: postgresql
         targetRevision: 14.2.2
         helm:
           releaseName: postgresql
           values: |
             architecture: standalone
             auth:
               database: geekcity
               username: ben.wangz
               existingSecret: postgresql-credentials
             primary:
               persistence:
                 enabled: false
             readReplicas:
               replicaCount: 1
               persistence:
                 enabled: false
             backup:
               enabled: false
             image:
               registry: docker.io
               pullPolicy: IfNotPresent
             volumePermissions:
               enabled: false
               image:
                 registry: docker.io
                 pullPolicy: IfNotPresent
             metrics:
               enabled: false
               image:
                 registry: docker.io
                 pullPolicy: IfNotPresent
       destination:
         server: https://kubernetes.default.svc
         namespace: database
     
     

2. prepare credentials secret

   • kubectl get namespaces database > /dev/null 2>&1 || kubectl create namespace database
     kubectl -n database create secret generic postgresql-credentials \
         --from-literal=postgres-password=$(tr -dc A-Za-z0-9 </dev/urandom | head -c 16) \
         --from-literal=password=$(tr -dc A-Za-z0-9 </dev/urandom | head -c 16) \
         --from-literal=replication-password=$(tr -dc A-Za-z0-9 </dev/urandom | head -c 16)
     

3. apply to k8s

   • kubectl -n argocd apply -f postgresql.yaml
     

4. sync by argocd

   • argocd app sync argocd/postgresql
     

5. expose interface
   1. prepare postgresql-expose.yaml

      • apiVersion: v1
        kind: Service
        metadata:
          labels:
            app.kubernetes.io/component: primary
            app.kubernetes.io/instance: postgresql
          name: postgresql-expose
        spec:
          ports:
          - name: tcp-postgresql
            port: 5432
            protocol: TCP
            targetPort: tcp-postgresql
            nodePort: 32543
          selector:
            app.kubernetes.io/component: primary
            app.kubernetes.io/instance: postgresql
            app.kubernetes.io/name: postgresql
          type: NodePort
        
        

   2. apply to k8s

      • kubectl -n database apply -f postgresql-expose.yaml
        

tests

1. with root user

   • POSTGRES_PASSWORD=$(kubectl -n database get secret postgresql-credentials -o jsonpath='{.data.postgres-password}' | base64 -d)
     podman run --rm \
         --env PGPASSWORD=${POSTGRES_PASSWORD} \
         --entrypoint psql \
         -it docker.io/library/postgres:15.2-alpine3.17 \
         --host host.containers.internal \
         --port 32543 \
         --username postgres \
         --dbname postgres \
         --command 'SELECT datname FROM pg_database;'
     

2. with normal user

   • POSTGRES_PASSWORD=$(kubectl -n database get secret postgresql-credentials -o jsonpath='{.data.password}' | base64 -d)
     podman run --rm \
         --env PGPASSWORD=${POSTGRES_PASSWORD} \
         --entrypoint psql \
         -it docker.io/library/postgres:15.2-alpine3.17 \
         --host host.containers.internal \
         --port 32543 \
         --username ben.wangz \
         --dbname geekcity \
         --command 'SELECT datname FROM pg_database;'
     

test with pgadmin4

1. prepare pgadmin4.yaml

   • apiVersion: argoproj.io/v1alpha1
     kind: Application
     metadata:
       name: pgadmin4
     spec:
       syncPolicy:
         syncOptions:
         - CreateNamespace=true
       project: default
       source:
         repoURL: https://helm.runix.net/
         chart: pgadmin4
         targetRevision: 1.23.3
         helm:
           releaseName: pgadmin4
           values: |
             replicaCount: 1
             persistentVolume:
               enabled: false
             env:
               email: pgadmin@mail.geekcity.tech
               variables:
                 - name: PGADMIN_CONFIG_WTF_CSRF_ENABLED
                   value: "False"
             existingSecret: pgadmin4-credentials
             image:
               registry: docker.io
               pullPolicy: IfNotPresent
             ingress:
               enabled: true
               ingressClassName: nginx
               annotations:
                 cert-manager.io/cluster-issuer: self-signed-ca-issuer
                 nginx.ingress.kubernetes.io/rewrite-target: /$1
               hosts:
                 - host: pgadmin4.dev.geekcity.tech
                   paths:
                     - path: /?(.*)
                       pathType: ImplementationSpecific
               tls:
                 - secretName: pgadmin4-tls
                   hosts:
                     - pgadmin4.dev.geekcity.tech
       destination:
         server: https://kubernetes.default.svc
         namespace: database
     
     

2. prepare credentials secret

   • kubectl -n database create secret generic pgadmin4-credentials \
         --from-literal=password=$(tr -dc A-Za-z0-9 </dev/urandom | head -c 16)
     

3. apply to k8s

   • kubectl -n argocd apply -f pgadmin4.yaml
     

4. sync by argocd

   • argocd app sync argocd/pgadmin4
     

5. open with browser: https://pgadmin4.dev.geekcity.tech:32443
   • pgadmin4.dev.geekcity.tech should be resolved to nginx-ingress
     • for example, add $K8S_MASTER_IP pgadmin4.dev.geekcity.tech to /etc/hosts
6. login
   • email: pgadmin@mail.geekcity.tech
   • password

     • kubectl -n database get secret pgadmin4-credentials -o jsonpath='{.data.password}' | base64 -d
       

   • connecting to postgresql database
     • host: postgresql.database
     • port: 5432
     • username: postgres
       • password

         • kubectl -n database get secret postgresql-credentials -o jsonpath='{.data.postgres-password}' | base64 -d
           

     • username: ben.wangz
       • password

         • kubectl -n database get secret postgresql-credentials -o jsonpath='{.data.password}' | base64 -d