Menu
▾
▴
Re: [Postgres-xc-general] postgres-xc 0.9.7 : failed to connect to data node/can not connect to datanode 0
|
From: Phil S. <phi...@ne...> - 2012-04-20 18:20:02
|
Michel,
Just a quick note to let you know I resolved the connection pool issue.
It wasn't a permissions or firewall issue as no firewalls are running
between any of the three VMs.
I turned up the logging on both coord/datanode VMs as per your
suggestion but nothing of any significance was noted.
In attempts to troubleshoot the issue, I then updated the node
initialization removing the remote nodes on each VM. Once I did this,
the CREATE DATABASE sql would run clean but only get created on the
local node, which made sense, seeing only the local datanode was
configured in the pgxc_node catalog.
Seeing it was probably network related, one of my SA co-workers took a
thorough look at the network configs on each VM and found that one of
the VMs had an additional NIC configured called virbr0. This apparently
occured when VMTools was installed on this host (another SA was testing
out the VMTool suite a while back and used this VM to run his tests).
I disabled and removed the virbr0 NIC. I then had to edit the
pg_hba.conf hosts on coord1, data1, coord2 and data2 as follows:
host all all 192.168.38.0/24 trust
I had originally added it as 'host all all 192.168.38.0*/32* trust' but
the /32 was causing issues where postgres-xc couldn't find the hosts
properly.
Once I did all this and updated the node initialization to include the
remote hosts on each VM, the CREATE DATABASE SQL ran clean.
I have subsequently created 3 test db's, numerous login roles, numerous
group roles and loaded pg_dump backups into each test db on one of the
db VMs. The second db VM was then accessed and all db objects that were
created on the remote db VM had successfully been created on the db VM I
was accessing. As well, all the data on the remote db VM had
successfully been loaded into the db VM I was accessing.
Postgres-XC is working great so far!!
Thanks very much for taking the time to respond to and halp with this
issue. It was greatly appreciated.
Phil.
On 4/16/12 7:24 PM, Michael Paquier wrote:
>
>
> On Mon, Apr 16, 2012 at 11:34 PM, Phil Somers <phi...@ne...
> <mailto: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
|
