F.32. pgxc_ctl

F.32.1. Description

Note: The following description applies only to Postgres-XC

pgxc_ctl takes care of configuration, initialization, start, stop, monitor and failover of postgres-xc components. Manual configuration of postgres-xc cluster is a great and painful work. You should plan your postgres-xc configuration carefully. This document describes how to use pgxc_ctl to help these tiring work.

F.32.2. Building and installing pgxc_ctl

You should build pgxc_ctl using your Postgres-XC build environment. pgxc_ctl source code comes with postgres-xc source code tarball. Latest version of the source code will be available at its home repository,

https://github.com/koichi-szk/PGXC-Tools/tree/master/pgxc_ctl

If you would like to use the latest version from pgxc_ctl home repository, get the set of the source code tarball and expand it at contrib directory of your Postgres-XC build environment. If you are using pgxc_ctl in postgres-xc tarball, you don't have to do this.

Before building pgxc_ctl, you should build postgres-xc binary, like

$ cd your postgres-xc build directory
$ ./configure your configuration option
$ make

Please note that you dont'have to install postgres-xc binary to build pgxc_ctl. Also please note that postgres-xc top level make and make install command does not take care of pgxc_ctl. You should build and install the separately.

Then you can build pgxc_ctl as follows:

$ cd contrib/pgxc_ctl
$ make
$ make install

pgxc_ctl binary will be installed at the same directory as postgres-xc binaries.

Postgres-XC consists of many components (or called "nodes") running in various physical or virtual machines. Because pgxc_ctl relies on ssh connection between the machines where pgxc_ctl and other nodes are running, you should setup ssh-agent authentication to avoid typing password each time pgcx_ctl issues ssh.

F.32.3. pgxc_ctl home directory

pgxc_ctl uses its own work directory, where it stores configuration files and logs, as well as other resources. Default value is $HOME/pgxc_ctl and you can specify this by --home option.

pgxc_ctl home directory may be referred to as $PGXC_CTL_HOME through this manual.

You do not have to create this directory manually. Pgxc_ctl will create the directory when it is invoked for the first time. Please note that you need appropriate privilege to create $PGXC_CTL_HOME. YOu can create this manually, of course. For details, please refer to later sections.

F.32.4. pgxc_ctl configuration file

pgxc_ctl uses configuration file. The default name and the location is $PGXC_CTL_HOME/pgxc_ctl.conf. When you change postgres-xc cluster configuration using pgxc_ctl commands, this file will bel updated. Depending upon your configuration, pgxc_ctl will backs up this file to the other file according to your configuration.

It could be painful to write the configuration file from the scratch. pgxc_ctl provides prepare command to setup the prototype of this file. For details, please refer to command syntax of pgxc_ctl.

F.32.5. pgxc_ctl initialization file

You can specify your preferred parameters of pgxc_ctl behavior. You can specify parameters in /etc/pgxc_ctl and/or $HOME/.pgxc_ctl file. Setups in $HOME/.pgxc_ctl have higher priority so you can specify system-wide setups at /etc/pgxc_ctl and then your personal preferences in $HOME/.pgxc_ctl.

Format of this file will be given in the later section.

F.32.6. Running pgxc_ctl for the first time

Unless you build $PGXC_CTL_HOME and configuration file from the scratch, you should run pgxc_ctl to build your $PGXC_CTL_HOME and get a prototype of configuration file. From your bash prompt, simply type pgxc_ctl. You will have the following prompt:

$ pgxc_ctl
PGXC$ 

You will get the default prompt, which you can modify at any time through initialization files.

Try to type pwd. You will get what is your $PGXC_CTL_HOME is.

$ pgxc_ctl
PGXC$ pwd
/home/koichi/pgxc_ctl
PGXC$

If you specify --home option with another directory, pgxc_ctl will start at this directory, after building it if needed.

$ pgxc_ctl --home /home/koichi/my_pgxc_ctl
PGXC$ pwd
/home/koichi/my_pgxc_ctl
PGXC$

You can specify your pgxc_ctl_home as environment variable PGXC_CTL_HOME, or you can specify this as variable pgxc_ctl_home in your initialization files.

Command line option has the highest priority, then the environment, $HOME/.pgxc_ctl and /etc/pgxc_ctl.

