pgEdge Distributed Postgres is a database system built on top of standard open‑source Postgres, extended to support global, distributed, and multi‑master (active‑active) deployments.

In the evolving landscape of distributed databases, efficient query execution across nodes is essential to leverage the full power of a distributed architecture. Specifically for distributed Postgres environments, managing a multi-node cluster often requires executing SQL commands that don’t automatically replicate. This includes critical operations like executing DDL statements, performing administrative tasks, and altering cluster configuration - actions that must be applied only on specific nodes.

To solve this operational challenge, I created the exec_node() function: a utility designed to make remote SQL execution across pgEdge nodes simple, consistent, and scriptable directly from within the database.

Why I Created exec_node()

As part of general administration tasks, there is commonly a need to execute SQL commands on a specific node or all nodes within a pgEdge distributed cluster. pgEdge leverages Spock for logical replication, but many important SQL commands—particularly the following DDL and Spock-specific cluster management functions—do not replicate by design. This includes operations like:

• CREATE DATABASE, DROP DATABASE

• ALTER DATABASE, ALTER SYSTEM

• -VACUUM

• Spock-specific commands like spock.repset_add_table, spock.node_add_interface

In a traditional setup, executing these commands safely and consistently across all or specific nodes requires manual logins, scripts, or orchestration tools. This is time-consuming and requires additional steps.

Using exec_node()

With exec_node(), you can issue commands directly from the database—through SQL—and target the exact node you want. The function signature is:

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

• sql: The SQL command to execute.

• node (optional): The name of the target node. Defaults to 'all', which means the command will execute on all nodes in the cluster.

With exec_node() you can:

Run SQL on Any or All Nodes — Remotely and Natively

Whether you're running a maintenance command, executing DDL statements, or configuring Spock, you can use exec_node() to do it all from a single SQL interface.

Example: Running a data maintenance command statement only on node1:

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

Example: Applying a maintenance operation across all nodes:

SELECT exec_node('VACUUM ANALYZE');

Execute Non-Replicating Commands Where They Belong

Some SQL commands are intentionally not replicated in pgEdge; this is either to avoid conflicts or is because they are inherently local. exec_node() allows these commands to be sent only to the relevant node(s), avoiding misconfiguration or inconsistencies.

Common non-replicating commands include:

• ALTER SYSTEM SET

• CREATE / DROP DATABASE

• ALTER DATABASE SET

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

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

Example: Changing a GUC on all the 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();');

Execute Spock Cluster Management Functions

pgEdge clusters are built on top of the Spock extension for logical replication, but Spock management commands must be run on a specific node—and they don’t replicate. exec_node() makes this easy to automate and manage.

Example: Adding a table to a replication set:

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

Example: Creating a table on all the pgEdge nodes without adding them to the replication 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; now they can be run as SQL from any connected client or script.

Supports Targeted DDL Deployment

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

Example: Targeted 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.

Improves Automation and Operational Safety

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

It removes the need for external scripting or SSH automation and reduces the risk of human error by centralising command execution in a controlled and auditable way.

Use Cases

Use exec_node() to help with:

• deploying a non-replicated data maintenance command,  DDL or functions to a specific node.

• running Spock configuration commands (repset_add_table, node_add_interface, etc.).

• executing maintenance commands (VACUUM, REINDEX, ANALYZE) cluster-wide.

• setting or altering system parameters per node (ALTER SYSTEM).

• Creating or dropping databases on individual nodes.

• Controlled rollout of feature flags or logic to subset of nodes.

Best Practices

• Use with awareness: While powerful, be careful when executing write operations across all nodes. Ensure commands are safe / secure and do not pose risks.

• Log executions: In automation scripts, consider logging the use of exec_node() for auditability.

• Validate SQL: Especially when executing SQL, validate the structure and scope to avoid unintended changes.

• Test on dev/staging: For complex cluster operations, test exec_node() in non-production environments before rollout.

The exec_node() function was designed to bridge a critical gap in managing pgEdge clusters, giving users a safe, simple, and SQL-native way to execute non-replicated operations on any node (or all nodes!) from within the database.

Whether you're managing replication 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 consistency in a distributed environment.

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

Haven’t used pgEdge? You can self-host straight 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; get in touch with us through the pgEdge community Discord channel, anytime.

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;