Port forward to postgres kubernetes pod fails with connection reset when executing certain commands via psql

Question

I have a postgres deployment, whose configuration looks like this

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-13.5-0
  postgresVersion: 13
  users:
  - name: hippo
    databases: ["hippo"]
    options: "CREATEDB"
  instances:
  - name: instance1
    dataVolumeClaimSpec:
      accessModes:
      - "ReadWriteOnce"
      resources:
        requests:
          storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.36-0
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi

And I forward the local port 5432 to it, like so

DB_PORT=5432
PG_CLUSTER_PRIMARY_POD=$(microk8s kubectl get pod -o name \
-l postgres-operator.crunchydata.com/cluster=hippo,postgres-operator.crunchydata.com/role=master)
microk8s kubectl port-forward "${PG_CLUSTER_PRIMARY_POD}" ${DB_PORT}:${DB_PORT}

And I can then connect via psql. I can list the databases and connect to the hippo database.

rob@rob-Vostro-5402:~$ psql postgresql://hippo:Zw%5EAQuPf%3D%3Bi%3B%3F2%3E1RRbLTLrT@localhost:5432/hippo
psql (13.7 (Ubuntu 13.7-1.pgdg20.04+1), server 13.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

hippo=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 hippo     | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | hippo=CTc/postgres
 postgres  | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | 
 template0 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

hippo=> \c hippo
psql (13.7 (Ubuntu 13.7-1.pgdg20.04+1), server 13.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "hippo" as user "hippo".

However, when I run \dt, I get disconnected.

hippo=> \dt
SSL SYSCALL error: EOF detected
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?>

And the terminal in which I was running the port-forwarding now shows an error.

Forwarding from 127.0.0.1:5432 -> 5432
Forwarding from [::1]:5432 -> 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
E0625 15:59:16.859963   72918 portforward.go:406] an error occurred forwarding 5432 -> 5432: error forwarding port 5432 to pod 8f58bd2f87d0ef63b969725920c98793f0dd1f41a25dc04bfe1b06a0ad7b58fc, uid : failed to execute portforward in network namespace "/var/run/netns/cni-0f76b252-b44c-017f-e337-b0285117cc4e": read tcp4 127.0.0.1:46248->127.0.0.1:5432: read: connection reset by peer
E0625 15:59:16.860854   72918 portforward.go:234] lost connection to pod

Any help would be much appreciated. Thanks

What does the db server's log file say?

jjanes
– jjanes

2022-06-25 16:29:52 +00:00
Commented Jun 25, 2022 at 16:29 — jjanes
– jjanes, Commented Jun 25, 2022 at 16:29

Fritz Duchardt · Accepted Answer · 2022-06-26 06:43:06Z

8

I am used to the same brittle behavior of port forwarding to Postgres and resorted to a simple reconnect as a workable solution:

while true; do kubectl port-forward "$path" -n "$namespace" "$ports"; done

answered Jun 26, 2022 at 6:43

Fritz Duchardt

12.1k5 gold badges48 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Max Binnewies Over a year ago

Genius! Sometimes the simplest solution is the best.

Collectives™ on Stack Overflow

Port forward to postgres kubernetes pod fails with connection reset when executing certain commands via psql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related