Type prepare or prepare config to get a configuration template file pgxc_ctl.conf at $PGXC_CTL_HOME. You may add file name as an option to get configuration template in your favorite file. For example:

PGXC$ prepare
PGXC$

or

PGXC$ prepare config my_pgxc.conf
PGXC$

Detailed syntax of the command will be given in later section.

F.32.7. Make your configuration

Please take a look at the template of the configuration file you created as in the previous section. This file is actually a bash script file to setup various bash script variables which are passed to pgxc_ctl next time you run it.

Postgres-xc configuration needs to specify same or similar values to each node configuration, for example, work directory, port, etc. To avoid trivial error, you can specify such same value as your favorite variables and refer to them in each variable setups.

For example, a part of your template may look like this:

#---- Shortcuts ------
gtmProxyDir=$HOME/pgxc/nodes/gtm_pxy

#---- Overall -------
gtmProxy=y              # Specify y if you configure 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.

This is a section to specify gtm proxy setups. We have four gtm proxies in each of the server. They share working directory path and is specified as a shortcut which is referred to later.

You can do all these in any part of the configuration file.

Please note that the working directory of this script is $PGXC_CTL_HOME, unless you change it explicitly in this configuration file.

F.32.8. pgxc_ctl invocation options

When you invoke pgxc_cmd from your shell, pgxc_ctl accepts several options to control its behavior. pgxc_ctl command format is as follows:

pgxc [options ... ] [pgxc_command]

Options are as follows:

-c configuration_file
--configuration configuration_file

Specifies configuration file. The default is pgxc_ctl_conf, or the value of configFile option found in the initialization file.

--home home_directory

Specifies $PGXC_CTL_HOME directory. You can specify this as pgxc_ctl_home variable in the initialization file.

-i file
--infile file

Specifies where to read pgxc_ctl commands. There's not corresponding variables in the initialization file. Default is the standard input.

-L logfile
--logfile logfile

Specifies where to write the log. The path is relative to $PGXC_CTL_HOME or the value of log directory specified as -l option or logDir variable in the initialization file, if specified.

-l logdir
--logdir logdir

Specifies the directory of the log file. Default is $PGXC_HOME/pgxc_log/.

-o outfile
--out-file outfile

Specifies where to write pgxc_ctl output. Default is the standard output.

--silent

Specifies to run pgxc_ctl without printing messages as much as possible. This value can also be set as variable verbose in the initialization file. You can setup level of messages logMessage and printMessage variables in the initialization file as well.

-V
--version

Prints pgxc_ctl version and exits.

-v
--verbose

Specifies to run pgxc_ctl to print as many messages as possible.

F.32.9. pgxc_ctl initialization file

As described in previous sections, pgxc_ctl behavior, as specified in the command line options, can be specified in advance in the initialization file /etc/pgxc_ctl or $HOME/.pgxc_ctl.

The syntax is as follows:

name value [ value ... ] # comment

Blank lines or lines beginning with '#' are simply ignored. If you'd like to include space or tab in the variable name, enclose the name with '...' or "...".

Please note that this file is not a bash script.

List of the name and their value is as follows:

configFile filename

Specify the configuration file name. Default is pgxc_ctl.conf. This option can be overridden by -c command line option.

localTmpDir dirname

Specify remote temporary directory, default is /tmp.

logDir dirname

Specifies the directory to write log. Can be overridden by -l command line option.

logFile filename

Specifies the log file name, which is relative to $PGXC_CTL_HOME/pgxc_log or value of logDir variable. Can be overridden by -L, command line option.

logMessage msglevel

Specifies the message level to print to the terminal or output file. Valid value is MANDATORY, PANIC, ERROR, WARNING, NOTICE, NOTICE2, INFO, DEBUG1, DEBUG2, or DEBUG3. Default is NOTICE.

pgxc_ctl_home dirname

Specifies $PGXC_CTL_HOME. Default is $HOME/pgxc_ctl or environment variable $PGXC_CTL_HOME. Can be overriden by --home command line option.

printMessage msglevel

Specifies the message level to print to the log file. Valid value is MANDATORY, PANIC, ERROR, WARNING, NOTICE, NOTICE2, INFO, DEBUG1, DEBUG2, or DEBUG3. Default is NOTICE.

