postgres-xc-general

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

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

Hello all ...

I am in the process of setting up and running postgres-XC 0.9.7 on 
Ubuntu 10.04 LTS 64bit in a development/test sandbox.

I used the install manual, 
http://iweb.dl.sourceforge.net/project/postgres-xc/Version_0.9.7/PG-XC_InstallManual_v0_9_7.pdf 
<http://iweb.dl.sourceforge.net/project/postgres-xc/Version_0.9.7/PG-XC_InstallManual_v0_9_7.pdf>, 
to set up one GTM VM host and two coordinator/datanode VM hosts as this 
is the recommended environment per the manual.

After I initialized the node information in each coordinator using the 
Create Node SQL, I am getting the following error trying to create a 
database on the coord1 node:

     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;

Was wondering if someone could suggest what is causing my connection 
issues.  I have searched through all the forums on sourceforge as well 
as the net and haven't been able to find anything useful yet.

Thanks in advance,
Phil.




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


Here are the details:


GTM host install:
----------------
as root:
- installed all necessary Ubuntu packages required for postgres-xc install
- created the postgres-xc user
- created a postgres-xc build directory
- downloaded pgxc_v0.9.7.tar.gz from sourgeforge.net 
<http://sourgeforge.net> and untarred it in build dir above
- config'ed postgres-xc build pointing to custom db home directory with 
NLS enabled and perl
- ran 'make' and 'make install' without issues
- created custom gtm data and log directories

as postgres-xc:
- copied gtm.conf.sample to gtm data dir
- edited the file as per the install manual:

     nodename = 'one'
     listen_addresses = '*'
     port = 6666
     log_file = '/home/postgres/gtm/log/gtm.log'

- started the GTM without issue using the following cmd:

     gtm_ctl start -S gtm -D /home/postgres/gtm/data -l 
/home/postgres/gtm/log/gtm.log


Coord/Datanode host install:
---------------------------

as root:
- installed all necessary Ubuntu packages required for postgres-xc install
- created the postgres-xc user
- created a postgres-xc build directory
- downloaded pgxc_v0.9.7.tar.gz from sourgeforge.net 
<http://sourgeforge.net> and untarred it in build dir above
- config'ed postgres-xc build pointing to custom db home directory with 
NLS enabled and perl
- ran 'make' and 'make install' without issues
- created custom coordinator and datanode data and log directories


as postgres-xc:
- ran initdb for cooordinator on each of the two db hosts
   - data directory was customized (ie not default)
   - nodename was coord1/coord2 respective of db_host_1/db_host_2

- ran initdb for datanode on each of the two db hosts
   - data directory was customized (ie not default)
   - nodename was data1/data2 respective of db_host_1/db_host_2

- edited coordinators postgres.conf and pg_hba.conf respective of the db 
host
   - postgresql.conf
     - pooler_port=6667            (just commented it out)
     - gmt_host=<IP address of GTM host>    (just commented it out)
     - gmt_post=6666                (just commented it out)
     - pgxc_node_name=coord1         (didn't have to change this)  ---> 
it was set to coord2 on the 2nd db host
     - port=5432                (just commented it out)
   - pg_hba.conf
     - added the following line to the end of the file:
         host    all    all <IP_subnet>.0/32    trust    ---> all hosts 
are on the same IP_subnet


- edited datanodes postgres.conf and pg_hba.conf respective of the db host
   - postgresql.conf
     - gmt_host=<IP address of GTM host>    (just commented it out)
     - gmt_post=6666                (just commented it out)
     - pgxc_node_name=data1             (didn't have to change this)  
---> it was set to data2 on the 2nd db host
     - port=15432                (edited it to change to 15432)
   - pg_hba.conf
     - added the following line to the end of the file:
         host    all    all <IP_subnet>.0/32    trust    ---> all hosts 
are on the same IP_subnet

- 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


- 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



Node inititialization on each coordinator:
-----------------------------------------

logged onto each coordinator using psql and ran the following cmds:

     psql -U postgres -d postgres

     CREATE NODE coord1 WITH (TYPE = 'coordinator', PORT = 5432, HOST = 
'<IP_of_coord1>');
     CREATE NODE coord2 WITH (TYPE = 'coordinator', PORT = 5432, HOST = 
'<IP_of_coord2>');

     CREATE NODE data1 WITH (TYPE = 'datanode', PORT = 15432, HOST = 
'<IP_of_data1>');
     CREATE NODE data2 WITH (TYPE = 'datanode', PORT = 15432, HOST = 
'<IP_of_data2>');

     SELECT pgxc_pool_reload();

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...>]

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

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

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

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,

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

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 Sat, Apr 21, 2012 at 3:19 AM, Phil Somers <phi...@ne...> wrote:

>  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.
>
OK. Good to know you found the origin of the problem.

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 for your feedback. It is appreciated too.
Don't forget that we released 1.0beta1, we have more features in so you
might try it.
Regards,
-- 
Michael Paquier
http://michael.otacoo.com

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

[Ashutosh B. <ash...@en...>]

>
>> 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.
>>
>
Can you please enlist the steps you took to load the databases? From where
did you dump the data? in which format? How did you load it (depends upon
format). I think, we can use this as a example case, for those who want to
move from PostgreSQL to Postgres-XC.


>
>> Postgres-XC is working great so far!!
>>
> Thanks for your feedback. It is appreciated too.
> Don't forget that we released 1.0beta1, we have more features in so you
> might try it.
> Regards,
> --
> Michael Paquier
> http://michael.otacoo.com
>
>
> ------------------------------------------------------------------------------
> For Developers, A Lot Can Happen In A Second.
> Boundary is the first to Know...and Tell You.
> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
> http://p.sf.net/sfu/Boundary-d2dvs2
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

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...>]

