Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

pgpool redirects traffic to a slave #28222

Closed
DCx14 opened this issue Jul 23, 2024 · 5 comments
Closed

pgpool redirects traffic to a slave #28222

DCx14 opened this issue Jul 23, 2024 · 5 comments
Assignees
@carrodher
Labels
postgresql-ha solved stale 15 days without activity tech-issues The user has a technical issue about an application triage Triage is needed

Comments

@DCx14
Copy link

DCx14 commented Jul 23, 2024 •
edited by carrodher
Loading

Name and Version

bitnami/postgresql-ha:14.2.12

What architecture are you using?

amd64

What steps will reproduce the bug?

helm install my-release oci://registry-1.docker.io/bitnamicharts/postgresql-ha -f value.yaml

once the pods are all green I wait another 2 minutes and then I kill the postgresql-0 pod

I check that the switch is done correctly:


I have no name!@postgresql-ha-1721745843-postgresql-2:/$ /opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show
postgresql-repmgr 16:50:21.10 INFO  ==> 
postgresql-repmgr 16:50:21.10 INFO  ==> Welcome to the Bitnami postgresql-repmgr container
postgresql-repmgr 16:50:21.10 INFO  ==> Subscribe to project updates by watching https://github.com/bitnami/containers
postgresql-repmgr 16:50:21.10 INFO  ==> Submit issues and feature requests at https://github.com/bitnami/containers/issues
postgresql-repmgr 16:50:21.10 INFO  ==> Upgrade to Tanzu Application Catalog for production environments to access custom-configured and pre-packaged software components. Gain enhanced features, including Software Bill of Materials (SBOM), CVE scan result reports, and VEX documents. To learn more, visit https://bitnami.com/enterprise
postgresql-repmgr 16:50:21.11 INFO  ==> 

 ID   | Name                                  | Role    | Status    | Upstream                              | Location | Priority | Timeline | Connection string                                                                                                                                                                                      
------+---------------------------------------+---------+-----------+---------------------------------------+----------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1000 | postgresql-ha-1721745843-postgresql-0 | standby |   running | postgresql-ha-1721745843-postgresql-1 | default  | 100      | 2        | user=repmgr password=7cl0eNHANK host=postgresql-ha-1721745843-postgresql-0.postgresql-ha-1721745843-postgresql-headless.cluster-postgresql3.svc.cluster.local dbname=repmgr port=5432 connect_timeout=3
 1001 | postgresql-ha-1721745843-postgresql-1 | primary | * running |                                       | default  | 100      | 2        | user=repmgr password=7cl0eNHANK host=postgresql-ha-1721745843-postgresql-1.postgresql-ha-1721745843-postgresql-headless.cluster-postgresql3.svc.cluster.local dbname=repmgr port=5432 connect_timeout=3
 1002 | postgresql-ha-1721745843-postgresql-2 | standby |   running | postgresql-ha-1721745843-postgresql-1 | default  | 100      | 2        | user=repmgr password=7cl0eNHANK host=postgresql-ha-1721745843-postgresql-2.postgresql-ha-1721745843-postgresql-headless.cluster-postgresql3.svc.cluster.local dbname=repmgr port=5432 connect_timeout=3
I have no name!@postgresql-ha-1721745843-postgresql-2:/$

Are you using any custom parameters or values?

#values.yml  

repmgrConnectTimeout: 3
repmgrReconnectAttempts: 2
repmgrReconnectInterval: 3
repmgrFenceOldPrimary: false
repmgrChildNodesCheckInterval: 1
repmgrChildNodesConnectedMinCount: 1
repmgrChildNodesDisconnectTimeout: 5

and

**periodSeconds: 120**  for  livenessProbe pgpool

What is the expected behavior?

if I but a pgadmin or I install keycloak it should be able to connect and have write rights

What do you see instead?

when I connect to the postgresql database via pgadmin it does not show me my replication servers.

the pgpool seems to redirect traffic to a slave server and not the master.

and the base and read-only

Additional information

cluster show:

$ /opt/bitnami/scripts/postgresql-repmgr/entrypoint.sh repmgr -f /opt/bitnami/repmgr/conf/repmgr.conf cluster show
postgresql-repmgr 16:50:21.10 INFO  ==> 
postgresql-repmgr 16:50:21.10 INFO  ==> Welcome to the Bitnami postgresql-repmgr container
postgresql-repmgr 16:50:21.10 INFO  ==> Subscribe to project updates by watching https://github.com/bitnami/containers
postgresql-repmgr 16:50:21.10 INFO  ==> Submit issues and feature requests at https://github.com/bitnami/containers/issues
postgresql-repmgr 16:50:21.10 INFO  ==> Upgrade to Tanzu Application Catalog for production environments to access custom-configured and pre-packaged software components. Gain enhanced features, including Software Bill of Materials (SBOM), CVE scan result reports, and VEX documents. To learn more, visit https://bitnami.com/enterprise
postgresql-repmgr 16:50:21.11 INFO  ==> 

 ID   | Name                                  | Role    | Status    | Upstream                              | Location | Priority | Timeline | Connection string                                                                                                                                                                                      