tmpDir dirname

Specify local temporary directory, default is /tmp.

verbose value

Specifies verbose message output from pgxc_ctl. Value should be y or n. Can be overridedn by -v or --silent command line option.

xc_prompt prompt

Specifies pgxc_prompt. Default is 'PGXC$ '.

Typical example of this initialization file will be as follows:

$ cat ~/.pgxc_ctl
xc_prompt 'PGXC$ '
verbose y
logMessage 'DEBUG3'
printMessage 'DEBUG1'
printLocation y
$

F.32.10. Postgres-XC basics and its resources

F.32.10.1. Postgres-XC components

Postgres-xc consists of the following components. Each component may be called node, which may not necessarily refer to physical or virtual server because you can configure more than one node to one physical/virtual server. You should consider how many of them to configure. Hereafter, we call such physical/virtual server as host.

GTM

GTM stands for global transaction manager. You must have one in the cluster. For production, GTM should be configured in a separate server. GTM can have a slave which can fail over when GTM fails. GTM slave can be installed (hopefully) in a separate server but can be installed in one of the others where you have gtm_proxy, coordinators and datanodes.

GTM-Proxy

GTM proxy reduces the communication load between coordinator and GTM and helps GTM failover. You should configure one gtm_proxy in each server where you have coordinator or datanode as described below.

Coordinator

Coordinator handles application connection and statement handling. For simplicity and load balancing, it is a good idea to install coordinator to each server other than where GTM (and GTM slave) are configured. Coordinator can have a slave. Slave can be configured in one of the servers where other coordinator master is installed.

Datanode

Datanode stores the data and run local SQL statement supplied by a coordinator. Datanode should also be configured in all the servers except those for GTM (and GTM slave).

F.32.10.2. Common resource assignment and configuration practice

Each component requires the following resources:

  • hostname, IP address or host name you can refer through DNS, /etc/hosts or by equivalent means.

  • port

  • work directory

Also, coordinator needs additional port for connection pooling to other nodes.

In the same host, you must not assign the same port and the same work directory to nodes. pgxc_ctl checks this.

When assign the port, you should be careful not to assign already assigned one to other service.

Also, please note the following:

  • You should not assign the same port to GTM master and GTM slave.

  • Coordinator master and coordinator slave must share the same port, at different hosts.

  • Datanode master and datanode slave must share the same port, at different hosts.

Please note that the last two are not required by postgres-xc but requirement of pgxc_ctl.

GTM, coordinators and datanodes can configure their slave. Pgxc_ctl does not support cascaded slave or more than one slave for coordinator and datanode. It is not a restriction of postgres-XC, it is a restriction of pgxc_ctl.

At present, coordinator and datanode slaves are connected using synchronous replication in pgxc_ctl. This is not a Posgres-XC restriction either. In the future, asynchronous, cascaded and multiple slaves may be supported.

F.32.11. Configuration

As described in the previous section, you can configure your postgres-xc cluster by editing pgxc_ctl.conf or other configuration files manually. But editing the file from the scratch can be a mess. It is much better to have separate configuration file. You can create configuration file template by typing

PGXC$ prepare config
PGXC$ 

You have your pgxc_ctl.conf file at $HOME/pgxc_ctl. You can edit it to configure your postgres-xc cluster. When it messes up, you can again create the template with prepare config command. If you want to use other file name, specify the names prepare config command option like:

PGXC$ prepare config my_config.conf

Then you can edit this file to configure you postgres-XC cluster. This file is actually a bash script file defining many variables to define the cluster configuration. With template values and comments, it will be easy to understand what they mean. The following describes each variable in the order you find in the configuration template.

F.32.11.1. Overall

configBackup

Option if you backup the configuration file to a remote server. Specify y if you'd like to backup the configuration file. n otherwise.

configBackupFile

Name of the configuration backup file. Effective when configuration file backup is enabled.

configBackupHost

Host name (or IP address) where you backup the configuration file. Effective when configuration file backup is enabled.

localTmpDir

Local directory used by pgxc_ctl itself. You need full access to this directory.

This parameter was left here to make it compatible with bash-version. It is recommended to configure this parameter in initialization file.