Ashutosh,

We have a standalone postgreSQL db host (v9.0.6) we are currently using 
for QA functional testing.  I used the databases on this host as the 
source db's.  I used pg_dump to dump each QA database to their 
respective SQL files.  I then copied these SQL files over to db_VM1 of 
the postgres-xc db host cluster.  I then used psql to load the SQL files 
into the test db's I had already created on db_VM1.  I then logged onto 
db_VM2 to verify the pg_dump SQLs had been applied to this db node and 
everything looked good.

However, I subsequently noticed that all the unique constraints on the 
QA db tables did not get applied to the postgres-xc db tables.  I am 
currently looking into fixing this issue.  This appears to be the only 
issue encountered so far since fixing the connection pooling issue.

Thanks,
Phil.

On 4/23/12 3:32 AM, Ashutosh Bapat wrote:
>
>
>         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.
>
>
> Can you please enlist the steps you took to load the databases? From 
> where did you dump the data? in which format? How did you load it 
> (depends upon format). I think, we can use this as a example case, for 
> those who want to move from PostgreSQL to Postgres-XC.
>
>
>         Postgres-XC is working great so far!!
>
>     Thanks for your feedback. It is appreciated too.
>     Don't forget that we released 1.0beta1, we have more features in
>     so you might try it.
>     Regards,
>     -- 
>     Michael Paquier
>     http://michael.otacoo.com
>
>     ------------------------------------------------------------------------------
>     For Developers, A Lot Can Happen In A Second.
>     Boundary is the first to Know...and Tell You.
>     Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
>     http://p.sf.net/sfu/Boundary-d2dvs2
>     _______________________________________________
>     Postgres-xc-general mailing list
>     Pos...@li...
>     <mailto:Pos...@li...>
>     https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>
>
>
> -- 
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>

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

[Ashutosh B. <ash...@en...>]

Thanks Phil.

On Wed, Apr 25, 2012 at 3:43 AM, Phil Somers <phi...@ne...> wrote:
> Ashutosh,
>
> We have a standalone postgreSQL db host (v9.0.6) we are currently using for
> QA functional testing.  I used the databases on this host as the source
> db's.  I used pg_dump to dump each QA database to their respective SQL
> files.  I then copied these SQL files over to db_VM1 of the postgres-xc db
> host cluster.  I then used psql to load the SQL files into the test db's I
> had already created on db_VM1.  I then logged onto db_VM2 to verify the
> pg_dump SQLs had been applied to this db node and everything looked good.
>
> However, I subsequently noticed that all the unique constraints on the QA db
> tables did not get applied to the postgres-xc db tables.  I am currently
> looking into fixing this issue.  This appears to be the only issue
> encountered so far since fixing the connection pooling issue.
>
> Thanks,
> Phil.
>
>
> On 4/23/12 3:32 AM, Ashutosh Bapat wrote:
>
>
>>>
>>> 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.
>
>
> Can you please enlist the steps you took to load the databases? From where
> did you dump the data? in which format? How did you load it (depends upon
> format). I think, we can use this as a example case, for those who want to
> move from PostgreSQL to Postgres-XC.
>
>>>
>>>
>>> Postgres-XC is working great so far!!
>>
>> Thanks for your feedback. It is appreciated too.
>> Don't forget that we released 1.0beta1, we have more features in so you
>> might try it.
>> Regards,
>> --
>> Michael Paquier
>> http://michael.otacoo.com
>>
>>
>> ------------------------------------------------------------------------------
>> For Developers, A Lot Can Happen In A Second.
>> Boundary is the first to Know...and Tell You.
>> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
>> http://p.sf.net/sfu/Boundary-d2dvs2
>> _______________________________________________
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>



-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company