add_data_node()

Add a new data node on the access node to be used by distributed hypertables. The data node will automatically be used by distributed hypertables that are created after the data node has been added, while existing distributed hypertables require an additional attach_data_node.

If the data node already exists, the command will abort with either an error or a notice depending on the value of if_not_exists.

For security purposes, only superusers or users with necessary privileges can add data nodes (see below for details). When adding a data node, the access node will also try to connect to the data node and therefore needs a way to authenticate with it. TimescaleDB currently supports several different such authentication methods for flexibility (including trust, user mappings, password, and certificate methods). Please refer to Setting up Multi-Node TimescaleDB for more information about node-to-node authentication.

Unless bootstrap is false, the function will attempt to bootstrap the data node by:

  1. Creating the database given in database that will serve as the new data node.
  2. Loading the TimescaleDB extension in the new database.
  3. Setting metadata to make the data node part of the distributed database.

Note that user roles are not automatically created on the new data node during bootstrapping. The distributed_exec procedure can be used to create additional roles on the data node after it is added.

Required Arguments

NameDescription
node_nameName for the data node.
hostHost name for the remote data node.

Optional Arguments

NameDescription
databaseDatabase name where remote hypertables will be created. The default is the current database name.
portPort to use on the remote data node. The default is the PostgreSQL port used by the access node on which the function is executed.
if_not_existsDo not fail if the data node already exists. The default is FALSE.
bootstrapBootstrap the remote data node. The default is TRUE.
passwordPassword for authenticating with the remote data node during bootstrapping or validation. A password only needs to be provided if the data node requires password authentication and a password for the user does not exist in a local password file on the access node. If password authentication is not used, the specified password will be ignored.

Returns

ColumnDescription
node_nameLocal name to use for the data node
hostHost name for the remote data node
portPort for the remote data node
databaseDatabase name used on the remote data node
node_createdWas the data node created locally
database_createdWas the database created on the remote data node
extension_createdWas the extension created on the remote data node

Errors

An error will be given if:

  • The function is executed inside a transaction.
  • The function is executed in a database that is already a data node.
  • The data node already exists and if_not_exists is FALSE.
  • The access node cannot connect to the data node due to a network failure or invalid configuration (e.g., wrong port, or there is no way to authenticate the user).
  • If bootstrap is FALSE and the database was not previously bootstrapped.

Privileges

To add a data node, you must be a superuser or have the USAGE privilege on the timescaledb_fdw foreign data wrapper. To grant such privileges to a regular user role, do:

  1. GRANT USAGE ON FOREIGN DATA WRAPPER timescaledb_fdw TO <newrole>;

Note, however, that superuser privileges might still be necessary on the data node in order to bootstrap it, including creating the TimescaleDB extension on the data node unless it is already installed.

Sample usage

Let’s assume that you have an existing hypertable conditions and want to use time as the time partitioning column and location as the space partitioning column. You also want to distribute the chunks of the hypertable on two data nodes dn1.example.com and dn2.example.com:

  1. SELECT add_data_node('dn1', host => 'dn1.example.com');
  2. SELECT add_data_node('dn2', host => 'dn2.example.com');
  3. SELECT create_distributed_hypertable('conditions', 'time', 'location');

If you want to create a distributed database with the two data nodes local to this instance, you can write:

  1. SELECT add_data_node('dn1', host => 'localhost', database => 'dn1');
  2. SELECT add_data_node('dn2', host => 'localhost', database => 'dn2');
  3. SELECT create_distributed_hypertable('conditions', 'time', 'location');

Note that this does not offer any performance advantages over using a regular hypertable, but it can be useful for testing.