pgxcInstallDir

Postgres-XC should at least be installed in the server you are running pgxc_ctl. This variable specifies this installation directory, as you specify with --prefix= option of configure command when you build it. All the installation will be copied to the same directory at each servers and you should give appropriate privilege to this directory in advance.

pgxcOwner

Name of the database user who owns whole Postgres-XC database. This can be different from $pgxcUser. In the present version, we assume these two should be the same though.

pgxcUser

Name of the operating system user you are logging in as postgres-xc owner. At present, this should be the same as $pgxcOwner.

tmpDir

Directory used for work at each server except for the one pgxc_ctl runs. You need full access to this directory at all the servers.

This parameter was left here to make it compatible with bash-version. It is recommended to configure this parameter in initialization file.

F.32.11.2. GTM

gtmName

Node name of GTM.

gtmExtraConfig

If you'd like to add specific configuration to both GTM master and slave, specify the file which contains such lines for gtm.config file. Otherwise, specify none.

gtmMasterDir

Work directory for GTM master.

gtmMasterPort

Listening port number of GTM master.

gtmMasterServer

Host name where GTM master runs.

gtmMasterSpecificExtraConfig

If you'd like to add specific configuration only to GTM master, specify the file which contains such lines for gtm.config file. Otherwise, specify none

gtmSlave

Option to enable GTM slave. Specify y to enable, n otherwise.

gtmSlaveDir

Work directory for GTM slave.

gtmSlavePort

Listening port number of GTM slave.

gtmSlaveServer

Host name where GTM slave runs. Effective only when GTM slave is effective.

gtmSlaveSpecificExtraConfig

If you'd like to add specific configuration only to GTM slave, specify the file which contains such lines for gtm.config file. Otherwise, specify none.

F.32.11.3. GTM Proxy

gtmProxy

This specifies if you configure any GTM proxy in your postgres-xc cluster. Specify the value y if you configure gtm proxy in your postgres-xc cluster. Otherwise specify n. If you specify n, all the other parameters for gtm_proxy will be ignored.

gtmProxyDir

This is a shortcut used to assign same work directory to all the GTM proxies. You don't have to worry about it when you specify these values manually.

gtmProxyDirs

Specify work directory for each GTM proxy.

gtmPxyExtraConfig

If you'd like to add configuration value to all the GTM proxy, specify the file name which contains such lines for gtm_proxy.conf. Otherwise specify none.

gtmProxyNames

Specify unique name for each GTM proxy. This is an array. In the template, we have four servers for coordinator and datanode and we have four gtm proxy as well.

gtmProxyPorts

Specify listening port number for each GTM proxy.

gtmProxyServers

Specify host name where each of the GTM Proxy runs. Specify server name as the same order as $gtmProxyNames.

gtmPxySpecificExtraConfig

If you'd like to add specific configuration value to each GTM proxy, specify file names with such lines for gtm_proxy.conf. Otherwise specify none.

F.32.11.4. Coordinators

coordArchLogDir

Shortcut to assign the same WAL archive directory to all the coordinator slaves. Not needed if you specify these manually.

coordArchLogDirs

Array of WAL archive log directory for each datanode slave. If you don't configure coordinator slaves and specify coordSlave variable value to n, you don't have to worry about this variable.

coordExtraConfig

If you would like to add extra configuration value for all the coordinators, specify the file name containing such lines for postgresql.conf. Specify none otherwise.

coordExtraPgHba

File name which contains entries of pg_hba.conf file for all the coordinators. Specify none if you do not have such file.

coordMasterDir

Shortcut to assign the same work directory to all the coordinator masters. Not needed if you specify these manually.

coordMasterDirs

Array of coordinator master work directory.

coordMasterServers

Array of the host name where each coordinator master runs. Specify in the order of coordNames above.

coordMaxWalSender

Shortcut to assign the same value to each member of coordMaxWalSenders. Not needed if you assign the value manually.

coordMaxWalSenders

Array of coordinator max_wal_senders value. Note that a master and the slave shares the same value of this variable.

coordNames

Array to specify coordinator names. Coordinator slave uses the same name as the master.

coordPgHbaEntries

