[Postgres-xc-general] ERROR: Failed to get pooled connections CONTEXT: SQL statement "EXECUTE DIRECT ON (cn2) 'SELECT pg_catalog.pg_try_advisory_xact_lock_shared(65535, 0)'"

[Wilkerson, D. <dwi...@fu...>]

Hi. I am trying to stand up a multi node cluster across multiple servers. The current configuration I have is one coordinator and two datanodes per server on 3 servers. Each has a GTM proxy configured. I have two more servers configured to be the GTM master and slave/standby.

When executing create database in my cluster after all the config work (including the create node calls) I am getting the following error. ERROR:  Failed to get pooled connections
CONTEXT:  SQL statement "EXECUTE DIRECT ON (cn2) 'SELECT pg_catalog.pg_try_advisory_xact_lock_shared(65535, 0)'"

I noticed that the coordinator on each node is added as host = local host after using the initdb command to create the coordinator nodes. When executing the create node calls to add the other coordinators and datanodes to each other I am using the IP addresses of the servers instead; I have also tried the DNS names also with no luck.

The output of pgxc_node from one of the cluster coordinator/datanodes looks like:

 oid  | node_name | node_type | node_port |  node_host   | nodeis_primary | nodeis_preferred |   node_id
-------+-----------+-----------+-----------+--------------+----------------+------------------+-------------
 11198 | cn1       | C         |      5432 | localhost    | f              | f                | -1178713634
 16393 | cn2       | C         |      5432 | xx.xx.40.11  | f              | f                | -1923125220
 16394 | cn3       | C         |      5432 | xx.xx.40.123 | f              | f                |  1101067607
 16395 | dn1       | D         |     15432 | xx.xx.33.198 | t              | t                |  -560021589
 16396 | dn2       | D         |     15433 | xx.xx.33.198 | f              | f                |   352366662
 16397 | dn3       | D         |     15432 | xx.xx.40.11  | f              | f                |  -700122826
 16398 | dn4       | D         |     15433 | xx.xx.40.11  | f              | f                |   823103418
 16399 | dn5       | D         |     15432 | xx.xx.40.123 | f              | f                | -1268658584
 16400 | dn6       | D         |     15433 | xx.xx.40.123 | f              | f                | -1765597067

The entry with localhost was added after using the initdb command. I used it this way: initdb –D CN1 - - node name CN1

The last troubleshooting step I tried as issuing and alter node to change the host of CN1 to it's IP instead of local host, but I get the ERROR:  Failed to get pooled connections error there too. I have tried to reload the pool several times with select pgxc_pool_reload(); That didn't work either.  I have added the entries in the pg_hba.conf for the two subnets and copied the hba file to all the servers and their nodes.


Any help would be greatly appreciated. I'm certain it's something silly I did wrong in the cluster config, but am having trouble finding it. It's something very, very tiny I imagine. If there is any other config information needed let me know. Hope I've capture the most important bits.

Thanks!
dew