Deploying PostgreSQL in MiniShift/OpenShift

The last post quickly outlined on how you can setup MiniShift for playing around with OpenShift on your workstation. In this post we’ll setup PostgreSQL in MiniShift using the default PostgreSQL image that already comes with MiniShift.

When MiniShift is currently stopped start it up:

dwe@box:~$ minishift start
dwe@box:~$ eval $(minishift oc-env)
dwe@box:~$ which oc
/home/dwe/.minishift/cache/oc/v3.9.0/linux/oc

With OpenShift everything is organized into projects and the first thing you need to do is to create a project. You can either do that using the command line or the web interface. Doing it using the command line is quite simple and fast. The first step is to login to OpenShift:

dwe@box:~$ oc login
Authentication required for https://192.168.99.100:8443 (openshift)
Username: system
Password: 
Login successful.

You don't have any projects. You can try to create a new project, by running

    oc new-project 

The output of the login command already tells what you need to do to create a new project:

dwe@box:~$ oc new-project postgres
Now using project "postgres" on server "https://192.168.99.100:8443".

You can add applications to this project with the 'new-app' command. For example, try:

    oc new-app centos/ruby-22-centos7~https://github.com/openshift/ruby-ex.git

to build a new example application in Ruby.

Doing the same with the web interface of course needs more time:

To get a list of available images from the command line:

dwe@box:~$ oc get imagestreams --namespace openshift
NAME             DOCKER REPO                                TAGS                           UPDATED
dotnet           172.30.1.1:5000/openshift/dotnet           2.0,latest                     4 hours ago
dotnet-runtime   172.30.1.1:5000/openshift/dotnet-runtime   2.0,latest                     4 hours ago
httpd            172.30.1.1:5000/openshift/httpd            2.4,latest                     4 hours ago
jenkins          172.30.1.1:5000/openshift/jenkins          1,2,latest                     4 hours ago
mariadb          172.30.1.1:5000/openshift/mariadb          10.1,10.2,latest               4 hours ago
mongodb          172.30.1.1:5000/openshift/mongodb          2.4,2.6,3.2 + 2 more...        4 hours ago
mysql            172.30.1.1:5000/openshift/mysql            5.5,5.6,5.7 + 1 more...        4 hours ago
nginx            172.30.1.1:5000/openshift/nginx            1.10,1.12,1.8 + 1 more...      4 hours ago
nodejs           172.30.1.1:5000/openshift/nodejs           8,latest,0.10 + 2 more...      4 hours ago
perl             172.30.1.1:5000/openshift/perl             5.24,latest,5.16 + 1 more...   4 hours ago
php              172.30.1.1:5000/openshift/php              5.5,5.6,7.0 + 2 more...        4 hours ago
postgresql       172.30.1.1:5000/openshift/postgresql       9.2,9.4,9.5 + 2 more...        4 hours ago
python           172.30.1.1:5000/openshift/python           3.3,3.4,3.5 + 3 more...        4 hours ago
redis            172.30.1.1:5000/openshift/redis            3.2,latest                     4 hours ago
ruby             172.30.1.1:5000/openshift/ruby             2.0,2.2,2.3 + 2 more...        4 hours ago
wildfly          172.30.1.1:5000/openshift/wildfly          10.1,8.1,9.0 + 2 more...       4 hours ago

Quite a few to choose from but we of course are interested in the postgresql one.

dwe@box:~$ oc new-app -e POSTGRESQL_USER=blubb -e POSTGRESQL_PASSWORD=blubb -e POSTGRESQL_DATABASE=blubb postgresql

Checking the logs is always a good idea. For this we need to know the pod:

dwe@box:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
postgresql-1-8n85h   1/1       Running   0          5m

Now that we know the pod we can ask for the logs:

dwe@box:~$ oc logs postgresql-1-8n85h
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/data/userdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/data/userdata -l logfile start


WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
 done
server started
=> sourcing /usr/share/container-scripts/postgresql/start/set_passwords.sh ...
ALTER ROLE
waiting for server to shut down.... done
server stopped
Starting server...
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".

Looks good so far. How can we work with the PostgreSQL instance now? One way is to start a remote shell:

dwe@box:~$ oc rsh postgresql-1-8n85h
sh-4.2$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
1000100+     1     0  0 13:29 ?        00:00:00 postgres
1000100+    57     1  0 13:29 ?        00:00:00 postgres: logger process  
1000100+    59     1  0 13:29 ?        00:00:00 postgres: checkpointer process  
1000100+    60     1  0 13:29 ?        00:00:00 postgres: writer process  
1000100+    61     1  0 13:29 ?        00:00:00 postgres: wal writer process  
1000100+    62     1  0 13:29 ?        00:00:00 postgres: autovacuum launcher pr
1000100+    63     1  0 13:29 ?        00:00:00 postgres: stats collector proces
1000100+    85     0  0 13:46 ?        00:00:00 /bin/sh

sh-4.2$ psql -c "\l"
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
   
-----------+----------+----------+------------+------------+--------------------
---
 blubb     | blubb    | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres        
  +
           |          |          |            |            | postgres=CTc/postgr
es
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres        
  +
           |          |          |            |            | postgres=CTc/postgr
es
(4 rows)

This is usually not what you want to do. What you rather want is to make the instance available from outside the cluster. How can you do that? Either you do port forwarding:

dwe@box:~$ oc port-forward postgresql-1-8n85h 5432
Forwarding from 127.0.0.1:5432 -> 5432

This will stay in the foreground. From another session you can use psql to connect:

dwe@box:~$ psql -h localhost -U blubb blubb
psql (9.5.12, server 9.6.5)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.

blubb=> 

… or you can expose a service:

dwe@box:~$ oc expose dc postgresql --type=LoadBalancer --name=mpostgresql-ingress
service "mpostgresql-ingress" exposed
dwe@box:~$ oc get svc
NAME                  TYPE           CLUSTER-IP     EXTERNAL-IP                     PORT(S)          AGE
mpostgresql-ingress   LoadBalancer   172.30.15.98   172.29.104.134,172.29.104.134   5432:31734/TCP   38s

From now on you can connect using the MiniShift IP and the port listed above:

dwe@box:~$ psql -h $(minishift ip) -p 31734 -U blubb
Password for user blubb: 
psql (9.5.12, server 9.6.5)
WARNING: psql major version 9.5, server major version 9.6.
         Some psql features might not work.
Type "help" for help.

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

blubb=> 

Be aware that the storage is not persistent in that case and you’ll lose everything when the container is stopped. In the next post we’ll look into how we can deploy the containers which are provided by EnterpriseDB.