This blog refers to an older version of EDB’s Postgres on Kubernetes offering that is no longer available.
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:
After a few seconds another pod is running:
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.