Menu
▾
▴
#76 Pooled ports through pgxc_ctl nort being set correctly
Version of PG XL used is...
/Releases/Version_9.5r1/postgres-xl-9.5r1.4.tar.gz
We were running a 2 node cluster on Amazon AWS (EC2 instances), but have run into some issues since we extended that by 2 nodes and now the whole cluster is unuseable and we are struggling to find answers in the documentation.
The errors we get, namely...
Invalid operation: Failed to get pooled connections
...pointed at problems with "pooled connections", which suggested a possible issue with pooled ports. On looking at the configuration we see that the pooled ports are not configured as we might have expected, even though we had manually entered specific and unique ports values, we see from the below that several of them are NULL and others have been configured differently from the values specified. Using the config utility we see...
Coordinator Master:
Nodename: 'coord1', port: 5432, pooler port: 15432
MaxWalSenders: 5, Dir: '/data/postgres/pgxc/nodes/coord'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: '(null)'
Datanode Master:
Nodename: 'datanode1', port: 20012, pooler port 15432
MaxWALSenders: 5, Dir: '/data/postgres/pgxc/nodes/dn_master'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: 'none'
====== Server: 10.0.22.66 =======
Coordinator Master:
Nodename: 'coord2', port: 5432, pooler port: 15432
MaxWalSenders: 5, Dir: '/data/postgres/pgxc/nodes/coord'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: '(null)'
Datanode Master:
Nodename: 'datanode2', port: 20012, pooler port 15432
MaxWALSenders: 5, Dir: '/data/postgres/pgxc/nodes/dn_master'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: 'none'
====== Server: 10.0.22.93 =======
Datanode Master:
Nodename: 'datanode3', port: 20013, pooler port (null)
MaxWALSenders: 5, Dir: '/data/postgres/pgxc/nodes/dn_master'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: 'none'
====== Server: 10.0.22.246 =======
Datanode Master:
Nodename: 'datanode4', port: 20014, pooler port (null)
MaxWALSenders: 5, Dir: '/data/postgres/pgxc/nodes/dn_master'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: 'none'
====== Server: 10.0.22.181 =======
Datanode Master:
Nodename: 'datanode5', port: 20015, pooler port (null)
MaxWALSenders: 5, Dir: '/data/postgres/pgxc/nodes/dn_master'
ExtraConfig: '(null)', Specific Extra Config: 'none'
pg_hba entries ( )
Extra pg_hba: '(null)', Specific Extra pg_hba: ‘none'
...but in the actual config file we see... #!/usr/bin/env bash
Postgres-XC Configuration file for pgxc_ctl utility.
Configuration file can be specified as -c option from pgxc_ctl command. Default is
$PGXC_CTL_HOME/pgxc_ctl.org.
This is bash script so you can make any addition for your convenience to configure
your Postgres-XC cluster.
Please understand that pgxc_ctl provides only a subset of configuration which pgxc_ctl
provide. Here's several several assumptions/restrictions pgxc_ctl depends on.
1) All the resources of pgxc nodes has to be owned by the same user. Same user means
user with the same user name. User ID may be different from server to server.
This must be specified as a variable $pgxcOwner.
2) All the servers must be reacheable via ssh without password. It is highly recommended
to setup key-based authentication among all the servers.
3) All the databases in coordinator/datanode has at least one same superuser. Pgxc_ctl
uses this user to connect to coordinators and datanodes. Again, no password should
be used to connect. You have many options to do this, pg_hba.conf, pg_ident.conf and
others. Pgxc_ctl provides a way to configure pg_hba.conf but not pg_ident.conf. This
will be implemented in the later releases.
4) Gtm master and slave can have different port to listen, while coordinator and datanode
slave should be assigned the same port number as master.
5) Port nuber of a coordinator slave must be the same as its master.
6) Master and slave are connected using synchronous replication. Asynchronous replication
have slight (almost none) chance to bring total cluster into inconsistent state.
This chance is very low and may be negligible. Support of asynchronous replication
may be supported in the later release.
7) Each coordinator and datanode can have only one slave each. Cascaded replication and
multiple slave are not supported in the current pgxc_ctl.
8) Killing nodes may end up with IPC resource leak, such as semafor and shared memory.
Only listening port (socket) will be cleaned with clean command.
9) Backup and restore are not supported in pgxc_ctl at present. This is a big task and
may need considerable resource.
========================================================================================
pgxcInstallDir variable is needed if you invoke "deploy" command from pgxc_ctl utility.
If don't you don't need this variable.
pgxcInstallDir=$HOME/pgxc
---- OVERALL -----------------------------------------------------------------------------
pgxcOwner=$USER # owner of the Postgres-XC databaseo cluster. Here, we use this
# both as linus user and database user. This must be
# the super user of each coordinator and datanode.
pgxcUser=$pgxcOwner # OS user of Postgres-XC owner
tmpDir=/tmp # temporary dir used in XC servers
localTmpDir=$tmpDir # temporary dir used here locally
configBackup=n # If you want config file backup, specify y to this value.
configBackupHost=pgxc-linker # host to backup config file
configBackupDir=$HOME/pgxc # Backup directory
configBackupFile=pgxc_ctl.bak # Backup file name --> Need to synchronize when original changed.
---- GTM ------------------------------------------------------------------------------------
GTM is mandatory. You must have at least (and only) one GTM master in your Postgres-XC cluster.
If GTM crashes and you need to reconfigure it, you can do it by pgxc_update_gtm command to update
GTM master with others. Of course, we provide pgxc_remove_gtm command to remove it. This command
will not stop the current GTM. It is up to the operator.
---- GTM Master -----------------------------------------------
---- Overall ----
gtmName=gtm
gtmMasterServer=10.0.22.163
gtmMasterPort=20001
gtmMasterDir=$HOME/pgxc/nodes/gtm
---- Configuration ---
gtmExtraConfig=none # Will be added gtm.conf for both Master and Slave (done at initilization only)
gtmMasterSpecificExtraConfig=none # Will be added to Master's gtm.conf (done at initialization only)
---- GTM Slave -----------------------------------------------
Because GTM is a key component to maintain database consistency, you may want to configure GTM slave
for backup.
---- Overall ------
gtmSlave=n # Specify y if you configure GTM Slave. Otherwise, GTM slave will not be configured an
# all the following variables will be reset.
gtmSlaveName=gtmSlave
gtmSlaveServer=node12 # value none means GTM slave is not available. Give none if you don't configure GTM Slave.
gtmSlavePort=20001 # Not used if you don't configure GTM slave.
gtmSlaveDir=$HOME/pgxc/nodes/gtm # Not used if you don't configure GTM slave.
Please note that when you have GTM failover, then there will be no slave available until you configure the slave
again. (pgxc_add_gtm_slave function will handle it)
---- Configuration ----
gtmSlaveSpecificExtraConfig=none # Will be added to Slave's gtm.conf (done at initialization only)
---- GTM Proxy -------------------------------------------------------------------------------------------------------
GTM proxy will be selected based upon which server each component runs on.
When fails over to the slave, the slave inherits its master's gtm proxy. It should be
reconfigured based upon the new location.
To do so, slave should be restarted. So pg_ctl promote -> (edit postgresql.conf and recovery.conf) -> pg_ctl restart
You don't have to configure GTM Proxy if you dont' configure GTM slave or you are happy if every component connects
to GTM Master directly. If you configure GTL slave, you must configure GTM proxy too.
---- Shortcuts ------
gtmProxyDir=$HOME/pgxc/nodes/gtm_pxy
---- Overall -------
gtmProxy=n # Specify y if you conifugre at least one GTM proxy. You may not configure gtm proxies
# only when you dont' configure GTM slaves.
# If you specify this value not to y, the following parameters will be set to default empty values.
# If we find there're no valid Proxy server names (means, every servers are specified
# as none), then gtmProxy value will be set to "n" and all the entries will be set to
# empty values.
gtmProxyNames=(gtm_pxy1 gtm_pxy2 gtm_pxy3 gtm_pxy4) # No used if it is not configured
gtmProxyServers=(node06 node07 node08 node09) # Specify none if you dont' configure it.
gtmProxyPorts=(20001 20001 20001 20001) # Not used if it is not configured.
gtmProxyDirs=($gtmProxyDir $gtmProxyDir $gtmProxyDir $gtmProxyDir) # Not used if it is not configured.
---- Configuration ----
gtmPxyExtraConfig=none # Extra configuration parameter for gtm_proxy. Coordinator section has an example.
gtmPxySpecificExtraConfig=(none none none none)
---- Coordinators ----------------------------------------------------------------------------------------------------
---- shortcuts ----------
---- Overall ------------
# the same connection
# This entry allows only $pgxcOwner to connect.
# If you'd like to setup another connection, you should
# supply these entries through files specified below.
Note: The above parameter is extracted as "host all all 0.0.0.0/0 trust". If you don't want
such setups, specify the value () to this variable and suplly what you want using coordExtraPgHba
and/or coordSpecificExtraPgHba variables.
coordPgHbaEntries=(::1/128) # Same as above but for IPv6 addresses
---- Master -------------
---- Slave -------------
coordSlave=n # Specify y if you configure at least one coordiantor slave. Otherwise, the following
# configuration parameters will be set to empty values.
# If no effective server names are found (that is, every servers are specified as none),
# then coordSlave value will be set to n and all the following values will be set to
# empty values.
coordSlaveSync=y # Specify to connect with synchronized mode.
coordSlaveServers=(10.0.22.66 10.0.22.59) # none means this slave is not available
coordSlavePorts=(20004 20005) # Master ports
coordSlavePoolerPorts=(20010 20011) # Master pooler ports
coordSlaveDirs=($coordSlaveDir $coordSlaveDir)
coordArchLogDirs=($coordArchLogDir $coordArchLogDir)
---- Configuration files---
Need these when you'd like setup specific non-default configuration
These files will go to corresponding files for the master.
You may supply your bash script to setup extra config lines and extra pg_hba.conf entries
Or you may supply these files manually.
================================================
Added to all the coordinator postgresql.conf
Original: $coordExtraConfig
Additional Configuration file for specific coordinator master.
You can define each setting by similar means as above.
----- Additional Slaves -----
Please note that this section is just a suggestion how we extend the configuration for
multiple and cascaded replication. They're not used in the current version.
coordAdditionalSlaves=n # Additional slave can be specified as follows: where you
coordAdditionalSlaveSet=(cad1) # Each specifies set of slaves. This case, two set of slaves are
# configured
cad1_Sync=n # All the slaves at "cad1" are connected with asynchronous mode.
# If not, specify "y"
# The following lines specifies detailed configuration for each
# slave tag, cad1. You can define cad2 similarly.
cad1_Servers=(node08 node09 node06 node07) # Hosts
cad1_dir=$HOME/pgxc/nodes/coord_slave_cad1
cad1_Dirs=($cad1_dir $cad1_dir $cad1_dir $cad1_dir)
cad1_ArchLogDir=$HOME/pgxc/nodes/coord_archlog_cad1
cad1_ArchLogDirs=($cad1_ArchLogDir $cad1_ArchLogDir $cad1_ArchLogDir $cad1_ArchLogDir)
---- Datanodes -------------------------------------------------------------------------------------------------------
---- Shortcuts --------------
datanodeMasterDir=/data/postgres/pgxc/nodes/dn_master
datanodeSlaveDir=/data/postgres/pgxc/nodes/dn_slave
datanodeArchLogDir=/data/postgres/pgxc/nodes/datanode_archlog
---- Overall ---------------
datanodePgHbaEntries=(10.0.0.0/16 192.168.0.0/16) # Assumes that all the coordinator (master/slave) accepts
datanodeMaxWalSender=5 # max_wal_senders: needed to configure slave. If zero value is
# specified, it is expected this parameter is explicitly supplied----------------------------------------------------------
walArchive=n # If you'd like to configure WAL archive, edit this section.
# Pgxc_ctl assumes that if you configure WAL archive, you configure it
# for all the coordinators and datanodes.
# Default is "no". Please specify "y" here to turn it on.
End of Configuration Section
==========================================================================================================================
========================================================================================================================
The following is for extension. Just demonstrate how to write such extension. There's no code
which takes care of them so please ignore the following lines. They are simply ignored by pgxc_ctl.
No side effects.
=============<< Beginning of future extension demonistration >> ========================================================
You can setup more than one backup set for various purposes, such as disaster recovery.
walArchiveSet=(war1 war2)
war1_source=(master) # you can specify master, slave or ano other additional slaves as a source of WAL archive.
# Default is the master
wal1_source=(slave)
wal1_source=(additiona_coordinator_slave_set additional_datanode_slave_set)
war1_host=node10 # All the nodes are backed up at the same host for a given archive set
war1_backupdir=$HOME/pgxc/backup_war1
wal2_source=(master)
war2_host=node11
war2_backupdir=$HOME/pgxc/backup_war2
=============<< End of future extension demonistration >> ========================================================
===================================================
===================================================
================================================================
================================================================
===================================================
pgxc configuration file updated due to datanode master addition
coordSlave=n
coordNames=( coord1 coord2 )
coordMasterDirs=( /data/postgres/pgxc/nodes/coord /data/postgres/pgxc/nodes/coord )
coordPorts=( 5432 5432 )
poolerPorts=( 15432 15432 )
coordMasterServers=( 10.0.22.59 10.0.22.66 )
coordMaxWALSenders=( 5 5 )
coordSlaveServers=( none none )
coordSlavePorts=( none none )
coordSlavePoolerPorts=( none none )
coordSlaveDirs=( none none )
coordArchLogDirs=( none none )
coordSpecificExtraConfig=( none none none none )
================================================================
pgxc configuration file updated due to datanode master removal
20170206_12:00:43
datanodeSlave=n
datanodeNames=( datanode1 datanode2 datanode3 datanode4 datanode5 )
datanodeMasterDirs=( /data/postgres/pgxc/nodes/dn_master /data/postgres/pgxc/nodes/dn_master /data/postgres/pgxc/nodes/dn_master /data/postgres/pgxc/nodes/dn_master /data/postgres/pgxc/nodes/dn_master )
datanodeMasterWALDirs=( none none none none none )
datanodePorts=( 20012 20012 20013 20014 20015 )
datanodePoolerPorts=( 20022 20022 20023 20024 20025 )
datanodeMasterServers=( 10.0.22.59 10.0.22.66 10.0.22.93 10.0.22.246 10.0.22.181 )
datanodeMaxWALSenders=( 5 5 5 5 5 )
datanodeSpecificExtraConfig=( none none none none none )
datanodeSpecificExtraPgHba=( none none none none none )
I got this error too. It comes down to having conflicting ports set or a firewall problem. In your config you have nodes set to 20012 and 20022. Even though they are differnt nodes it causes a conflict for some reason. When I changed them it started working.... well I got passed that error any way.
datanodePorts=( 20012 20012 20013 20014 20015 )
datanodePoolerPorts=( 20022 20022 20023 20024 20025 )