Infrastructure at your Service

Daniel Westermann

Scaling the EDB containers in MiniShift/OpenShift

When you followed the last post you should have two pgpool instances and one EDB Postgres instance running in OpenShift. pgpool is responsible for the load balancing, meaning: Send write requests to the master instance and spread read requests over all instances. In the current setup this does not make much sense as we only have one instance so in this post we will scale the setup to finally consist of one master and one read only replica container.

Lets check what pods are currently running:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-vldkj   1/1       Running   2          5d
edb-pgpool-1-699vh   1/1       Running   2          5d
edb-pgpool-1-nsgrm   1/1       Running   2          5d

As expected, two pgpool containers and one database container are up an running. When we login to one of the pgpool container we should see the pgpool processes:

dwe@dwe:~$ oc rsh edb-pgpool-1-699vh
sh-4.2$ ps -ef | grep pool
edbuser     63     0  0 15:27 ?        00:00:00 pgpool -D -d -f /etc/sysconfig/edb/pgpool3.5/pgpool.conf -a /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
edbuser     64    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     65    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     66    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     67    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     68    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     69    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     70    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     71    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     72    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     73    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     74    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     75    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     76    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     77    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     78    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     79    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     80    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     81    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     82    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     83    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     84    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     85    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     86    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     87    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     88    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     89    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     90    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     91    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     92    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     93    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     94    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     95    63  0 15:27 ?        00:00:00 pgpool: wait for connection request
edbuser     96    63  0 15:27 ?        00:00:00 pgpool: PCP: wait for connection request
edbuser     97    63  0 15:27 ?        00:00:00 pgpool: worker process
edbuser    365   342  0 15:30 ?        00:00:00 grep pool

What I am interested in is the backend configuration. As we do have only one database container there should be only one backend configured:

sh-4.2$ cat /etc/sysconfig/edb/pgpool3.5/pgpool.conf | grep backend | egrep -v "^ |^#"
backend_hostname0 = '172.17.0.7'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
								   # but also for ordinary conection to backend.
fail_over_on_backend_error = off

Fine, this is what is expected. The same is of course true for the other container. The question is: We have two pgpool instances running against one database instance, how can we connect then? What in addition happened when we deployed the setup is that a service was created:

dwe@dwe:~$ oc get svc -o wide
NAME          TYPE           CLUSTER-IP      EXTERNAL-IP                     PORT(S)          AGE       SELECTOR
edb-service   LoadBalancer   172.30.162.55   172.29.228.247,172.29.228.247   5444:30726/TCP   5d        lb=edb-pgpool

This services proxies the request to one of the pgpool instances which then routes the connection to the database instance. Lets try to connect from outside:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U postgres -W
Password for user postgres: 
psql: FATAL:  md5 authentication failed
DETAIL:  pool_passwd file does not contain an entry for "postgres"

Hm. Looking at the available databases and users in the database containers:

dwe@dwe:~$ oc rsh edb-as10-0-1-vldkj
sh-4.2$ psql edb
psql.bin (10.3.8)
Type "help" for help.

edb=# \l
                                 List of databases
   Name    |    Owner     | Encoding  | Collate | Ctype | ICU |  Access privileges  
-----------+--------------+-----------+---------+-------+-----+---------------------
 edb       | enterprisedb | SQL_ASCII | C       | C     |     | 
 postgres  | edbuser      | SQL_ASCII | C       | C     |     | 
 template0 | edbuser      | SQL_ASCII | C       | C     |     | =c/edbuser         +
           |              |           |         |       |     | edbuser=CTc/edbuser
 template1 | edbuser      | SQL_ASCII | C       | C     |     | =c/edbuser         +
           |              |           |         |       |     | edbuser=CTc/edbuser
(4 rows)

edb=# \du
                                         List of roles
       Role name       |                         Attributes                         | Member of 
