postgresql - psql : FATAL: Could not obtain a transaction ID from GTM. GTM 可能失败或失去连接

Question

我想创建一个postgres-xl 集群。该集群包括 5 个节点、1 个 GTM、2 个 Coordinator 和 2 个 Datanode。以下是节点的详细信息

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

我已经安装了 pgxc_ctl 并将 /usr/local/pgsql/bin 添加到 postgres 的 PATH。我已经配置了 ssh 身份验证以避免输入 pgxc_ctl 的密码。我已经在两个节点上编辑了 postgresql.conf 和 pg_hba.conf。

然后我搭建集群如下:

$ 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

添加coord2后，得到如下结果

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 

添加dn2后出现如下错误

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

但是当我监控所有节点时，它显示

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

我无法通过运行连接到 coord2

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

显示

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

但我可以通过运行连接到 coord1

psql  -p 30001 -U user -d postgres 

我可以在没有密码的情况下从本地主机 ping 主机 2。我需要解决上述错误。有什么帮助吗？添加配置:

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

你能告诉我们你的配置吗？

max_connections 和 max_pool_size 是多少？ initdb 为您的内核显示了什么？我的猜测是，当您添加 datanode2 (dn2) 时，您没有足够的连接数。

你有:

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

Postgres-xl 特定的:max_pool_size=300max_coordinators=2max_datanodes=2

如果是协调器(最小设置):max_connections=100#从应用程序接受的连接数max_prepared_transactions = 100 # 与连接数相同

如果是Datanode(最小设置):max_connections=200 # 2 个协调器max_prepared_transactions=2 #指定至少集群中协调器的总数。

摘自 Postgres(-xl) 文档

max_connections(整数)

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(整数)

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.

编辑 - 用于更新问题配置

试试这个:

• 协调员

  max_connections=100
  max_pool_size=300
  

• Datanode(你定义了 2 个 datanode)

  max_connections=200 
  max_pool_size=500