------+---------------------------------------+---------+-----------+---------------------------------------+----------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 1000 | postgresql-ha-1721745843-postgresql-0 | standby |   running | postgresql-ha-1721745843-postgresql-1 | default  | 100      | 2        | user=repmgr password=7cl0eNHANK host=postgresql-ha-1721745843-postgresql-0.postgresql-ha-1721745843-postgresql-headless.cluster-postgresql3.svc.cluster.local dbname=repmgr port=5432 connect_timeout=3
 1001 | postgresql-ha-1721745843-postgresql-1 | primary | * running |                                       | default  | 100      | 2        | user=repmgr password=7cl0eNHANK host=postgresql-ha-1721745843-postgresql-1.postgresql-ha-1721745843-postgresql-headless.cluster-postgresql3.svc.cluster.local dbname=repmgr port=5432 connect_timeout=3
 1002 | postgresql-ha-1721745843-postgresql-2 | standby |   running | postgresql-ha-1721745843-postgresql-1 | default  | 100      | 2        | user=repmgr password=7cl0eNHANK host=postgresql-ha-1721745843-postgresql-2.postgresql-ha-1721745843-postgresql-headless.cluster-postgresql3.svc.cluster.local dbname=repmgr port=5432 connect_timeout=3
I have no name!@postgresql-ha-1721745843-postgresql-2:/$

kubectl describe pgpool:

$ kubectl describe pod postgresql-ha-1721745843-pgpool-748855b95-d4cz7 -n cluster-postgresql3
Name:         postgresql-ha-1721745843-pgpool-748855b95-d4cz7
Namespace:    cluster-postgresql3
Priority:     0
Node:         pslpsepinfkub04/172.19.236.13
Start Time:   Tue, 23 Jul 2024 16:43:57 +0200
Labels:       app.kubernetes.io/component=pgpool
              app.kubernetes.io/instance=postgresql-ha-1721745843
              app.kubernetes.io/managed-by=Helm
              app.kubernetes.io/name=postgresql-ha
              app.kubernetes.io/version=4.5.2
              helm.sh/chart=postgresql-ha-14.2.12
              pod-template-hash=748855b95
Annotations:  cni.projectcalico.org/containerID: ec7a5323afcea5d8862ea96222542c6761c5db04e885ff0a5a1b0307dd694b85
              cni.projectcalico.org/podIP: 172.31.57.63/32
              cni.projectcalico.org/podIPs: 172.31.57.63/32
              container.seccomp.security.alpha.kubernetes.io/k8tz: runtime/default
              container.seccomp.security.alpha.kubernetes.io/pgpool: runtime/default
              k8tz.io/injected: true
              k8tz.io/timezone: Europe/Paris
Status:       Running
IP:           172.31.57.63
IPs:
  IP:           172.31.57.63
Controlled By:  ReplicaSet/postgresql-ha-1721745843-pgpool-748855b95
Init Containers:
  k8tz:
    Container ID:  containerd://1b2c24b8be7b1561d746bbce77dd777af973836072e54c85f7347a44ee6223dc
    Image:         quay.io/k8tz/k8tz:0.14.0
    Image ID:      quay.io/k8tz/k8tz@sha256:9442e473a5fbe96239951efc668f9f6932083f6af7681c4b5277a18fd264f8d1
    Port:          <none>
    Host Port:     <none>
    Args:
      bootstrap
    State:          Terminated
      Reason:       Completed
      Exit Code:    0
      Started:      Tue, 23 Jul 2024 16:43:58 +0200
      Finished:     Tue, 23 Jul 2024 16:43:58 +0200
    Ready:          True
    Restart Count:  0
    Environment:    <none>
    Mounts:
      /mnt/zoneinfo from k8tz (rw)
Containers:
  pgpool:
    Container ID:   containerd://81127ff7f4d204eef72b4dc0df6db2cd353e17d6dcb56ae3ee21b47aecdd999c
    Image:          docker.io/bitnami/pgpool:4.5.2-debian-12-r2
    Image ID:       docker.io/bitnami/pgpool@sha256:2077843b56c7283e40d3f132a034c86c4321adc1d51df9330e2df561b13013c6
    Port:           5432/TCP
    Host Port:      0/TCP
    State:          Running
      Started:      Tue, 23 Jul 2024 16:44:00 +0200
    Ready:          True
    Restart Count:  0
    Limits:
      cpu:                375m
      ephemeral-storage:  2Gi
      memory:             384Mi
    Requests:
      cpu:                250m
      ephemeral-storage:  50Mi
      memory:             256Mi
    Liveness:             exec [/opt/bitnami/scripts/pgpool/healthcheck.sh] delay=30s timeout=5s period=120s #success=1 #failure=5
    Readiness:            exec [bash -ec PGPASSWORD=${PGPOOL_POSTGRES_PASSWORD} psql -U "postgres" -d "postgres" -h /opt/bitnami/pgpool/tmp -tA -c "SELECT 1" >/dev/null] delay=5s timeout=5s period=5s #success=1 #failure=5
    Environment:
      BITNAMI_DEBUG:                         true
      PGPOOL_BACKEND_NODES:                  0:postgresql-ha-1721745843-postgresql-0.postgresql-ha-1721745843-postgresql-headless:5432,1:postgresql-ha-1721745843-postgresql-1.postgresql-ha-1721745843-postgresql-headless:5432,2:postgresql-ha-1721745843-postgresql-2.postgresql-ha-1721745843-postgresql-headless:5432,
      PGPOOL_SR_CHECK_USER:                  repmgr
      PGPOOL_SR_CHECK_PASSWORD:              <set to the key 'repmgr-password' in secret 'postgresql-ha-1721745843-postgresql'>  Optional: false
      PGPOOL_SR_CHECK_DATABASE:              postgres
      PGPOOL_ENABLE_LDAP:                    no
      PGPOOL_POSTGRES_USERNAME:              postgres
      PGPOOL_POSTGRES_PASSWORD:              <set to the key 'password' in secret 'postgresql-ha-1721745843-postgresql'>  Optional: false
      PGPOOL_ADMIN_USERNAME:                 admin
      PGPOOL_ADMIN_PASSWORD:                 <set to the key 'admin-password' in secret 'postgresql-ha-1721745843-pgpool'>  Optional: false
      PGPOOL_AUTHENTICATION_METHOD:          scram-sha-256
      PGPOOL_ENABLE_LOAD_BALANCING:          yes
      PGPOOL_DISABLE_LOAD_BALANCE_ON_WRITE:  transaction
      PGPOOL_ENABLE_LOG_CONNECTIONS:         no
      PGPOOL_ENABLE_LOG_HOSTNAME:            yes
      PGPOOL_ENABLE_LOG_PER_NODE_STATEMENT:  no
      PGPOOL_RESERVED_CONNECTIONS:           1
      PGPOOL_CHILD_LIFE_TIME:                
      PGPOOL_ENABLE_TLS:                     no
      PGPOOL_HEALTH_CHECK_PSQL_TIMEOUT:      6
      TZ:                                    Europe/Paris
    Mounts:
      /etc/localtime from k8tz (ro,path="Europe/Paris")
      /opt/bitnami/pgpool/conf from empty-dir (rw,path="app-conf-dir")
      /opt/bitnami/pgpool/etc from empty-dir (rw,path="app-etc-dir")
      /opt/bitnami/pgpool/logs from empty-dir (rw,path="app-logs-dir")
      /opt/bitnami/pgpool/tmp from empty-dir (rw,path="app-tmp-dir")
      /tmp from empty-dir (rw,path="tmp-dir")
      /usr/share/zoneinfo from k8tz (ro)
Conditions:
  Type              Status
  Initialized       True 
  Ready             True 
  ContainersReady   True 
  PodScheduled      True 
