Re: [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...>]

Thank you for the replies Masataka and Koichi. I appreciate them very much.

You were both right. It was definitely some config relates
issues/mismatches. I suppose my mind was pretty tired after configuring
each config file on everyone one of the servers and trying to troubleshoot
all day yesterday. Cross connecting with psql and using netstat definitely
helped suss out the problematic config items across the nodes and servers.

To fix my issue I made some corrections to pg_hba.conf for my
coordinators. My data nodes were configured correctly, but the
coordinators were not. I also noticed on CN1 and CN3, I missed changing
the listen address from localhost to * (0.0.0.0). That issue was apparent
with netstat -an

I'm going to have a look at the pgxc_ctl tool to simplify cluster starts
and stops as well as the config of future clusters for production use.
This is only a prototype environment.

Thanks again for the help my friends. I appreciate it.

dw

On 6/10/14 6:06 AM, "Masataka Saito" <pg...@gm...> wrote:

>Hello Wilkerson,
>
>I'm afraid that you're using Postgres-XC 1.2 which has such ugly bug
>fixed on 1.2.1.
>Or Postgres-XC nodes must trust other nodes: you have to add
>pg_hba.conf entry for subnet xx.xx.40.0/24 and xx.xx.33.0/24 with
>trust authentication method, so you can't specify other authentication
>method which requires extra dialogue with client, e.g. password or md5
>are not allowed.
>
>If both suggestions don't hit, please test that you can connect to
>other nodes from cn1 by psql command. I think it's a good way to find
>the component in which the cause resides.
>
>Regards.
>
>On 10 June 2014 07:13, Wilkerson, Daniel <dwi...@fu...> wrote:
>> 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
>>
>> 
>>-------------------------------------------------------------------------
>>-----
>> HPCC Systems Open Source Big Data Platform from LexisNexis Risk
>>Solutions
>> Find What Matters Most in Your Big Data with HPCC Systems
>> Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
>> Leverages Graph Analysis for Fast Processing & Easy Data Exploration
>> http://p.sf.net/sfu/hpccsystems
>> _______________________________________________
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general