Array of CIDR addresses to be added to pg_hba.conf. Will create pg_hba.conf file entry with pgxcOwner user.

coordPorts

Array of the listening port number for each coordinator. Please note that pgxc_ctl supposed that a master and its slave uses the same port number.

coordSlave

Specify y if you configure coordinator slave. n otherwise. If you specify n, then all the other variables for coordinator slave will be ignored.

coordSlaveDir

Shortcut to assign the same work directory to all the coordinator slaves. Not needed if you specify these manually.

coordSlaveDirs

Array of work directory for each coordinator slaves.

coordSlaveServers

Array of the hostname where slave of each coordinator runs. Specify none if you don't configure the slave for specific coordinator.

coordSpecificExtraConfig

Array of the filename which contains extra configuration values for each coordinator. Specify none if you don't have such file.

poolerPorts

Array of the port number for each pooler. Pooler takes care of the connection between coordinator and datanode and needs separate port. Please note that a master and its slave uses the same pooler port number.

F.32.11.5. Datanodes

datanodeArchLogDir

Shortcut to assign the same WAL archive directory to all the datanode slaves. Not needed if you specify these manually.

datanodeArchLogDirs

Array of WAL archive log directory for each datanode slave.

datanodeExtraConfig

If you would like to add extra configuration value for all the datanodes, specify the file name containing such lines for postgresql.conf. Specify none otherwise.

datanodeExtraPgHba

File name which contains entries for all the datanodes' pg_hba.conf file. Specify none if you don't have such file.

datanodeMasterDir

Shortcut to assign the same work directory to all the datanode masters. Not needed if you specify these manually.

datanodeMasterDirs

Array of datanode master work directory.

datanodeMaterServers

Array of the host name where each datanode master runs. Specify in the order of $coordNames above.

datanodeMaxWalSender

shortcut to assign the same value to each member of datanodeMaxWalSenders. Not needed if you assign the value manually.

datanodeMaxWalSenders

Array of datanode max_wal_senders value.

datanodeNames

Array to specify coordinator names.

datanodePgHbaEntries

Array of CIDR addresses to be added to pg_hba.conf. Will create pg_hba.conf file entry with $pgxcOwner user.

datanodePorts

Array of the listening port number for each datanode (master and slave use the same port).

datanodeSlave

Specify y if you configure datanode slaves. n otherwise. If you specify n, all the other variables for datanode slaves will be ignored.

datanodeSlaveDir

Shortcut to assign the same work directory to all the datanode slaves. Not needed if you specify these manually.

datanodeSlaveDirs

Array of work directory for each datanode slave.

datanodeSlaveServers

Array of the hostname where slave of each datanode runs. Specify none if you don't configure the slave for specific coordinator.

datanodeSpecificExtraConfig

Array of the filename which contains extra configuration values for each datanode. Specify none if you don't have such file.

datanodeSpecificExtraPgHba

Array of file names which contain specific extra pg_hba.conf entry for each datanode. Specify none if you don't have such file.

primaryDataode

Specify name of the primary node. This must be one of the name in $datanodeNames. If you don't want the primary node, specify N/A or none.

F.32.12. pgxc_ctl commands

pgxc_ctl command names and literal options are case-insensitive. Other options are case-sensitive.

If other command is given, it will be passed to your shell. When the shell stops, then the control returns to pgxc_ctl.

add gtm slave name host port dir
add gtm_proxy name host port dir
add coordinator master name host port pooler dir
add coordinator slave name host dir archDir
add datanode master name host port dir
add datanode slave name host dir archDir

Add the specified node to your postgres-xc cluster. Each node need host name and its work directory. gtm slave, gtm_proxy, coordinator master and datanode master need its own port to listen to. Coordinator needs its pooler port to pool connections to datanodes. Coordinator and datanode slave need a directory to receive WAL segments from their master.

When you add coordinator and datanode master, node information at all the coordinators will be updated with the new one and gtm_proxy will be selected automatically based upon where the new node runs.

You cannot add slaves without master.

Createdb [ - coordinator ] createdb_option ...

Invokes createdb utility to create a new datanode using specified coordinator. If no coordinator is specified, pgxc_ctl chooses one of the available ones.

Createuser[ - coordinator ] createuser_option ...

