<p>I have installed postgres cluster Zalando postgres operator <a href="https://github.com/zalando/postgres-operator" rel="nofollow noreferrer">https://github.com/zalando/postgres-operator</a>
How can I get access to postgres database from outside?
I tried to change cluster service type from ClusterIP to NodePort, but it is overwritten automatically.</p>

<p>The process is explained here:
<a href="https://postgres-operator.readthedocs.io/en/latest/user/" rel="nofollow noreferrer">https://postgres-operator.readthedocs.io/en/latest/user/</a></p>
<p>You need 2 steps.</p>
<ol>
<li>script that opens up and forwards a port to your local machine.</li>
</ol>
<p>I created a script called <code>set_dbforwarding.sh</code>.  You need
to change the names in the script to your cluster names and settings!
so <code>cdf-cluster</code>  should become <code>yourclustername</code>.</p>
<pre><code>   #!/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
</code></pre>
<p>executed like:</p>
<pre><code>     ./set_dbforwarding.sh yourclusternamespace
</code></pre>
<ol start="2">
<li><p>connect to your cluster with the correct credentials. <code>restore_db.sh</code> script.</p>
<pre><code>  #!/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  
</code></pre>
</li>
</ol>
<p>used like</p>
<pre><code>     ./restore_db.sh namespace databasename backup.gz
</code></pre>
<ol start="3">
<li><p>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.</p>
<p>editconnection -&gt; connectionsettings -&gt; initialization -&gt; Keep-Alive.</p>
</li>
</ol>
