K8s/PgBouncer: Difference between revisions

From Chorke Wiki
Jump to navigation Jump to search
← Older edit
VisualWikitext
 
(34 intermediate revisions by the same user not shown)
Line 16: Line 16:
export KUBECONFIG=${HOME}/.kube/shahed-ab-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/shahed-ab-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/shahed-ac-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/shahed-ac-kubeconfig.yaml
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
kubectl config get-contexts
kubectl config view
</syntaxhighlight>
</syntaxhighlight>
|}
|}
Line 27: Line 34:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
cat <<'EXE'| sudo bash
cat <<'EXE'| sudo bash
          mkdir -p /var/minikube/pvc/pgbouncer/data-pgbouncer-0/
      mkdir -p /var/minikube/pvc/pgbouncer/data-pgbouncer-0/
chown -R 1001:1001 /var/minikube/pvc/pgbouncer/
chown -R 70:70 /var/minikube/pvc/pgbouncer/
EXE
EXE
</syntaxhighlight>
</syntaxhighlight>
Line 60: Line 67:
* [[K8s/Storage#Storage » Persistent Volume|Find More 👉 Storage » Persistent Volume]]
* [[K8s/Storage#Storage » Persistent Volume|Find More 👉 Storage » Persistent Volume]]
----
----
|}
==K8s » Deploy==
{|class='wikitable mw-collapsible mw-collapsed'
!scope='col' colspan='2' style='width:1000px'|
K8s » Deploy
|-
|valign='top'|
<syntaxhighlight lang="bash">
kubectl config get-contexts
kubectl config view
</syntaxhighlight>
|valign='top'|
<syntaxhighlight lang="bash">
kubectl create ns  pgbouncer
kubectl get ns|grep pgbouncer
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="ini">
cat <<'INI'| kubectl -n pgbouncer create configmap pgbouncer --from-file=pgbouncer.ini=/dev/stdin
[databases]
bouncer        = host=192.168.49.1 user=bouncer  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
gitlab          = host=192.168.49.1 user=gitlab    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
harbor          = host=192.168.49.1 user=harbor    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
openldap        = host=192.168.49.1 user=openldap  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
[users]
gitlab          = pool_mode=transaction max_user_connections=20
[pgbouncer]
listen_addr    = *
listen_port    = 5432
unix_socket_dir =
auth_type      = plain
;auth_user      = bouncer
;auth_dbname    = bouncer
auth_file      = /etc/pgbouncer/userlist.txt
;auth_query    = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1)
admin_users    = bouncer
stats_users    = gitlab, harbor, openldap
pool_mode                = session
ignore_startup_parameters = extra_float_digits
server_check_query        = SELECT 1
max_client_conn          = 10000
default_pool_size        = 100
;min_pool_size            = 5
;reserve_pool_size        = 5
;reserve_pool_timeout    = 5
max_db_connections        = 100
max_user_connections      = 100
;server_round_robin      = 0
logfile                  = /dev/stdout
;log_connections          = 1
;log_disconnections      = 1
;log_pooler_errors        = 1
;verbose                  = 1
INI
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
cat << USR | kubectl -n pgbouncer create secret generic pgbouncer --from-file=userlist.txt=/dev/stdin
"bouncer"  "sadaqah!"
"gitlab"  "sadaqah!"
"harbor"  "sadaqah!"
"openldap" "sadaqah!"
USR
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang="yaml" highlight="21,22">
cat <<'YML'| kubectl apply -n pgbouncer -f -
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    app.kubernetes.io/version: 1.0.0
    app.kubernetes.io/name: pgbouncer
    app.kubernetes.io/instance: pgbouncer
    app.kubernetes.io/managed-by: kubectl
spec:
  selector:
    app: pgbouncer
  ports:
    - targetPort: 5432
      name: pgbouncer
      protocol: TCP
      port: 5432
  type: ClusterIP
YML
</syntaxhighlight>
|valign='top'|
<syntaxhighlight lang="yaml" highlight="21,22" line>
cat << YML | kubectl -n pgbouncer apply -f -
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer-lb
  namespace: pgbouncer
  labels:
    app.kubernetes.io/version: 1.0.0
    app.kubernetes.io/name: pgbouncer
    app.kubernetes.io/instance: pgbouncer
    app.kubernetes.io/managed-by: kubectl
spec:
  selector:
    app: pgbouncer
  ports:
    - targetPort: 5432
      name: pgbouncer
      protocol: TCP
      port: 5432
  type: LoadBalancer
  loadBalancerIP: 192.168.49.103
YML
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="yaml">
cat <<'YML'| kubectl apply -n pgbouncer -f -
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    app: pgbouncer
    app.kubernetes.io/version: 1.0.0
    app.kubernetes.io/name: pgbouncer
    app.kubernetes.io/instance: pgbouncer
    app.kubernetes.io/managed-by: kubectl
  annotations:
    kubernetes.io/change-cause: "CKI-1| Initial Deployment"
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      securityContext:
        runAsUser: 70
        fsGroup: 70
      containers:
        - name: pgbouncer
          image: edoburu/pgbouncer:latest
          ports:
          - containerPort: 5432
            name: pgbouncer
            protocol: TCP
          resources:
            requests:
              cpu: 50m
              memory: 64Mi
            limits:
              memory: 128Mi
              cpu: 100m
          volumeMounts:
            - mountPath: /etc/pgbouncer/pgbouncer.ini
              subPath: pgbouncer.ini
              name: pgbouncer-cfg
              readOnly: true
            - mountPath: /etc/pgbouncer/userlist.txt
              subPath: userlist.txt
              name: pgbouncer-sec
              readOnly: true
      volumes:
        - name: pgbouncer-cfg
          configMap:
            name: pgbouncer
            items:
            - key: pgbouncer.ini
              path: pgbouncer.ini
        - name: pgbouncer-sec
          secret:
            secretName: pgbouncer
            items:
            - key: userlist.txt
              path: userlist.txt
YML
</syntaxhighlight>
|}
==K8s » Verify==
{|class='wikitable mw-collapsible'
!scope='col' colspan='2' style='width:1000px'|
K8s » Verify
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo
# Password: sadaqah!
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer logs -f svc/pgbouncer -c pgbouncer
kubectl -n pgbouncer logs -f svc/pgbouncer
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang="bash">
psql -U bouncer  -d bouncer  -p 5432 -h 192.168.49.103
psql -U gitlab  -d gitlab    -p 5432 -h 192.168.49.103
psql -U harbor  -d harbor    -p 5432 -h 192.168.49.103
psql -U openldap -d openldap  -p 5432 -h 192.168.49.103
</syntaxhighlight>
|valign='top'|
<syntaxhighlight lang="bash">
psql -U bouncer  -d pgbouncer -p 5432 -h 192.168.49.103
psql -U gitlab  -d pgbouncer -p 5432 -h 192.168.49.103
psql -U harbor  -d pgbouncer -p 5432 -h 192.168.49.103
psql -U openldap -d pgbouncer -p 5432 -h 192.168.49.103
</syntaxhighlight>
|}
==K8s » Scaling==
{|class='wikitable mw-collapsible'
!scope='col' style='width:1000px'|
K8s » Scaling
|-
|valign='top'|
<syntaxhighlight lang="yaml">
cat <<YML | \
kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin
---
spec:
  replicas: 0
YML
</syntaxhighlight>
----
<syntaxhighlight lang="yaml">
cat <<YML | \
kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin
---
spec:
  replicas: 1
YML
</syntaxhighlight>
|}
==K8s » Rolling==
{|class='wikitable mw-collapsible'
!scope='col' style='width:1000px'|
K8s » Rollout
|-
|valign='top'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer rollout history deploy/pgbouncer
kubectl -n pgbouncer rollout pause  deploy/pgbouncer
</syntaxhighlight>
----
<syntaxhighlight lang="yaml">
cat <<YML | kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin
---
metadata:
  annotations:
    kubernetes.io/change-cause: "CKI-2| Container Updated"
spec:
  template:
    spec:
      containers:
        - name: pgbouncer
          image: edoburu/pgbouncer:v1.24.1-p1
YML
</syntaxhighlight>
----
<syntaxhighlight lang="bash">
kubectl -n pgbouncer annotate        deploy/pgbouncer --overwrite \
kubernetes.io/change-cause="CKI-2| Image Updated"
kubectl -n pgbouncer rollout resume  deploy/pgbouncer
kubectl -n pgbouncer rollout history deploy/pgbouncer
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer rollout undo    deploy/pgbouncer --to-revision=1
kubectl -n pgbouncer rollout history deploy/pgbouncer
</syntaxhighlight>
----
<syntaxhighlight lang="bash">
kubectl -n pgbouncer annotate        deploy/pgbouncer --overwrite \
kubernetes.io/change-cause="CKI-3| Revert Back to CKI-1"
kubectl -n pgbouncer rollout history deploy/pgbouncer
</syntaxhighlight>
|}
==K8s » Delete==
{|class='wikitable mw-collapsible mw-collapsed'
!scope='col' colspan='2' style='width:1000px'|
K8s » Delete
|-
|valign='top'|
<syntaxhighlight lang="bash">
kubectl delete svc    --all -n pgbouncer
kubectl delete deploy --all -n pgbouncer
kubectl delete pvc    --all -n pgbouncer
</syntaxhighlight>
|valign='top'|
<syntaxhighlight lang="bash">
kubectl delete pv    pgbouncer-data-pgbouncer-0
kubectl delete all    --all -n pgbouncer
kubectl delete ns    pgbouncer
</syntaxhighlight>
|}
|}


Line 102: Line 440:
psql -U harbor  -d pgbouncer -p 5433 -h 127.0.0.1
psql -U harbor  -d pgbouncer -p 5433 -h 127.0.0.1
psql -U openldap -d pgbouncer -p 5433 -h 127.0.0.1
psql -U openldap -d pgbouncer -p 5433 -h 127.0.0.1
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
export DOCKER_HOST_IP=host.docker.internal
echo -n 'Password: ';read -s PGBOUNCER_PASSWORD;export PGBOUNCER_PASSWORD;echo
# Password: sadaqah!
</syntaxhighlight>
</syntaxhighlight>


Line 121: Line 451:
SQL
SQL
</syntaxhighlight>
</syntaxhighlight>
 
----
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
cat <<'DDL'| psql
cat <<'DDL'| psql
Line 138: Line 466:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
export DOCKER_HOST_IP=host.docker.internal
echo -n 'Password: ';read -s PGBOUNCER_PASSWORD;export PGBOUNCER_PASSWORD;echo
# Password: sadaqah!
</syntaxhighlight>
----
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
cat << DDL | psql
cat << DDL | psql
Line 152: Line 486:
|-
|-
|valign='top' colspan='2'|
|valign='top' colspan='2'|
<syntaxhighlight lang="sql">
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
\! printf '\n'
\c bouncer
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE pool_shadow (usename TEXT PRIMARY KEY, passwd TEXT);
DDL
</syntaxhighlight>
----
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
Line 177: Line 520:
DDL
DDL
</syntaxhighlight>
</syntaxhighlight>
 
----
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
Line 203: Line 544:
CREATE OR REPLACE FUNCTION  bouncer.fn_get_pg_shadow(IN _username TEXT)
CREATE OR REPLACE FUNCTION  bouncer.fn_get_pg_shadow(IN _username TEXT)
   RETURNS TABLE(usename TEXT, passwd TEXT)
   RETURNS TABLE(usename TEXT, passwd TEXT)
   LANGUAGE SQL SECURITY DEFINER
   LANGUAGE plpgsql
  SECURITY DEFINER
AS $BODY$
AS $BODY$
BEGIN
BEGIN
Line 212: Line 554:
$BODY$;
$BODY$;
-- REVOKE ALL    ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) FROM public;
-- REVOKE ALL    ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) FROM public;
-- GRANT  EXECUTE ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) TO  bouncer;
  GRANT  EXECUTE ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) TO  bouncer;