Invokes createuser utility to create a new user using specified coordinator. Of coordinator is not specified, pgxc_ctl chooses one of the available ones.

deploy [ all | host ... ]

Deploys postgres-xc binaries and other installation material to specified hosts. If "all" is specified, they will be deployed to all the hosts found in the configuration file. If list of the host is specifies, deployment will be done to all the specified hosts, regardless if they are found in the configuration file or not. Target directory is taken from the variable pgxcInstallDir.

failover [ gtm | coordinator nodename | datanode nodename | nodename ]

Failover specified node to its master.

init all
init nodename ...
init gtm [ master | slave | all ]
init gtm_proxy [ all | nodename ... ]
init coordinator nodename ...
init coordinator [ master | slave ] [ all | nodename ... ]
init datanode nodename ...
init datanode [ master | slave ] [ all | nodename ... ]

Initializes specified nodes.

At initialization, all the working directories of each component will be created if it does not exist. If it does, then all the contents under the working directory will be removed.

When "all" option is specified, then node information at each coordinator will be set up.

kill all
kill nodename ...
kill gtm [ master | slave | all ]
kill gtm_proxy [ all | nodename ... ]
kill coordinator nodename ...
kill coordinator [ master | slave ] [ all | nodename ... ]
kill datanode nodename ...
kill datanode [ master | slave ] [ all | nodename ... ]

Kills specified node. If nodename is specified and it has both master and slave, then both master and slave will be chosen.

When killing components, their ports will be cleaned too.

Please be careful that you should not use kill command unless you're ready to reinitialize all the component from the scratch. Especially, when your database is in production, you must not kill the component. Stop them using stop command with -m fast or -m smart option. This is for the test use and this command does not guarantee to maintain postgres-xc database cluster consistency.

log [ variable | var ] varname
log [ message | msg ] message_body

Prints the specified contents to the log file. variable or var option writes specified variable name and its value. message or msg option writes specified message.

monitor all
monitor nodename ...
monitor gtm [ master | slave | all ]
monitor gtm_proxy [ all | nodename ... ]
monitor coordinator nodename ...
monitor coordinator [ master | slave ] [ all | nodename ... ]
monitor datanode nodename ...
monitor datanode [ master | slave ] [ all | nodename ... ]

Monitors if specified nodes are running.

prepare [ path ]

Write pgxc_ctl configuration file template to the specified file. If path option is not specified, target file will be default configuration file, or the file specified by configFile option in /etc/pgxc_ctl or ~/.pgxc_ctl. If you specify relative path, it will be against pgxc_ctl_home.

Psql [ - coordinator ] psql_option ...

Invokes psql targetted to specified coordinator. If no coordinator is specifies, pgxc_ctl will choose one of the available ones.

q | quit | exit

Exits pgxc_ctl. This command has no option.

reconnect gtm_proxy [ all | nodename ... ]

Reconnects specified gtm_proxy to new gtm. This is needed after you failover gtm to its slave.

remove gtm slave
remove gtm_proxy nodename [ clean ]
remove coordinator [ master| slave ] nodename [ clean ]
remove datanode [ master| slave ] nodename [ clean ]

Removes the specified node from the cluster. If clean option is specified, then the work directory and listening socket will be cleared.

set varname value ...

Set variable value. You can specify multiple values to a variable. In this case simply specify these values as separated value.

show [ configuration | configure | config ] [ all | basic ]
show [ configuration | configure | config ] host hostname ...
show [ configuration | configure | config ] gtm [ all | master | slave ]
show [ configuration | configure | config ] gtm_proxy [ all | gtm_proxy_name ... ]
show [ configuration | configure | config ] [ coordinator | datanode ] [ all | master | slave ] nodename ...

Shows postgres-xc configuration.

If you specify all or basic, global configuration information will be given.

If you specify host with hostname list, configuration for each hostname will be given.

If you specify gtm, gtm information will be given. If you specify any more option, master and slave, if configured, information will be given. master and slave option will show only specified portion of the component.

If you specify gtm_proxy, gtm_proxy configuration will be given. all option will show all the gtm_proxy configuration. Giving a list of gtm_proxy names will show specified gtm_proxy configuration.

