Re: [Postgres-xc-general] postgres-xc 0.9.7 : failed to connect to data node/can not connect to datanode 0

[Phil S. <phi...@ne...>]

Michel,

Thanks very much for your speedy reply.

After reading your email, I realized I had forgotten to set the 
listen_addresses parameter to '*' on the coordinators and datanodes.  I 
shut down the whole postgres-xc environment, updated the postgresql.conf 
files on the coordinators and datanodes and fired everything back up 
without issue, using the -o "-i" option for the coordinator/datanodes 
startup.

After doing all this, I am still getting connection issues with datanode 
0 when trying to create a database on coordinator1.

I provided the output of the pgxc_node table as I am thinking maybe 
there are problems with the entries in this table?

Any other suggestions you may have with this issue would be greatly 
appreciated.

Phil



Here are the details:

-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------


shutdown gtm, coord1/data1, coord2/data2
----------------------------------------

updated postgresql.conf on dbhost1 for both coord1 and data1:
------------------------------------------------------------
- listen_addresses = '*'

updated postgresql.conf on dbhost2 for both coord2 and data2:
------------------------------------------------------------
- listen_addresses = '*'


started coordinator and datanodes on each db host adding the -o "-i" as 
per your recommendation:
-----------------------------------------------------------------------------------------------
dbhost1:
     pg_ctl start -D /home/postgres/datanode/data -l 
/home/postgres/datanode/log/datanode1.log -o "-i -p 15432" -Z datanode
     pg_ctl start -D /home/postgres/coordinator/data -l 
/home/postgres/coordinator/log/coordinator1.log -o "-i" -Z coordinator

dbhost2:
     pg_ctl start -D /home/postgres/datanode/data -l 
/home/postgres/datanode/log/datanode2.log -o "-i -p 15432" -Z datanode
     pg_ctl start -D /home/postgres/coordinator/data -l 
/home/postgres/coordinator/log/coordinator2.log -o "-i" -Z coordinator


checked to make sure postgres was running on dbhost1:
-----------------------------------------------------
$ ps -ef | grep postgres

root      1005   587  0 10:22 ?        00:00:00 sshd: postgres [priv]
1058      1083  1005  0 10:22 ?        00:00:01 sshd: postgres@pts/0
1058      1222     1  0 10:47 pts/0    00:00:00 
/home/postgres/db_home/bin/postgres -X -D /home/postgres/datanode/data 
-i -p 15432
1058      1224  1222  0 10:47 ?        00:00:00 postgres: writer process
1058      1225  1222  0 10:47 ?        00:00:00 postgres: wal writer process
1058      1226  1222  0 10:47 ?        00:00:00 postgres: autovacuum 
launcher process
1058      1227  1222  0 10:47 ?        00:00:00 postgres: stats 
collector process
1058      1234     1  0 10:48 pts/0    00:00:00 
/home/postgres/db_home/bin/postgres -C -D /home/postgres/coordinator/data -i
1058      1236  1234  0 10:48 ?        00:00:00 postgres: pooler process
1058      1237  1234  0 10:48 ?        00:00:00 postgres: writer process
1058      1238  1234  0 10:48 ?        00:00:00 postgres: wal writer process
1058      1239  1234  0 10:48 ?        00:00:00 postgres: autovacuum 
launcher process
1058      1240  1234  0 10:48 ?        00:00:00 postgres: stats 
collector process
1058      1401  1084  0 10:53 pts/0    00:00:00 grep --color=auto postgres


psql onto dbhost1 coord1 and ran 'create database TEST';
------------------------------------------------------

psql -U postgres -d postgres

Password for user postgresql:
psql (9.1.2)
Type "help" for help.

postgres=# create database TEST;
ERROR:  Failed to get pooled connections



coord1 log output:
-----------------

LOG:  database system was shut down at 2012-04-16 11:00:24 ADT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
LOG:  failed to connect to data node
WARNING:  can not connect to datanode 0
LOG:  failed to acquire connections
STATEMENT:  create database TEST;
ERROR:  Failed to get pooled connections
STATEMENT:  create database TEST;



psql output for pgxc_node (output is exactly same on both coord1 and 
coord2):
----------------------------------------------------------------------------

postgres=# select * from pgxc_node;
  node_name | node_type | node_port |   node_host    | nodeis_primary | 
nodeis_preferred
-----------+-----------+-----------+----------------+----------------+------------------
  coord2    | C         |      5432 | 192.168.38.100 | f              | f
  data2     | D         |     15432 | 192.168.38.100 | f              | f
  coord1    | C         |      5432 | 192.168.38.101 | f              | f
  data1     | D         |     15432 | 192.168.38.101 | f              | f
(4 rows)




On 4/13/12 10:54 PM, Michael Paquier wrote:
> Hi,
>
> I had a look at your settings. Please see my comments in-line. It 
> looks that your remote nodes are refusing connections as pooler cannot 
> get a connection.
>
>     - started the coordinator on each node without issue using the
>     folowing command:
>
>         pg_ctl start -D /home/postgres/coordinator/data -l
>     /home/postgresql/coordinator/log/coordinator1.log -Z coordinator 
>     ---> log is coordinator2 on 2nd db host
>
> When starting a nodes that are on separate servers, you also need to 
> use option "-i" or the node will only accept local connections.
> This option basically allows remote clients to connect with TCP-IP.
> So you startup option becomes:
> pg_ctl start -D /home/postgres/coordinator/data -l 
> /home/postgresql/coordinator/log/coordinator1.log -Z coordinator -o "-i"
>
>
>     - started the datanode on each node without issue using the
>     folowing command:
>
>         pg_ctl start -D /home/postgres/datanode/data -l
>     /home/postgresql/coordinator/log/datanode1.log -o "-p 15432" -Z
>     datanode  ---> log is datanode2 on 2nd db host
>
> Same here, you need to allow Datanode to accept remote TCP-IP connections.
> Even if you set up correctly pg_hba.conf, this is mandatory.
> pg_ctl start -D /home/postgres/datanode/data -l 
> /home/postgresql/coordinator/log/datanode1.log -o "-i -p 15432" -Z 
> datanode
>
> Please note that this is equivalent to setting listen_addresses to "*" 
> on postgresql.conf of each node.
> Regards,
> -- 
> Michael Paquier
> http://michael.otacoo.com