pgraft SQL Reference
Cluster Lifecycle
Lifecycle helpers manage initial bootstrap, node membership, and cluster shape changes. Always execute membership changes on the elected leader and confirm quorum before shutting down members.
pgraft_init()
Initializes pgraft metadata using configured GUC parameters. Automatically executed on CREATE EXTENSION but exposed for reinitialization.
Returns: boolean
Usage
-- Initialize cluster (typically automatic during CREATE EXTENSION)
SELECT pgraft_init();pgraft_add_node(node_id integer, address text, port integer)
Registers a follower with the elected leader. Ensure the follower is running and shares the same cluster_id before calling.
Returns: void
Usage
-- Register followers from the leader node
SELECT pgraft_add_node(2, '10.0.0.12', 7002);
SELECT pgraft_add_node(3, '10.0.0.13', 7003);pgraft_remove_node(node_id integer)
Removes a node from membership and updates the Raft configuration. Wait for quorum confirmation before shutting down the node.
Returns: void
Usage
-- Gracefully remove node 3
SELECT pgraft_remove_node(3);
SELECT pgraft_quorum_met() AS quorum_ok;Leadership Operations
Leader discovery and transfer functions coordinate manual failovers and allow automation to verify topology.
pgraft_get_leader()
Returns the current leader node ID as observed by the caller.
Returns: integer
Usage
SELECT pgraft_get_leader(); -- Example: returns 1pgraft_is_leader()
Boolean helper indicating whether the connected session is running on the leader.
Returns: boolean
Usage
SELECT pgraft_is_leader();pgraft_transfer_leadership(target_node_id integer)
Requests the current leader to step down and promote another node. Useful for scheduled maintenance and rolling restarts.
Returns: void
Usage
-- Promote node 2 to become leader
SELECT pgraft_transfer_leadership(2);Monitoring Views
pgraft exposes lightweight SQL views that surface Raft metrics, node status, and log growth without attaching external agents.
pgraft_get_cluster_status()
Primary cluster health view containing leadership state, current term, quorum, message counters, and election statistics.
Query
SELECT * FROM pgraft_get_cluster_status();pgraft_get_nodes()
Lists registered members with connection info, Raft role, and replication indexes as seen by the local node.
Query
SELECT node_id,
address,
port,
state,
is_leader
FROM pgraft_get_nodes();pgraft_log_get_stats()
Reports Raft log volume, commit/applied indexes, and disk usage metrics for sizing and troubleshooting.
Query
SELECT first_index,
last_index,
commit_index,
applied_index,
total_entries,
disk_usage_mb
FROM pgraft_log_get_stats();Consensus Diagnostics
Combine status and log views to create dashboards or alerting pipelines. The examples below illustrate common checks for operational readiness.
Election stability
SELECT node_id,
state,
current_term,
elections_triggered,
elections_triggered::float / GREATEST(current_term, 1) AS elections_per_term
FROM pgraft_get_cluster_status();Follower lag
SELECT node_id,
match_index,
commit_index,
lag_entries,
state
FROM pgraft_log_get_replication_status()
ORDER BY lag_entries DESC;Log growth rate
SELECT total_entries,
disk_usage_mb,
(disk_usage_mb::float / NULLIF(total_entries, 0)) * 1024 AS avg_entry_kb
FROM pgraft_log_get_stats();Node connectivity
SELECT node_id,
address,
port,
last_heartbeat_ms,
replication_lag_bytes
FROM pgraft_get_nodes();Key-Value Store API
pgraft bundles a replicated KV store for small configuration payloads and coordination primitives. Values replicate through the Raft log and honor the same commit guarantees as SQL writes.
pgraft_kv_put(key text, value jsonb)
Writes an item to the replicated key-value store that ships with pgraft.
Returns: void
Usage
SELECT pgraft_kv_put('models/embedding', jsonb_build_object('version', '1.2.0', 'status', 'active'));pgraft_kv_get(key text)
Reads a value from the KV store. Returns NULL when the key does not exist.
Returns: jsonb
Usage
SELECT pgraft_kv_get('models/embedding');pgraft_kv_delete(key text)
Removes a key/value pair. Replicated to the entire cluster through the Raft log.
Returns: void
Usage
SELECT pgraft_kv_delete('models/embedding');Maintenance & Debug Helpers
Runtime configuration functions allow automation pipelines to tune consensus behavior, enable debug logging, and persist safe defaults without restarting PostgreSQL.
pgraft_set_config(name text, value text)
Adjusts pgraft configuration at runtime. Changes persist only in memory until pgraft_save_config() is called.
Returns: void
Usage
SELECT pgraft_set_config('heartbeat_interval', '75ms');pgraft_save_config()
Persists runtime configuration overrides to the metadata catalog so changes survive restarts.
Returns: void
Usage
SELECT pgraft_save_config();pgraft_set_debug(enabled boolean)
Enables verbose logging inside pgraft for investigating consensus behavior.
Returns: void
Usage
SELECT pgraft_set_debug(true);
-- ... perform operations ...
SELECT pgraft_set_debug(false);Automation Recipes
Bundle the fundamental commands into reusable automation tasks for DevOps pipelines and incident response playbooks.
Rolling maintenance checklist
-- 1. Confirm quorum and leadership
SELECT pgraft_quorum_met(), pgraft_get_leader();
-- 2. Transfer leadership away from maintenance target
SELECT pgraft_transfer_leadership(2);
-- 3. Pause automatic failover if required
SELECT pgraft_set_config('failover_enabled', 'false');
-- 4. After maintenance, resume elections and verify status
SELECT pgraft_set_config('failover_enabled', 'true');
SELECT * FROM pgraft_get_cluster_status();