psql: FATAL: Could not obtain a transaction ID from GTM. The GTM might have failed or lost connectivity

Question

I want to create a postgres-xl cluster. The cluster includes 5 nodes, 1 GTM, 2 Coordinator and 2 Datanodes. The following are the details of nodes

GTM:   
hostname=localhost  
nodename=gtm  
IP=127.0.0.1  
port=20001

Coordinator1：  
hostname=localhost  
nodename=coord1  
IP=127.0.0.1  
pooler_port=30011，port=30001  

Coordinator2：  
hostname=host2  
nodename=coord2  
IP=10.4.6.36  
pooler_port=30012，port=30002  

Datanode1：  
hostname=localhost  
nodename=dn1  
IP=127.0.0.1  
pooler_port=40011, port=40001  

Datanode2：  
hostname=host2  
nodename=dn2  
IP=10.4.6.36  
pooler_port=40012, port=40002

I have installed pgxc_ctl and added /usr/local/pgsql/bin to PATH for postgres. I have Configured ssh authentication to avoid inputting the password for pgxc_ctl. I have edited postgresql.conf and pg_hba.conf on both nodes.

Then I built the cluster as follows:

$ pgxc_ctl
PGXC$  add gtm master gtm localhost 20001 $dataDirRoot/gtm    
PGXC$  add coordinator master coord1 localhost 30001 30011 
       $dataDirRoot/coord_master.1 none none
PGXC$  add coordinator master coord2 10.4.6.36 30002 30012 
       $dataDirRoot/coord_master.2 none none

after adding coord2, i got the following

psql: FATAL: Could not obtain a transaction ID from GTM. The GTM might have failed or lost connectivity

PGXC$  add datanode master dn1 localhost 40001 40011 
       $dataDirRoot/dn_master.1 none none none
PGXC$  add datanode master dn2 10.4.6.36 40002 40012 
       $dataDirRoot/dn_master.2 none none none 

after adding dn2, I got the following error

ERROR: Failed to get pooled connections HINT: This may happen because one or more nodes are currently unreachable, either because of node or network failure. It's also possible that the target node may have hit the connection limit or the pooler is configured with low connections. Please check if all nodes are running fine and also review max_connections and max_pool_size configuration parameters

But when I monitor all the nodes, it shows

PGXC$  monitor all
Running: gtm master
Running: coordinator master coord1
Running: coordinator master coord2
Running: datanode master dn1
Running: datanode master dn2 

I could not connect to coord2 by running

 psql -h 10.4.6.36 -p 30002 -U user -d postgres

It shows

psql: FATAL: Could not obtain a transaction ID from GTM. The GTM might have failed or lost connectivity

But I could connect to the coord1 by running

psql  -p 30001 -U user -d postgres 

I could ping host2 from my localhost without the password. I need to resolve the above errors. Any help? Adding the configuraion:

pgxcInstallDir=$HOME/pgxc
pgxcOwner=$USER     
pgxcUser=$pgxcOwner     
tmpDir=/tmp                 
localTmpDir=$tmpDir         
configBackup=n                  
configBackupHost=pgxc-linker    
configBackupDir=$HOME/pgxc      
configBackupFile=pgxc_ctl.bak   
dataDirRoot=$HOME/DATA/pgxl/nodes

#---- Coordinators ----------------------------------------------------------------------------------------------------

coordMasterDir=$dataDirRoot/coord_master
coordSlaveDir=$HOME/coord_slave
coordArchLogDir=$HOME/coord_archlog
coordExtraConfig=coordExtraConfig   
cat > $coordExtraConfig <<EOF
#================================================
# Added to all the coordinator postgresql.conf
# Original: $coordExtraConfig
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_pool_size=300
max_connections=200
hot_standby = off
EOF

#---- Datanodes -------------------------------------------------------------------------------------------------------

datanodeMasterDir=$dataDirRoot/dn_master
datanodeSlaveDir=$dataDirRoot/dn_slave
datanodeArchLogDir=$dataDirRoot/datanode_archlog
datanodeExtraConfig=datanodeExtraConfig 
cat > $datanodeExtraConfig <<EOF
#================================================
# Added to all the datanode postgresql.conf
# Original: $datanodeExtraConfig
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
listen_addresses = '*'
max_pool_size=300
max_connections=200
hot_standby = off
EOF
#---- GTM ------------------------------------------------------------------------------------      
gtmName=gtm
gtmMasterServer=localhost
gtmMasterPort=20001
gtmMasterDir=$dataDirRoot/gtm


coordNames=( coord1 coord2  )
coordMasterServers=( localhost 10.4.6.36  )
coordPorts=( 30001 30002  )
poolerPorts=( 30011 30012  )
coordMasterDirs=( $dataDirRoot/coord_master.1 $dataDirRoot/coord_master.2  )
coordMaxWALSenders=( 5 5  )
coordSlave=n
coordSlaveServers=( none none  )
coordSlavePorts=( none none  )
coordSlavePoolerPorts=( none none  )
coordSlaveDirs=( none none  )
coordArchLogDirs=( none none  )
coordSpecificExtraConfig=( coordExtraConfig coordExtraConfig  )
coordSpecificExtraPgHba=( none none  )


datanodeNames=( dn1 dn2  )
datanodeMasterServers=( localhost 10.4.6.36  )
datanodePorts=( 40001 40002  )
datanodePoolerPorts=( 40011 40012  )
datanodeMasterDirs=( $dataDirRoot/dn_master.1 $dataDirRoot/dn_master.2  )
datanodeMasterWALDirs=( none none  )
datanodeMaxWALSenders=( 5 5  )
datanodeSpecificExtraConfig=( datanodeExtraConfig datanodeExtraConfig  )
datanodeSpecificExtraPgHba=( none none  )

Answer 1

Could you show us your configuration?

What are your max_connections and max_pool_size? What did the initdb show for your kernel? My guess is that when you add the datanode2 (dn2) you don't have enough connections.

You have:

cluster includes 5 nodes, 1 GTM, 2 Coordinator and 2 Datanodes. The following are the details of nodes.

Postgres-xl specific: max_pool_size=300 max_coordinators=2 max_datanodes=2

In case of Coordinator (minimal settings): max_connections=100# number of connections accepted from application(s) max_prepared_transactions = 100 # same as number of connections

In case of Datanode (minimal settings): max_connections=200 # 2 coordinators max_prepared_transactions=2 #Specify at least total number of Coordinators in the cluster.

Excerpt from the Postgres(-xl) documentation

max_connections (integer)

Determines the maximum number of concurrent connections to the database server. The default is typically 100 connections, but might be less if your kernel settings will not support it (as determined during initdb). This parameter can only be set at server start.

When running a standby server, you must set this parameter to the same or higher value than on the master server. Otherwise, queries will not be allowed in the standby server.

In the case of the Coordinator, this parameter determines how many connections can each Coordinator accept.

In the case of the Datanode, number of connection to each Datanode may become as large as max_connections multiplied by the number of Coordinators.

max_pool_size (integer)

Specify the maximum connection pool of the Coordinator to Datanodes. Because each transaction can be involved by all the Datanodes, this parameter should at least be max_connections multiplied by number of Datanodes.

Edit - for update question configuration

Try this:

• Coordinator

  max_connections=100
  max_pool_size=300
  

• Datanode (you have 2 datanodes defined)

  max_connections=200 
  max_pool_size=500