Simplifying Cluster-Wide SQL Execution in pgEdge with exec_node()

by

pgs distributed postgres is a database system built on top of the standard open-source Postgres, extended to support global, distributed and multi-master (active-active) deployment.

In the emerging landscape of distributed databases, efficient query execution across nodes is essential to leverage the full power of distributed architectures. Particularly for distributed Postgres environments, managing multi-node clusters often requires executing SQL commands that are not automatically repeated. It includes important operations like implemented DDL statements, performing administrative tasks and making changes to the cluster configuration – actions which should be implemented only on specific nodes,

To solve this operational challenge, I created exec_node() Function: A utility designed to make Remote SQL execution into pgEdge nodes directly from within the database is simple, consistent, and scriptable,

why did i make it exec_node()

As part of general administration tasks, there is usually a need to execute SQL commands on a specific node or on all nodes within a pgEdge distributed cluster. pgEdge leverage Spock For logical replication, but many important SQL commands—notably the following DDL and Spock-specific cluster management functions—don’t copy By design. It includes such operations:

  • CREATE DATABASE, DROP DATABASE

  • ALTER DATABASE, ALTER SYSTEM

  • ,VACUUM

  • Spock-specific commands such as spock.repset_add_table, spock.node_add_interface

In traditional setups, these commands require manual logins, scripts, or orchestration tools to execute securely and consistently across all or specific nodes. This takes time and requires additional steps.

using exec_node()

with exec_node()You can issue commands – via SQL – directly from the database and target the exact node you want. The function signature is:

exec_node(sql text, node text DEFAULT 'all')

  • sql: SQL command to execute.

  • node (Optional): Name of the target node. default to ,all,which means the command will execute all nodes In the cluster.

with exec_node() you can:

Run SQL on any or all nodes – remotely and natively

Whether you’re running maintenance commands, executing DDL statements, or configuring Spock, you can use exec_node() To do all this from a single SQL interface.

Example: Running data maintenance command statement only Node1:

SELECT exec_node('VACUUM ANALYZE;', 'node1'),

Example: Applying maintenance operations to all nodes:

SELECT exec_node('VACUUM ANALYZE'),

Execute non-replicated orders where they are

Some SQL commands are not intentionally repeated In PGD; This is either to avoid conflicts or because they are inherently local. exec_node() allows these commands to be sent Only for relevant nodesAvoiding misconfigurations or inconsistencies.

Common non-replication commands include:

  • ALTER SYSTEM SET

  • CREATE / DROP DATABASE

  • ALTER DATABASE SET

Example: Setting GUC value on node 3 (only):

SELECT exec_node('ALTER SYSTEM SET log_min_duration_statement = 500; SELECT pg_reload_conf();', 'node3');

Example: Changing GUC on all cluster nodes:

SELECT exec_node('ALTER SYSTEM SET log_statement = ''all''; SELECT pg_reload_conf();'); SELECT exec_node('ALTER SYSTEM SET spock.enable_ddl_replication=on; SELECT pg_reload_conf();');

Perform Spock cluster management task

pgEdge clusters are built on top of Spock Extension for logical replication, but Spock management command must be run on a specific node-And they don’t copy. exec_node() Makes it easy to automate and manage.

Example: Adding a table to a replica set:

SELECT exec_node('begin; SELECT spock.repset_add_table(''default'', ''t1''); commit;', 'node3');

Example: Creating a table without adding all pgEdge nodes to the replica set:

SELECT exec_node('begin; select spock.repair_mode(true); create table t2 (a int primary key, b text); SELECT spock.repset_remove_table(''default'', ''t2''); commit;'); 

SELECT exec_node('begin; select spock.repair_mode(true); SET spock.include_ddl_repset=off; create table t2 (a int primary key, b text); commit;');

Without exec_node()These operations would require logging into each node or writing external scripts; These can now be run as SQL from any connected client or script.

Supports targeted DDL deployment

Sometimes not every function or schema change is needed on every node. Instead, you might want:

Example: Target function deployment:

SELECT exec_node('begin; SELECT spock.repair_mode(true); CREATE FUNCTION debug_info2() RETURNS text AS $$ SELECT current_database(); $$ LANGUAGE sql; commit;', 'node2');

You now have precise control over where that function lives.

Improve automation and operational security

Because exec_node() Works like any SQL function, it integrates seamlessly into:

This removes the need for external scripting or SSH automation and reduces the risk of human error by centralizing command execution in a controlled and auditable manner.

use cases

Use exec_node() To help:

  • Deploying a non-replicated data maintenance command, ddl Or Work For a specific node.

  • Running the Spock configuration command (repset_add_table, node_add_interfaceetc.).

  • Executing maintenance orders (VACUUM, REINDEX, ANALYZE) cluster-wide.

  • Setting or changing system parameters per node (ALTER SYSTEM,

  • Creating or dropping databases on individual nodes.

  • Controlled rollout of feature flags or logic across a subset of nodes.

best practices

  • use with awareness: While powerful, be careful when performing write operations on all nodes. Make sure orders are safe/secure and do not pose risks.

  • log execution:In automation scripts, consider the use of logging exec_node() For audit.

  • Validate SQL: Validate the structure and scope to avoid unexpected changes, especially when executing SQL.

  • test on dev/staging:For complex cluster operations, test exec_node() In a non-production environment before rollout.

exec_node() The function was designed to bridge a significant gap in the management of pgEdge clusters, providing users with a secure, simple, and SQL-native way to execute non-replicated operations on any node (or all nodes!) from within the database.

Whether you’re managing replica sets, creating node-local functions, or automating system-wide maintenance, exec_node() Provides a powerful and flexible tool to simplify your workflow and ensure operational stability in distributed environments.

If you are working with pgEdge and haven’t tried it yet, exec_node() Can save you hours of manual work, while reducing the risk of mistakes.

Haven’t you used pgEdge? You can self-host directly from GitHub: https://github.com/pgEdge/ Or, get an all-in-one download package on our Getting Started page: https://www.pgedge.com/get-started/platform

We welcome any feedback; Contact us through pgEdge Community Discord Channelanytime.

exec_node function code

 * Execute SQL on pgEdge node(s)
 * sql: sql code or statement
 * node: node name, default is `all` that will execute sql on all the cluster nodes
 *
 * Note: This function depends on dblink extension



CREATE OR REPLACE FUNCTION exec_node(sql text, node text DEFAULT 'all')
RETURNS VOID
AS $$
DECLARE
	ret text;
    r RECORD;
BEGIN
	-- CREATE EXTENSION IF NOT EXISTS dblink;
	IF node = 'all' THEN
		FOR r IN SELECT if_name, if_dsn FROM spock.node_interface LOOP
			SELECT dblink_exec(r.if_dsn, sql, false) INTO ret;
			RAISE NOTICE 'SQL `%` executed on node `%` : %', sql, r.if_name, ret;
		END LOOP;
	ELSE
		SELECT dblink_exec(if_dsn, sql, false) INTO STRICT ret FROM spock.node_interface WHERE if_name = node;
		RAISE NOTICE 'SQL `%` executed on node `%` : %', sql, node, ret;
	END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'Node `%` not found!', node;
    WHEN TOO_MANY_ROWS THEN
	-- should never happen
        RAISE EXCEPTION 'Multiple nodes found with the same name `%` !', node;
    WHEN OTHERS THEN
        RAISE EXCEPTION 'An unexpected error occurred: SQLSTATE % - %', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;



Leave a Comment