pgpool setup with cloud sql proxy

[sai-meyyappan]

Hi, I am trying to setup pgpool with a cloud sql proxy instead of a postgres operator. So the data flow would be Application->pgpool->cloud sql proxy ->Postgres db

Wanted to check if this is possible?

Postgres version - 14

[pengbo0328]

Sorry, I am not familiar with cloud sql proxy. If cloud sql proxy supports for PostgreSQL frontend/backend protocol, I think it should work.

[sai-meyyappan]

Thanks @pengbo0328 ! I am further along and my setup looks like this -

pgpool deployment ---> Postgres 14 Cloudsql db pgpool deployment ---> Postgres 14 replica Cloudsql db

Both db's are setup the same way and are SSL enabled and I can connect to the primary db just fine but the replica fails with

Configuring Pgpool-II...
Custom pgpool.conf file detected. Use custom configuration files.
Generating pool_passwd...
Skip generating pcp.conf. PGPOOL_PCP_USER or PGPOOL_PCP_PASSWORD isn't defined.
Skip generating pool_hba.conf due to enable_pool_hba = off.
Starting Pgpool-II...
2022-06-28 11:25:25.429: main pid 57: LOG:  Backend status file /tmp/pgpool_status does not exist
2022-06-28 11:25:25.429: main pid 57: LOCATION:  pgpool_main.c:3650
2022-06-28 11:25:25.429: main pid 57: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2022-06-28 11:25:25.429: main pid 57: LOCATION:  health_check.c:544
2022-06-28 11:25:25.429: main pid 57: LOG:  memory cache initialized
2022-06-28 11:25:25.429: main pid 57: DETAIL:  memcache blocks :64
2022-06-28 11:25:25.429: main pid 57: LOCATION:  pool_memqcache.c:2059
2022-06-28 11:25:25.429: main pid 57: LOG:  allocating (136981824) bytes of shared memory segment
2022-06-28 11:25:25.429: main pid 57: LOCATION:  pgpool_main.c:3511
2022-06-28 11:25:25.429: main pid 57: LOG:  allocating shared memory segment of size: 136981824 
2022-06-28 11:25:25.429: main pid 57: LOCATION:  pool_shmem.c:60
2022-06-28 11:25:25.659: main pid 57: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2022-06-28 11:25:25.659: main pid 57: LOCATION:  health_check.c:544
2022-06-28 11:25:25.659: main pid 57: LOG:  health_check_stats_shared_memory_size: requested size: 12288
2022-06-28 11:25:25.659: main pid 57: LOCATION:  health_check.c:544
2022-06-28 11:25:25.659: main pid 57: LOG:  memory cache initialized
2022-06-28 11:25:25.659: main pid 57: DETAIL:  memcache blocks :64
2022-06-28 11:25:25.659: main pid 57: LOCATION:  pool_memqcache.c:2059
2022-06-28 11:25:25.661: main pid 57: LOG:  pool_discard_oid_maps: discarded memqcache oid maps
2022-06-28 11:25:25.661: main pid 57: LOCATION:  pgpool_main.c:3592
2022-06-28 11:25:25.718: main pid 57: LOG:  Setting up socket for 0.0.0.0:9999
2022-06-28 11:25:25.718: main pid 57: LOCATION:  pgpool_main.c:812
2022-06-28 11:25:25.718: main pid 57: LOG:  Setting up socket for :::9999
2022-06-28 11:25:25.718: main pid 57: LOCATION:  pgpool_main.c:812
2022-06-28 11:25:25.725: main pid 57: LOG:  find_primary_node_repeatedly: waiting for finding a primary node
2022-06-28 11:25:25.725: main pid 57: LOCATION:  pgpool_main.c:3383
2022-06-28 11:25:25.726: health_check pid 93: LOG:  process started
2022-06-28 11:25:25.726: health_check pid 93: LOCATION:  pgpool_main.c:728
2022-06-28 11:25:25.726: sr_check_worker pid 92: LOG:  process started
2022-06-28 11:25:25.726: sr_check_worker pid 92: LOCATION:  pgpool_main.c:728
2022-06-28 11:25:25.727: pcp_main pid 91: LOG:  PCP process: 91 started
2022-06-28 11:25:25.727: pcp_main pid 91: LOCATION:  pcp_child.c:161
2022-06-28 11:25:25.730: main pid 57: LOG:  pgpool-II successfully started. version 4.3.1 (tamahomeboshi)
2022-06-28 11:25:25.730: main pid 57: LOCATION:  pgpool_main.c:489
2022-06-28 11:25:25.730: main pid 57: LOG:  node status[0]: 0
2022-06-28 11:25:25.730: main pid 57: LOCATION:  pgpool_main.c:500
2022-06-28 11:25:51.132: child pid 81: LOG:  new connection received
2022-06-28 11:25:51.133: child pid 81: DETAIL:  connecting host=localhost port=59842
2022-06-28 11:25:51.133: child pid 81: LOCATION:  child.c:1826
2022-06-28 11:25:51.252: child pid 81: ERROR:  backend authentication failed
2022-06-28 11:25:51.252: child pid 81: DETAIL:  backend response with kind 'E' when expecting 'R'
2022-06-28 11:25:51.252: child pid 81: HINT:  This issue can be caused by version mismatch (current version 3)

Do you have any pointers ?

[nishit130]

Hello there, @sai-meyyappan. I have a setup that is very similar to yours. Were you successful in getting pgpool-ii to work with cloudsql? can you share some resources