Note: XCONLY: 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.
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.
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.
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.
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.
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.
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.
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:
Specifies configuration file. The default is pgxc_ctl_conf, or the value of configFile option found in the initialization file.
Specifies $PGXC_CTL_HOME directory. You can specify this as pgxc_ctl_home variable in the initialization file.
Specifies where to read pgxc_ctl commands. There's not corresponding variables in the initialization file. Default is the standard input.
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.
Specifies the directory of the log file. Default is $PGXC_HOME/pgxc_log/.
Specifies where to write pgxc_ctl output. Default is the standard output.
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.
Prints pgxc_ctl version and exits.
Specifies to run pgxc_ctl to print as many messages as possible.
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:
Specify the configuration file name. Default is pgxc_ctl.conf. This option can be overridden by -c command line option.
Specify remote temporary directory, default is /tmp.
Specifies the directory to write log. Can be overridden by -l command line option.
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.
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.
Specifies $PGXC_CTL_HOME. Default is $HOME/pgxc_ctl or environment variable $PGXC_CTL_HOME. Can be overriden by --home command line option.
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.
Specify local temporary directory, default is /tmp.
Specifies verbose message output from pgxc_ctl. Value should be y or n. Can be overridedn by -v or --silent command line option.
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 $
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 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 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 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 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).
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.
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.
Option if you backup the configuration file to a remote server. Specify y if you'd like to backup the configuration file. n otherwise.
Name of the configuration backup file. Effective when configuration file backup is enabled.
Host name (or IP address) where you backup the configuration file. Effective when configuration file backup is enabled.
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.
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.
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.
Name of the operating system user you are logging in as postgres-xc owner. At present, this should be the same as $pgxcOwner.
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.
Node name of GTM.
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.
Work directory for GTM master.
Listening port number of GTM master.
Host name where GTM master runs.
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
Option to enable GTM slave. Specify y to enable, n otherwise.
Work directory for GTM slave.
Listening port number of GTM slave.
Host name where GTM slave runs. Effective only when GTM slave is effective.
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.
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.
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.
Specify work directory for each GTM proxy.
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.
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.
Specify listening port number for each GTM proxy.
Specify host name where each of the GTM Proxy runs. Specify server name as the same order as $gtmProxyNames.
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.
Shortcut to assign the same WAL archive directory to all the coordinator slaves. Not needed if you specify these manually.
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.
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.
File name which contains entries of pg_hba.conf file for all the coordinators. Specify none if you do not have such file.
Shortcut to assign the same work directory to all the coordinator masters. Not needed if you specify these manually.
Array of coordinator master work directory.
Array of the host name where each coordinator master runs. Specify in the order of coordNames above.
Shortcut to assign the same value to each member of coordMaxWalSenders. Not needed if you assign the value manually.
Array of coordinator max_wal_senders value. Note that a master and the slave shares the same value of this variable.
Array to specify coordinator names. Coordinator slave uses the same name as the master.
Array of CIDR addresses to be added to pg_hba.conf. Will create pg_hba.conf file entry with pgxcOwner user.
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.
Specify y if you configure coordinator slave. n otherwise. If you specify n, then all the other variables for coordinator slave will be ignored.
Shortcut to assign the same work directory to all the coordinator slaves. Not needed if you specify these manually.
Array of work directory for each coordinator slaves.
Array of the hostname where slave of each coordinator runs. Specify none if you don't configure the slave for specific coordinator.
Array of the filename which contains extra configuration values for each coordinator. Specify none if you don't have such file.
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.
Shortcut to assign the same WAL archive directory to all the datanode slaves. Not needed if you specify these manually.
Array of WAL archive log directory for each datanode slave.
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.
File name which contains entries for all the datanodes' pg_hba.conf file. Specify none if you don't have such file.
Shortcut to assign the same work directory to all the datanode masters. Not needed if you specify these manually.
Array of datanode master work directory.
Array of the host name where each datanode master runs. Specify in the order of $coordNames above.
shortcut to assign the same value to each member of datanodeMaxWalSenders. Not needed if you assign the value manually.
Array of datanode max_wal_senders value.
Array to specify coordinator names.
Array of CIDR addresses to be added to pg_hba.conf. Will create pg_hba.conf file entry with $pgxcOwner user.
Array of the listening port number for each datanode (master and slave use the same port).
Specify y if you configure datanode slaves. n otherwise. If you specify n, all the other variables for datanode slaves will be ignored.
Shortcut to assign the same work directory to all the datanode slaves. Not needed if you specify these manually.
Array of work directory for each datanode slave.
Array of the hostname where slave of each datanode runs. Specify none if you don't configure the slave for specific coordinator.
Array of the filename which contains extra configuration values for each datanode. Specify none if you don't have such file.
Array of file names which contain specific extra pg_hba.conf entry for each datanode. Specify none if you don't have such file.
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.
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 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.
Adding datanode by this command does not redistribute existing table data. You should do it by issuing ALTER TABLE command.
Invokes createdb utility to create a new datanode using specified coordinator. If no coordinator is specified, pgxc_ctl chooses one of the available ones.
Invokes createuser utility to create a new user using specified coordinator. Of coordinator is not specified, pgxc_ctl chooses one of the available ones.
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 specified node to its master.
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.
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.
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.
Monitors if specified nodes are running.
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.
Invokes psql targetted to specified coordinator. If no coordinator is specifies, pgxc_ctl will choose one of the available ones.
Exits pgxc_ctl. This command has no option.
Reconnects specified gtm_proxy to new gtm. This is needed after you failover gtm to its slave.
Removes the specified node from the cluster. If clean option is specified, then the work directory and listening socket will be cleared.
Remoging datanode by this command does not redistribute existing table data. You should do it by issuing ALTER TABLE command.
Set variable value. You can specify multiple values to a variable. In this case simply specify these values as separated value.
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.
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.
Displays configuration or variable name and its value.
Starts specified node.
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.
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.