The process is explained here: https://postgres-operator.readthedocs.io/en/latest/user/

You need 2 steps.

1) script that opens up and forwards a port to your local machine.

I created a script called set_dbforwarding.sh. You need to change the names in the script to your cluster names and settings! so cdf-cluster should become yourclustername.

   #!/usr/bin/env bash
     
    set -u   # crash on missing env variables
    set -e   # stop on any error
    set -x   # print what we are doing
     
    export NAMESPACE=$1
     
    export PGMASTER=$(kubectl -n cdf-acc get pods -o jsonpath={.items..metadata.name} -l application=spilo,cluster-name=cdf-cluster,spilo-role=m
    
    # PGMASTER should be now the master node. There are cases under failover
    # that you should connect to a different node in your cluster.
    # If you want to change something you should always connect to the master.
    # otherwise you get 
     
    # set up port forward
    kubectl -n $NAMESPACE port-forward $PGMASTER 6432:5432
     
    # get the password..it is printend in your terminal
    # so you can use it in your db tool of choice.
    export PGPASSWORD=$(kubectl -n $NAMESPACE get secret cdf.cdf-cluster.credentials.postgresql.acid.zalan.do -o 'jsonpath={.data.password}' | b
    export PGSSLMODE=require

executed like:

     ./set_dbforwarding.sh yourclusternamespace

2) connect to your cluster with the correct credentials. restore_db.sh script.

     #!/usr/bin/env bash

     set -u   # crash on missing env variables
     set -e   # stop on any error
     set -x   # print what we are doing

     export NAMESPACE=$1  
     export DATABASE=$2  
     export DATABASEDUMP=$3  

     export PGMASTER=$(kubectl -n $NAMESPACE get pods -o jsonpath={.items..metadata.name} -l application=spilo,cluster-name=cdf-cluster,spilo-rol
     export PGPASSWORD=$(kubectl -n $NAMESPACE get secret postgres.cdf-cluster.credentials.postgresql.acid.zalan.do -o 'jsonpath={.data.password}
     export PGSSLMODE=require  

     # examples you can run now the the above ENV variables set.

     # psql -h 127.0.0.1 -U postgres -d cdf -p 6432  
     #cat ~/dumps/cbs_schema_wfs.sql | psql -h 127.0.0.1 -U postgres -d cdf -p 6432

     # pg_restore -h 127.0.0.1 -U postgres -p 6432 -d $2 -c $3  
     # data only  
     # pg_restore -h 127.0.0.1 -U postgres -p 6432 -d $2 -a $3  
     # everything  
     pg_restore -h 127.0.0.1 -U postgres -p 6432 -d $2 $3  

used like

     ./restore_db.sh namespace databasename backup.gz

3) Tip if you are using a Database tool like DBbeaver make sure to check the keep-alive box every 5 seconds or so. Or the connection will be dropped. The keep alive will keep it open. But the settings is rather hidden on DBBeaver.

editconnection -> connectionsettings -> initialization -> Keep-Alive.