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

[Michael P. <mic...@gm...>]

On Mon, Apr 16, 2012 at 11:34 PM, Phil Somers <phi...@ne...>wrote:

>  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)
>
Just by looking at that, I can't really get the issue you have, but I am
pretty sure it is a permission problem.
Could it be a firewall issue? You may try to shut it down on node 2 once
and see what happens... Are you able to create a database correctly?
If this doesn't work, you should turn on log_connections on postgresql.conf
of each node and have a look at the logs. It will for sure help to spot
your problem.
-- 
Michael Paquier
http://michael.otacoo.com