DDL
DDL
</syntaxhighlight>
</syntaxhighlight>
 
----
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
cat << DDL | psql
cat << DDL | psql
\! printf '\n'
\! printf '\n'
SELECT  * FROM bouncer.fn_get_pg_shadow('harbor');
DDL
</syntaxhighlight>
----
<syntaxhighlight lang="sql">
cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
\! printf '\n'
\c bouncer
SELECT  * FROM bouncer.fn_get_pg_shadow('harbor');
SELECT  * FROM bouncer.fn_get_pg_shadow('harbor');
DDL
DDL
Line 229: Line 577:
==Auth » Query » Docker==
==Auth » Query » Docker==
{|class='wikitable mw-collapsible mw-collapsed'
{|class='wikitable mw-collapsible mw-collapsed'
!scope='col' colspan='2' style='width:1300px'|
!scope='col' colspan='2' style='width:1000px'|
Auth » Query » Docker
Auth » Query » Docker
|-
|-
Line 268: Line 616:
cat <<'INI'| tee ./pgbouncer.ini >/dev/null
cat <<'INI'| tee ./pgbouncer.ini >/dev/null
[databases]
[databases]
gitlab                    = host=192.168.49.1 port=5432 user=gitlab    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
bouncer        = host=192.168.49.1 user=bouncer  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
harbor                    = host=192.168.49.1 port=5432 user=harbor    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
gitlab          = host=192.168.49.1 user=gitlab  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
openldap                  = host=192.168.49.1 port=5432 user=openldap  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
harbor          = host=192.168.49.1 user=harbor  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
bouncer                  = host=192.168.49.1 port=5432 user=bouncer  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
openldap        = host=192.168.49.1 user=openldap client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'


[users]
[users]
gitlab                   = pool_mode=transaction max_user_connections=20
gitlab         = pool_mode=transaction max_user_connections=20


[pgbouncer]
[pgbouncer]
listen_addr               = *
listen_addr     = *
listen_port               = 5432
listen_port     = 5432
unix_socket_dir           =  
unix_socket_dir =  


auth_type                 = plain
auth_type       = plain
auth_user                 = bouncer
auth_user       = bouncer
auth_dbname               = bouncer
auth_dbname     = bouncer
auth_file                 = /etc/pgbouncer/userlist.txt
auth_file       = /etc/pgbouncer/userlist.txt
auth_query               = SELECT usename, pgp_sym_decrypt(decode(passwd, 'hex'), usename)::text AS "passwd" FROM pool_shadow WHERE usename = $1
auth_query     = SELECT usename, \
;auth_query               = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1)
pgp_sym_decrypt(decode(passwd, 'hex'), usename)::text AS "passwd" FROM pool_shadow WHERE usename = $1
;auth_query     = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1)