If you specify coordinator or datanode option, coordinator or datanode configuration will be shown respectively. all shows bot master and slave information. master and slave option will show each portion of the configuration. You can specify a list of node name to get configuration information for specific coordinator or datanode.

show resource datanode datanodename [ databasename [ username ] ]

Shows table names specified datanode is involved. Before removing a datanode, you must be sure that no tables are involved by such a datanode. This command shows what tables a given datanode is involved. If you don't specify the database name, default database (the name of the owner of XC, as specified by pgxcOwner variable, will be used. If you don't specify the username, pgxcOwner will be used.

Please note that this command is not intended to give complete information of the table distribution/replication. For specific users and schemas, you should check if there's nothing left to a datanode when you're removing a datanode master safely. Please also note that this command does not take care of the password. You might be prompted for the password if it id needed.

Typical SQL statement to check if there's any table involved by removing datanode is as follows:

SELECT pg_class.relname relation,
           CASE
             WHEN pclocatortype = 'H' THEN 'Hash'
             WHEN pclocatortype = 'M' THEN 'Modulo'
             WHEN pclocatortype = 'N' THEN 'Round Robin'
             WHEN pclocatortype = 'R' THEN 'Replicate'
             ELSE 'Unknown'
           END AS distribution,
            pg_attribute.attname attname,
            pgxc_node.node_name nodename
        FROM pg_class, pgxc_class, pg_attribute, pgxc_node
        WHERE pg_class.oid = pgxc_class.pcrelid
              and pg_class.oid = pg_attribute.attrelid
              and pgxc_class.pcattnum = pg_attribute.attnum
              and pgxc_node.node_name = 'datanode4'
              and pgxc_node.oid = ANY (pgxc_class.nodeoids)
     UNION
     SELECT pg_class.relname relation,
           CASE
             WHEN pclocatortype = 'H' THEN 'Hash'
             WHEN pclocatortype = 'M' THEN 'Modulo'
             WHEN pclocatortype = 'N' THEN 'Round Robin'
             WHEN pclocatortype = 'R' THEN 'Replicate'
             ELSE 'Unknown'
           END AS distribution,
            '- none -' attname,
            pgxc_node.node_name nodename
        FROM pg_class, pgxc_class, pg_attribute, pgxc_node
        WHERE pg_class.oid = pgxc_class.pcrelid
              and pg_class.oid = pg_attribute.attrelid
              and pgxc_class.pcattnum = 0
              and pgxc_node.node_name = 'datanode4'
              and pgxc_node.oid = ANY (pgxc_class.nodeoids)
         ;
      

where 'datanode4' is the datanode name in focus.

show [ variable | var ] [ all | varname ... ]

Displays configuration or variable name and its value.

start all
start nodename ...
start gtm [ master | slave | all ]
start gtm_proxy [ all | nodename ... ]
start coordinator nodename ...
start coordinator [ master | slave ] [ all | nodename ... ]
start datanode nodename ...
start datanode [ master | slave ] [ all | nodename ... ]

Starts specified node.

stop all
stop nodename ...
stop gtm [ master | slave | all ]
stop gtm_proxy [ all | nodename ... ]
stop coordinator nodename ... [ -m [ smart | fast | immediate ]
stop coordinator [ master | slave ] [ all | nodename ... ] [ -m [ smart | fast | immediate ]
stop datanode nodename ... [ -m [ smart | fast | immediate ]
stop datanode [ master | slave ] [ all | nodename ... ] [ -m [ smart | fast | immediate ]

Stops specified node. For datanode and coordinator, you can specify stop mode as in "pg_ctl stop" command.

When you stop coordinator or datanode slave, the master will be reconfigured to remove synchronous replication.

Please note that stopping coordinator or datanode in -m immediate is not safe. This is a kind of emergency method to stop each component. We have a little chance to make whole postgres-xc database cluster into inconsistent state. Plaese use -m smart or -m fast option as much as possible.

unregister unregister_option ...

Unregisteres specified node from the gtm. This could be needed when some node crashes and would like to start new one.

unregister_option is one of the following:

-n name: Specifies node name to unregister.

-Z { gtm | gtm_proxy | gtm_proxy_postmaster | coordinator | datanode }: Specifies the category of the specified node.