-----------------------+------------------------------------------------------------+-----------
 aq_administrator_role | No inheritance, Cannot login                              +| {}
                       | Profile default                                            | 
 edbuser               | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                       | Profile default                                            | 
 enterprisedb          | Superuser                                                 +| {}
                       | Profile default                                            | 
 repl                  | Replication                                               +| {}
                       | Profile default                                            | 

… it becomes clear: There is no user postgres. Using the edbuser it works fine:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# 

How can we verify that the service actually is routing the connection to both pgpool instances? PostgreSQL comes with a set of administration functions and one of those can be used to verify that.

Do a first connection and ask for the client address (the client is one of the pgpool instances in this case):

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# select inet_client_addr();
 inet_client_addr 
------------------
 172.17.0.6
(1 row)

Do a second one:

dwe@dwe:~$ psql -h $(minishift ip) -p 30726 -U edbuser -W postgres
Password for user edbuser: 
psql (9.5.12, server 10.3.8)
WARNING: psql major version 9.5, server major version 10.
         Some psql features might not work.
Type "help" for help.

postgres=# select inet_client_addr();
 inet_client_addr 
------------------
 172.17.0.7
(1 row)

postgres=# 

This two IP addresses are my two pgpool instances so this confirms that the service is behaving as expected. As having a single instance in that setup is somehow useless lets scale the setup by adding another database container. We’ll be doing that with the console for now:

Selection_022

After a few seconds another pod is running:
Selection_023

What I expect is that the pgpool instances got re-configured to include the new backend. Looking at the pods:

dwe@dwe:~$ oc get pods
NAME                 READY     STATUS    RESTARTS   AGE
edb-as10-0-1-frc99   1/1       Running   0          15m
edb-as10-0-1-gkpgq   1/1       Running   0          31m
edb-pgpool-1-lvwsq   1/1       Running   0          31m
edb-pgpool-1-nh4qb   1/1       Running   0          31m

Lets check the configuration of the first pgpool instance:

dwe@dwe:~$ oc rsh edb-pgpool-1-lvwsq
sh-4.2$ ps -ef | grep conf
edbuser   1918     0  0 07:43 ?        00:00:00 pgpool -D -d -f /etc/sysconfig/edb/pgpool3.5/pgpool.conf -a /etc/sysconfig/edb/pgpool3.5/pool_hba.conf
edbuser   2508  2483  0 07:48 ?        00:00:00 grep conf
sh-4.2$ cat /etc/sysconfig/edb/pgpool3.5/pgpool.conf | grep backend | egrep -v "^ |^#"
backend_hostname0 = '172.17.0.7'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/var/lib/edb/as9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
								   # but also for ordinary conection to backend.
fail_over_on_backend_error = off
backend_hostname1 = '172.17.0.5'
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = '/data1'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
sh-4.2$ 

Here we go, works as expected. What is a bit strange is that $PGDATA on the second backend is in /data1 where it is in /var/lib/edb/as9.6/data on the first backend. From now on read only queries should land on either the slave or the master node node and statements that modify data should land on the master node, lets check what is master and what is replica:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "show pool_nodes" postgres
Password for user edbuser: 
 node_id |  hostname  | port | status | lb_weight |  role   | select_cnt 
---------+------------+------+--------+-----------+---------+------------
 0       | 172.17.0.7 | 5444 | 2      | 0.500000  | primary | 1
 1       | 172.17.0.5 | 5444 | 2      | 0.500000  | standby | 0
(2 rows)

When I do two read only statements I should be round-robined:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.7
(1 row)

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

Disabling load balancing should bring me to the master:

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select /*NO LOAD BALANCE*/ inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

dwe@dwe:~$ psql -h $(minishift ip) -p 31014 -U edbuser -c "select /*NO LOAD BALANCE*/ inet_server_addr()" postgres
Password for user edbuser: 
 inet_server_addr 
------------------
 172.17.0.5
(1 row)

Works quite fine. In the next post we’ll add ConfigMaps to customize our deployment. Usually you want to pre-configure the deployment so it fits for your requirements and that is what ConfigMaps are for.

Leave a Reply

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure