0 Replies Latest reply on Oct 24, 2017 10:57 AM by Cheng Fang

    Remotely Connecting to a Database Server Running in OpenShift

    Cheng Fang Master

      From my experience of running JBeret batch applications, I always wanted a database server to be readily available for my batch application, either as the datastore for jdbc batch job repository, or as the application database.  Previously, I tried to leverage the pool of database servers provisioned and maintained by our excellent QE team, depending on db server instance availability.  Now with OpenShift, I can  connect to the database server running in my OpenShift Online project.

       

      By default, a database application is only accessible to the other applications in the same OpenShift project.  However, there is an easy way to expose it externally, via port forwarding.

       

      In my project, I have a PostgreSQL 9.5 server running at port 5432.  Next I'll show you how to set up port forwarding such that my JBeret batch applications running in my local machine can access that PostgreSQL instance at localhost:5432.

       

      Login to your OpenShift Online account from oc (the command below uses my account url and yours may be different), enter username and password:

      oc login https://api.starter-us-east-1.openshift.com
      

       

      Get the POD name of the PostgreSQL instance POD:

      oc get pod
      

       

      Then do the port-forwarding with the above POD name:

      oc port-forward postgresql-1-mgxxxx  5432:5432
      Forwarding from 127.0.0.1:5432 -> 5432
      Forwarding from [::1]:5432 -> 5432
      

       

      Now the db is available at localhost:5432.  Let's run psql tool in another terminal window in localhost to verify:

      psql sampledb jberet --host=127.0.0.1 --port=5432
      psql (9.6.2, server 9.5.7)
      Type "help" for help.
      
      sampledb=> select 1;
       ?column?
      ----------
              1
      (1 row)
      
      sampledb=> select * from movies limit 5;
       rank |                tit                |      grs      |    opn
      ------+-----------------------------------+---------------+------------
          1 | Marvel's The Avengers             | 623357910.000 | 2012-05-04
          2 | The Dark Knight Rises             | 448139099.000 | 2012-07-20
          3 | The Hunger Games                  | 408010692.000 | 2012-03-23
          4 | Skyfall                           | 304360277.000 | 2012-11-09
          5 | The Hobbit: An Unexpected Journey | 303003568.000 | 2012-12-14
      (5 rows)
      
      sampledb=>
      

       

      To usage of oc port-forward command is:

      oc port-forward -h
      Forward 1 or more local ports to a pod
      
      Usage:
        oc port-forward POD [LOCAL_PORT:]REMOTE_PORT [...[LOCAL_PORT_N:]REMOTE_PORT_N] [options]
      
      Examples:
        # Listens on ports 5000 and 6000 locally, forwarding data to/from ports 5000 and 6000 in the pod
        oc port-forward mypod 5000 6000
      
        # Listens on port 8888 locally, forwarding to 5000 in the pod
        oc port-forward mypod 8888:5000
      
        # Listens on a random port locally, forwarding to 5000 in the pod
        oc port-forward mypod :5000
      
        # Listens on a random port locally, forwarding to 5000 in the pod
        oc port-forward mypod 0:5000
      
      Options:
        -p, --pod='': Pod name (deprecated)
      

       

      For JDBC connection, the url should be:

      jdbc:postgresql://localhost:5432/sampledb

       

      For a sample JBeret batch application that work with data source, see GitHub - jberet/intro-jberet: Introduction to batch processing with WildFly and JBeret

       

      For more details on port-forwarding in OpenShift, or other cool features in OpenShift, see https://learn.openshift.com/