Volumes:
  empty-dir:
    Type:       EmptyDir (a temporary directory that shares a pod's lifetime)
    Medium:     
    SizeLimit:  <unset>
  k8tz:
    Type:        EmptyDir (a temporary directory that shares a pod's lifetime)
    Medium:      
    SizeLimit:   <unset>
QoS Class:       Burstable
Node-Selectors:  <none>
Tolerations:     node.kubernetes.io/not-ready:NoExecute op=Exists for 300s
                 node.kubernetes.io/unreachable:NoExecute op=Exists for 300s
Events:
  Type     Reason     Age                From               Message
  ----     ------     ----               ----               -------
  Normal   Scheduled  12m                default-scheduler  Successfully assigned cluster-postgresql3/postgresql-ha-1721745843-pgpool-748855b95-d4cz7 to pslpsepinfkub04
  Normal   Pulled     12m                kubelet            Container image "quay.io/k8tz/k8tz:0.14.0" already present on machine
  Normal   Created    12m                kubelet            Created container k8tz
  Normal   Started    12m                kubelet            Started container k8tz
  Normal   Pulled     12m                kubelet            Container image "docker.io/bitnami/pgpool:4.5.2-debian-12-r2" already present on machine
  Normal   Created    12m                kubelet            Created container pgpool
  Normal   Started    12m                kubelet            Started container pgpool
  Warning  Unhealthy  10m (x8 over 12m)  kubelet            Readiness probe failed: psql: error: connection to server on socket "/opt/bitnami/pgpool/tmp/.s.PGSQL.5432" failed: FATAL:  failed to create a backend 0 connection
DETAIL:  not executing failover because failover_on_backend_error is off
  Warning  Unhealthy  8m6s  kubelet  Readiness probe failed: command "bash -ec PGPASSWORD=${PGPOOL_POSTGRES_PASSWORD} psql -U \"postgres\" -d \"postgres\" -h /opt/bitnami/pgpool/tmp -tA -c \"SELECT 1\" >/dev/null" timed out

netstat -an in pgpool:

$ netstat -an
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       
tcp        0      0 127.0.0.1:9898          0.0.0.0:*               LISTEN      
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      
tcp        6      0 172.31.57.63:53800      172.31.62.222:5432      ESTABLISHED 
tcp        0      0 172.31.57.63:33852      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:52158      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:54344      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:35448      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:40108      172.31.57.43:5432       ESTABLISHED 
tcp        0      0 172.31.57.63:35424      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:50098      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:51778      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:35064      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:52390      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:53540      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:53772      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:51584      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:54366      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52604      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52384      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52594      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52988      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:51002      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:53084      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:60792      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:34852      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:53184      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:33464      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:53160      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:35244      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:35446      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:51390      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:51212      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:54356      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:34382      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:59028      172.31.62.222:5432      ESTABLISHED 
tcp        0      0 172.31.57.63:34620      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:33684      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:52602      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:34882      172.31.57.43:5432       ESTABLISHED 
tcp        0      0 172.31.57.63:54164      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:54070      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52788      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:34046      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:52974      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52962      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:34476      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:54566      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:53984      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52772      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:57646      172.31.17.174:5432      ESTABLISHED 
tcp        0      0 172.31.57.63:34240      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:52966      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:35554      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:35436      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:52990      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52426      172.31.17.174:5432      ESTABLISHED 
tcp        0      0 172.31.57.63:54364      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:33674      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:51234      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:53396      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52014      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:52864      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:51914      172.31.17.174:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:51478      172.31.62.222:5432      TIME_WAIT   
tcp        0      0 172.31.57.63:35646      172.31.57.43:5432       TIME_WAIT   
tcp        0      0 172.31.57.63:5432       172.31.17.188:43862     ESTABLISHED 
tcp        0      0 ::1:9898                :::*                    LISTEN      
tcp        0      0 :::5432                 :::*                    LISTEN      
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node Path
unix  2      [ ACC ]     STREAM     LISTENING     3710659673 /opt/bitnami/pgpool/tmp/.s.PGSQL.9898
unix  2      [ ACC ]     STREAM     LISTENING     3710639296 /opt/bitnami/pgpool/tmp/.s.PGSQL.5432

log pgpool:

pgpool.log
@DCx14 DCx14 added the tech-issues The user has a technical issue about an application label Jul 23, 2024
@github-actions github-actions bot added the triage Triage is needed label Jul 23, 2024
@carrodher
Copy link
Member

The issue may not be directly related to the Bitnami container image/Helm chart, but rather to how the application is being utilized, configured in your specific environment, or tied to a specific scenario that is not easy to reproduce on our side.

If you think that's not the case and are interested in contributing a solution, we welcome you to create a pull request. The Bitnami team is excited to review your submission and offer feedback. You can find the contributing guidelines here.

Your contribution will greatly benefit the community. Feel free to reach out if you have any questions or need assistance.

Suppose you have any questions about the application, customizing its content, or technology and infrastructure usage. In that case, we highly recommend that you refer to the forums and user guides provided by the project responsible for the application or technology.

With that said, we'll keep this ticket open until the stale bot automatically closes it, in case someone from the community contributes valuable insights.

@fmulero
Copy link
Collaborator

fmulero commented Aug 7, 2024

It could be related to #23219

@github-actions GitHub Actions
Copy link

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

@github-actions github-actions bot added the stale 15 days without activity label Aug 23, 2024
@github-actions GitHub Actions
Copy link

Due to the lack of activity in the last 5 days since it was marked as "stale", we proceed to close this Issue. Do not hesitate to reopen it later if necessary.

@bitnami-bot bitnami-bot closed this as not planned Won't fix, can't repro, duplicate, stale Aug 28, 2024
@juan131
Copy link
Contributor

juan131 commented Nov 26, 2024

Duplicate of #23219

@juan131 juan131 marked this as a duplicate of #23219 Nov 26, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@carrodher carrodher

Labels
postgresql-ha solved stale 15 days without activity tech-issues The user has a technical issue about an application triage Triage is needed
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@juan131 @bitnami-bot @carrodher @fmulero @DCx14