;admin_users             = gitlab, harbor, openldap
;admin_users   = gitlab, harbor, openldap
stats_users               = gitlab, harbor, openldap
stats_users     = gitlab, harbor, openldap


pool_mode                = session
pool_mode                = session
Line 310: Line 659:
verbose                  = 1
verbose                  = 1
INI
INI
</syntaxhighlight>
|}
==Playground==
{|class='wikitable mw-collapsible'
!scope='col' colspan='2' style='width:1000px'|
Playground
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer get secret pgbouncer -o json|jq -r '.data."userlist.txt"'|base64 -d;echo
kubectl -n pgbouncer get cm    pgbouncer -o json|jq -r '.data."pgbouncer.ini"';echo
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer rollout history deploy/pgbouncer
kubectl -n pgbouncer rollout restart deploy/pgbouncer
kubectl -n pgbouncer rollout undo    deploy/pgbouncer
</syntaxhighlight>
|valign='top'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer rollout pause  deploy/pgbouncer
kubectl -n pgbouncer rollout resume  deploy/pgbouncer
kubectl -n pgbouncer rollout status  deploy/pgbouncer
</syntaxhighlight>
|-
|valign='top'|
<syntaxhighlight lang="bash">
kubectl delete svc    --all -n pgbouncer
kubectl delete deploy --all -n pgbouncer
kubectl delete pvc    --all -n pgbouncer
</syntaxhighlight>
|valign='top'|
<syntaxhighlight lang="bash">
kubectl delete pv    pgbouncer-data-pgbouncer-0
kubectl delete all    --all -n pgbouncer
kubectl delete ns    pgbouncer
</syntaxhighlight>
|-
|valign='top' colspan='2'|
<syntaxhighlight lang="bash">
kubectl -n pgbouncer exec -it svc/pgbouncer -c pgbouncer -- ash
kubectl -n pgbouncer exec -it svc/pgbouncer -- ash
kubectl -n pgbouncer exec -it svc/pgbouncer -- id
kubectl -n pgbouncer logs -f  svc/pgbouncer -c pgbouncer
kubectl -n pgbouncer logs -f  svc/pgbouncer
</syntaxhighlight>
</syntaxhighlight>
|}
|}
Line 319: Line 721:
|-
|-
|valign='top'|
|valign='top'|
* [https://stackoverflow.com/questions/67708284/ K8s » Ingress » Connect to external service]
* [[K8s/HAProxy/Ingress|K8s » HAProxy » Ingress]]
* [https://kubernetes.github.io/ingress-nginx/user-guide/exposing-tcp-udp-services/ K8s » Ingress » Exposing TCP/UDP services]
* [[K8s/Apache/Ingress|K8s » Apache » Ingress]]
* [https://kubernetes.github.io/ingress-nginx/user-guide/fcgi-services/ K8s » Ingress » Exposing FastCGI Servers]
* [[K8s/Nginx/Ingress|K8s » Nginx » Ingress]]
* [https://stackoverflow.com/questions/59844622/ K8s » Ingress » Different namespaces]
* [[Minikube Ingress DNS|K8s » Ingress » DNS]]
* [https://kubernetes.github.io/ingress-nginx/user-guide/nginx-configuration/custom-template/ K8s » Ingress » Custom Template]
* [https://stackoverflow.com/questions/57764237/ K8s » Ingress » External Service]
* [https://kubernetes.github.io/ingress-nginx/user-guide/ingress-path-matching/ K8s » Ingress » Path Matching]
* [https://kubernetes.github.io/ingress-nginx/user-guide/nginx-configuration/annotations/ K8s » Ingress » Annotations]
* [https://kubernetes.github.io/ingress-nginx/user-guide/nginx-configuration/configmap/ K8s » Ingress » ConfigMaps]
* [https://kubernetes.io/docs/concepts/services-networking/ingress/ K8s » Ingress]
 
|valign='top'|
* [https://medium.com/@heshani.samarasekara/expose-tcp-services-through-ingress-nginx-controller-ef135dbfae4d K8s » Ingress » Expose TCP services]
* [https://stackoverflow.com/questions/60309108/ K8s » Ingress » Disable HSTS]
* [https://docs.k0sproject.io/v1.27.1+k0s.0/examples/ambassador-ingress/ K8s » Ingress » Ambassador]
* [https://github.com/kubernetes/ingress-nginx/blob/main/docs/user-guide/nginx-configuration/annotations.md K8s » Ingress » Annotations]
* [https://stackoverflow.com/questions/51744536/ K8s » Ingress » CORS Rules]
* [https://docs.k0sproject.io/v1.27.1+k0s.0/examples/metallb-loadbalancer/ K8s » Ingress » MetalLB]
* [https://docs.k0sproject.io/v1.27.1+k0s.0/examples/traefik-ingress/ K8s » Ingress » Traefik]
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
* [[K8s/Swiss Knife|K8s » Swiss Knife]]
* [[K8s/Storage|K8s » Storage]]
* [[K8s/Ingress|K8s » Ingress]]
* [[K8s/Service|K8s » Service]]
* [[K8s/Service|K8s » Service]]
* [[K8s/Secret|K8s » Secret]]
* [[K8s/Run|K8s » Run]]
* [[K8s/Run|K8s » Run]]


|valign='top'|
|valign='top'|
* [https://medium.com/@luis.laredo.vel/configuring-pgadmin-to-access-the-backstage-databases-in-a-kubernetes-cluster-953ee087e41d K8s » Configuring PgAdmin4]
* [[Helm/PostgreSQL/PV|Helm » PostgreSQL » PV]]
* [https://stackoverflow.com/questions/51744536/ K8s » Ingress » CORS Rules]
* [[Helm/PostgreSQL|Helm » PostgreSQL]]
* [[K8s/CSI Hostpath Driver|K8s » CSI Hostpath Driver]]
 
* [[K8s/HAProxy/Ingress|K8s » HAProxy » Ingress]]
|valign='top'|
* [[K8s/Apache/Ingress|K8s » Apache » Ingress]]
* [[K8s/Nginx/Ingress|K8s » Nginx » Ingress]]
* [[K8s/Storage|K8s » Storage]]


|-
|-
Line 365: Line 752:


|valign='top'|
|valign='top'|
* [https://www.uptimia.com/questions/how-to-change-the-default-port-for-nginx Nginx » Change The Default Port]
* [[Minikube Ingress DNS|Minikube » Ingress » DNS]]
* [[Minikube Ingress DNS|Minikube » Ingress » DNS]]
* [[Minikube Systemd|Minikube » Systemd]]
* [[Minikube Systemd|Minikube » Systemd]]
* [[Minikube Registry|Minikube » Registry]]
* [[Minikube Registry|Minikube » Registry]]
* [[Minikube MetalLB|Minikube » MetalLB]]
* [[Minikube Tunnel|Minikube » Tunnel]]
* [[Minikube Tunnel|Minikube » Tunnel]]
* [[Kubectl]]
* [[Kubectl]]
Line 377: Line 764:


|valign='top'|
|valign='top'|
* [https://support.severalnines.com/hc/en-us/articles/212427063 Apache » Change The Default Port]
* [[PostgreSQL/PgBouncer|PostgreSQL » PgBouncer]]
* [[PostgreSQL/PgLoader|PostgreSQL » PgLoader]]
* [[PostgreSQL]]
|}
|}

Latest revision as of 05:04, 25 July 2025

K8s » Config

K8s » Config 

export KUBECONFIG=${HOME}/.kube/aws-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/dev-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/gcp-kubeconfig.yaml

export KUBECONFIG=${HOME}/.kube/shahed-aa-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/shahed-ab-kubeconfig.yaml
export KUBECONFIG=${HOME}/.kube/shahed-ac-kubeconfig.yaml

kubectl config get-contexts
kubectl config view

K8s » Storage

K8s » Storage 

cat <<'EXE'| sudo bash
      mkdir -p /var/minikube/pvc/pgbouncer/data-pgbouncer-0/
chown -R 70:70 /var/minikube/pvc/pgbouncer/
EXE

cat <<'YML'| kubectl apply -f -
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: pgbouncer-data-pgbouncer-0
spec:
  capacity:
    storage: 10Gi
  accessModes:
    - ReadWriteOnce
  persistentVolumeReclaimPolicy: Retain
  storageClassName: hostpath
  hostPath:
    path: /var/hostpath_pv/pgbouncer/data-pgbouncer-0
    type: DirectoryOrCreate
YML

K8s » Deploy

K8s » Deploy 

kubectl config get-contexts
kubectl config view

kubectl create ns   pgbouncer
kubectl get ns|grep pgbouncer

cat <<'INI'| kubectl -n pgbouncer create configmap pgbouncer --from-file=pgbouncer.ini=/dev/stdin
[databases]
bouncer         = host=192.168.49.1 user=bouncer   client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
gitlab          = host=192.168.49.1 user=gitlab    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
harbor          = host=192.168.49.1 user=harbor    client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
openldap        = host=192.168.49.1 user=openldap  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

[users]
gitlab          = pool_mode=transaction max_user_connections=20

[pgbouncer]
listen_addr     = *
listen_port     = 5432
unix_socket_dir = 

auth_type       = plain
;auth_user      = bouncer
;auth_dbname    = bouncer
auth_file       = /etc/pgbouncer/userlist.txt
;auth_query     = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1)

admin_users     = bouncer
stats_users     = gitlab, harbor, openldap

pool_mode                 = session
ignore_startup_parameters = extra_float_digits
server_check_query        = SELECT 1

max_client_conn           = 10000
default_pool_size         = 100
;min_pool_size            = 5
;reserve_pool_size        = 5
;reserve_pool_timeout     = 5
max_db_connections        = 100
max_user_connections      = 100
;server_round_robin       = 0

logfile                   = /dev/stdout
;log_connections          = 1
;log_disconnections       = 1
;log_pooler_errors        = 1
;verbose                  = 1
INI

cat << USR | kubectl -n pgbouncer create secret generic pgbouncer --from-file=userlist.txt=/dev/stdin
"bouncer"  "sadaqah!"
"gitlab"   "sadaqah!"
"harbor"   "sadaqah!"
"openldap" "sadaqah!"
USR

cat <<'YML'| kubectl apply -n pgbouncer -f -
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    app.kubernetes.io/version: 1.0.0
    app.kubernetes.io/name: pgbouncer
    app.kubernetes.io/instance: pgbouncer
    app.kubernetes.io/managed-by: kubectl
spec:
  selector:
    app: pgbouncer
  ports:
    - targetPort: 5432
      name: pgbouncer
      protocol: TCP
      port: 5432
  type: ClusterIP

YML

cat << YML | kubectl -n pgbouncer apply -f -
---
apiVersion: v1
kind: Service
metadata:
  name: pgbouncer-lb
  namespace: pgbouncer
  labels:
    app.kubernetes.io/version: 1.0.0
    app.kubernetes.io/name: pgbouncer
    app.kubernetes.io/instance: pgbouncer
    app.kubernetes.io/managed-by: kubectl
spec:
  selector:
    app: pgbouncer
  ports:
    - targetPort: 5432
      name: pgbouncer
      protocol: TCP
      port: 5432
  type: LoadBalancer
  loadBalancerIP: 192.168.49.103
YML

cat <<'YML'| kubectl apply -n pgbouncer -f -
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
  namespace: pgbouncer
  labels:
    app: pgbouncer
    app.kubernetes.io/version: 1.0.0
    app.kubernetes.io/name: pgbouncer
    app.kubernetes.io/instance: pgbouncer
    app.kubernetes.io/managed-by: kubectl
  annotations:
    kubernetes.io/change-cause: "CKI-1| Initial Deployment"
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      securityContext:
        runAsUser: 70
        fsGroup: 70
      containers:
        - name: pgbouncer
          image: edoburu/pgbouncer:latest
          ports:
          - containerPort: 5432
            name: pgbouncer
            protocol: TCP
          resources:
            requests:
              cpu: 50m
              memory: 64Mi
            limits:
              memory: 128Mi
              cpu: 100m
          volumeMounts:
            - mountPath: /etc/pgbouncer/pgbouncer.ini
              subPath: pgbouncer.ini
              name: pgbouncer-cfg
              readOnly: true
            - mountPath: /etc/pgbouncer/userlist.txt
              subPath: userlist.txt
              name: pgbouncer-sec
              readOnly: true
      volumes:
        - name: pgbouncer-cfg
          configMap:
            name: pgbouncer
            items:
            - key: pgbouncer.ini
              path: pgbouncer.ini
        - name: pgbouncer-sec
          secret:
            secretName: pgbouncer
            items:
            - key: userlist.txt
              path: userlist.txt
YML

K8s » Verify

K8s » Verify 

echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo
# Password: sadaqah!

kubectl -n pgbouncer logs -f svc/pgbouncer -c pgbouncer
kubectl -n pgbouncer logs -f svc/pgbouncer

psql -U bouncer  -d bouncer   -p 5432 -h 192.168.49.103
psql -U gitlab   -d gitlab    -p 5432 -h 192.168.49.103
psql -U harbor   -d harbor    -p 5432 -h 192.168.49.103
psql -U openldap -d openldap  -p 5432 -h 192.168.49.103

psql -U bouncer  -d pgbouncer -p 5432 -h 192.168.49.103
psql -U gitlab   -d pgbouncer -p 5432 -h 192.168.49.103
psql -U harbor   -d pgbouncer -p 5432 -h 192.168.49.103
psql -U openldap -d pgbouncer -p 5432 -h 192.168.49.103

K8s » Scaling

K8s » Scaling 

cat <<YML | \
kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin
---
spec:
  replicas: 0
YML

cat <<YML | \
kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin
---
spec:
  replicas: 1
YML

K8s » Rolling

K8s » Rollout 

kubectl -n pgbouncer rollout history deploy/pgbouncer
kubectl -n pgbouncer rollout pause   deploy/pgbouncer

cat <<YML | kubectl -n pgbouncer patch deploy/pgbouncer --patch-file=/dev/stdin
---
metadata:
  annotations:
    kubernetes.io/change-cause: "CKI-2| Container Updated"
spec:
  template:
    spec:
      containers:
        - name: pgbouncer
          image: edoburu/pgbouncer:v1.24.1-p1
YML

kubectl -n pgbouncer annotate        deploy/pgbouncer --overwrite \
 kubernetes.io/change-cause="CKI-2| Image Updated"

kubectl -n pgbouncer rollout resume  deploy/pgbouncer
kubectl -n pgbouncer rollout history deploy/pgbouncer

kubectl -n pgbouncer rollout undo    deploy/pgbouncer --to-revision=1
kubectl -n pgbouncer rollout history deploy/pgbouncer

kubectl -n pgbouncer annotate        deploy/pgbouncer --overwrite \
 kubernetes.io/change-cause="CKI-3| Revert Back to CKI-1"

kubectl -n pgbouncer rollout history deploy/pgbouncer

K8s » Delete

K8s » Delete 

kubectl delete svc    --all -n pgbouncer
kubectl delete deploy --all -n pgbouncer
kubectl delete pvc    --all -n pgbouncer

kubectl delete pv     pgbouncer-data-pgbouncer-0
kubectl delete all    --all -n pgbouncer
kubectl delete ns     pgbouncer

Auth » Query » Config

Auth » Query » Config 

# set +o history
echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo
# Password: sadaqah!

psql -U bouncer  -d bouncer   -p 5432 -h 192.168.49.1
psql -U gitlab   -d gitlab    -p 5432 -h 192.168.49.1
psql -U harbor   -d harbor    -p 5432 -h 192.168.49.1
psql -U openldap -d openldap  -p 5432 -h 192.168.49.1

psql -U bouncer  -d bouncer   -p 5433 -h 127.0.0.1
psql -U gitlab   -d gitlab    -p 5433 -h 127.0.0.1
psql -U harbor   -d harbor    -p 5433 -h 127.0.0.1
psql -U openldap -d openldap  -p 5433 -h 127.0.0.1

psql -U bouncer  -d pgbouncer -p 5433 -h 127.0.0.1
psql -U gitlab   -d pgbouncer -p 5433 -h 127.0.0.1

psql -U harbor   -d pgbouncer -p 5433 -h 127.0.0.1
psql -U openldap -d pgbouncer -p 5433 -h 127.0.0.1

cat <<'SQL'| psql
\! printf '\n'
SELECT usename AS "user"     FROM pg_catalog.pg_user WHERE usename LIKE '%bouncer%' ORDER BY 1 ASC; -- \du+
SELECT datname as "database" FROM pg_database        WHERE datname LIKE '%bouncer%' ORDER BY 1 ASC; -- \l+
SQL

cat <<'DDL'| psql
\! printf '\n'
REVOKE ALL PRIVILEGES ON DATABASE bouncer FROM bouncer;
DROP OWNED    BY        bouncer;
DROP DATABASE IF EXISTS bouncer;
DROP USER     IF EXISTS bouncer;
DROP FUNCTION IF EXISTS bouncer.fn_get_pg_shadow;
DROP SCHEMA   IF EXISTS bouncer;
DDL

export DOCKER_HOST_IP=host.docker.internal
echo -n 'Password: ';read -s PGBOUNCER_PASSWORD;export PGBOUNCER_PASSWORD;echo
# Password: sadaqah!

cat << DDL | psql
\! printf '\n'
SELECT 'CREATE DATABASE bouncer' 
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'bouncer')\gexec
CREATE USER bouncer WITH ENCRYPTED PASSWORD '${PGBOUNCER_PASSWORD}';

GRANT ALL PRIVILEGES ON DATABASE bouncer TO bouncer;
ALTER DATABASE bouncer OWNER TO bouncer;
DDL

cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
\! printf '\n'
\c bouncer
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE pool_shadow (usename TEXT PRIMARY KEY, passwd TEXT);
DDL

cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
\! printf '\n'
\c bouncer
WITH raw_shadow AS (
   SELECT
      cast(raw_grouped_collection->>0 AS varchar) "usename",
      cast(raw_grouped_collection->>1 AS varchar) "password"
   FROM jsonb_array_elements('[
      ["gitlab",    "sadaqah!"],
      ["harbor",    "sadaqah!"],
      ["openldap",  "sadaqah!"]
   ]'::jsonb) AS raw_grouped_collection
)
INSERT INTO pool_shadow (usename, passwd)
SELECT
   raw.usename,
   encode(pgp_sym_encrypt(raw.password, raw.usename, 'cipher-algo=aes256'), 'hex')
FROM raw_shadow raw
WHERE NOT EXISTS (
   SELECT usename FROM pool_shadow
   WHERE (usename) = (raw.usename)
);
DDL

cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
\! printf '\n'
\c bouncer
-- DELETE FROM pool_shadow;
-- SELECT usename, passwd FROM pool_shadow;
-- SELECT usename, passwd FROM pool_shadow WHERE usename = 'harbor';
   SELECT usename, pgp_sym_decrypt(decode(passwd, 'hex'), usename)::text FROM pool_shadow;
DDL

cat <<'DDL' | psql
\! printf '\n'
CREATE SCHEMA IF NOT EXISTS bouncer;
GRANT USAGE ON SCHEMA bouncer TO bouncer;
ALTER ROLE bouncer SET search_path TO bouncer;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA bouncer TO bouncer;

CREATE OR REPLACE FUNCTION  bouncer.fn_get_pg_shadow(IN _username TEXT)
   RETURNS TABLE(usename TEXT, passwd TEXT)
   LANGUAGE plpgsql
   SECURITY DEFINER
AS $BODY$
BEGIN
   RETURN QUERY
   SELECT sdw.usename::TEXT, sdw.passwd::TEXT
   FROM pg_catalog.pg_shadow AS sdw WHERE sdw.usename = _username;
END
$BODY$;
-- REVOKE ALL     ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) FROM public;
   GRANT  EXECUTE ON FUNCTION bouncer.fn_get_pg_shadow(TEXT) TO   bouncer;
DDL

cat << DDL | psql
\! printf '\n'
SELECT  * FROM bouncer.fn_get_pg_shadow('harbor');
DDL

cat << DDL | psql -U bouncer -d bouncer -p 5432 -h 192.168.49.1
\! printf '\n'
\c bouncer
SELECT  * FROM bouncer.fn_get_pg_shadow('harbor');
DDL
# set -o history

Auth » Query » Docker

Auth » Query » Docker 

docker run --rm -it \
  -v ./pgbouncer.ini:/etc/pgbouncer/pgbouncer.ini:ro \
  -v ./userlist.txt:/etc/pgbouncer/userlist.txt:ro \
  -p 127.0.0.1:5433:5432 \
  edoburu/pgbouncer:latest

echo -n 'Password: ';read -s PGPASSWORD; export PGPASSWORD; echo
# Password: sadaqah!

psql -U bouncer  -d bouncer   -p 5433 -h 127.0.0.1
psql -U bouncer  -d pgbouncer -p 5433 -h 127.0.0.1

cat << USR | tee ./userlist.txt >/dev/null
"bouncer" "sadaqah!"
USR

cat <<'INI'| tee ./pgbouncer.ini >/dev/null
[databases]
bouncer         = host=192.168.49.1 user=bouncer  client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
gitlab          = host=192.168.49.1 user=gitlab   client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
harbor          = host=192.168.49.1 user=harbor   client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
openldap        = host=192.168.49.1 user=openldap client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

[users]
gitlab          = pool_mode=transaction max_user_connections=20

[pgbouncer]
listen_addr     = *
listen_port     = 5432
unix_socket_dir = 

auth_type       = plain
auth_user       = bouncer
auth_dbname     = bouncer
auth_file       = /etc/pgbouncer/userlist.txt
auth_query      = SELECT usename, \
 pgp_sym_decrypt(decode(passwd, 'hex'), usename)::text AS "passwd" FROM pool_shadow WHERE usename = $1
;auth_query     = SELECT usename, passwd FROM bouncer.fn_get_pg_shadow($1)

;admin_users    = gitlab, harbor, openldap
stats_users     = gitlab, harbor, openldap

pool_mode                 = session
ignore_startup_parameters = extra_float_digits

;max_client_conn          = 100
;default_pool_size        = 20
;min_pool_size            = 5
;reserve_pool_size        = 5
;reserve_pool_timeout     = 5
;max_db_connections       = 10
;max_user_connections     = 20
;server_round_robin       = 0

;logfile                  = /var/log/pgbouncer/pgbouncer.log
logfile                   = /dev/stdout
log_connections           = 1
log_disconnections        = 1
log_pooler_errors         = 1
verbose                   = 1
INI

Playground

Playground 

kubectl -n pgbouncer get secret pgbouncer -o json|jq -r '.data."userlist.txt"'|base64 -d;echo
kubectl -n pgbouncer get cm     pgbouncer -o json|jq -r '.data."pgbouncer.ini"';echo

kubectl -n pgbouncer rollout history deploy/pgbouncer
kubectl -n pgbouncer rollout restart deploy/pgbouncer
kubectl -n pgbouncer rollout undo    deploy/pgbouncer

kubectl -n pgbouncer rollout pause   deploy/pgbouncer
kubectl -n pgbouncer rollout resume  deploy/pgbouncer
kubectl -n pgbouncer rollout status  deploy/pgbouncer

kubectl delete svc    --all -n pgbouncer
kubectl delete deploy --all -n pgbouncer
kubectl delete pvc    --all -n pgbouncer

kubectl delete pv     pgbouncer-data-pgbouncer-0
kubectl delete all    --all -n pgbouncer
kubectl delete ns     pgbouncer

kubectl -n pgbouncer exec -it svc/pgbouncer -c pgbouncer -- ash
kubectl -n pgbouncer exec -it svc/pgbouncer -- ash
kubectl -n pgbouncer exec -it svc/pgbouncer -- id

kubectl -n pgbouncer logs -f  svc/pgbouncer -c pgbouncer
kubectl -n pgbouncer logs -f  svc/pgbouncer

References

References

Retrieved from "https://wiki.chorke.org/index.php?title=K8s/PgBouncer&oldid=15515"