Ace API - pgEdge

ACE API Endpoints

ACE includes API endpoints for some of its most frequently used functions.

API Reference

ACE provides a REST API for programmatic access. The API server runs on localhost:5000 by default. An SSH tunnel is required to access the API from outside the host machine for security purposes. You must also configure client-based certificiate authentication before using the ACE API.

Please refer to Cert Auth with EasyRSA (opens in a new tab) for information about setting up server and client certificates.

You should create a client certificate separately for ACE--with all necessary privileges on tables, schemas, and databases that you want to use with ACE. This user should preferably be a superuser since ACE may need elevated privileges during diffs and repairs. Each external user can have their own client certificate--typically with lower privileges. The client's role will then need to be granted to the ACE user. For example, if the ACE user (with higher privileges) has a certificate with ace_user as the common name, and the external user has a certificate with external_user as the common name, then the external_user role will need to be granted to ace_user.

GRANT external_user TO ace_user;

This is required since ACE will attempt to use SET ROLE to switch to the external user's role before performing any operations, thus ensuring that diffs and repairs happen with the external user's privileges.

The table-diff API

Initiates a table diff operation.

Endpoint: GET /ace/table-diff

Request Body:

{
    "cluster_name": "my_cluster",        // required
    "table_name": "public.users",        // required
    "dbname": "mydb",                    // optional
    "block_rows": 10000,                 // optional, default: 10000
    "max_cpu_ratio": 0.8,                // optional, default: 0.6
    "output": "json",                    // optional, default: "json"
    "nodes": "all",                      // optional, default: "all"
    "batch_size": 50,                    // optional, default: 1
    "table_filter": "id < 1000",         // optional
    "quiet": false                       // optional, default: false
}

Parameters:

cluster_name (required): Name of the cluster
table_name (required): Fully qualified table name (schema.table)
dbname (optional): Database name
block_rows (optional): Number of rows per block (default: 10000)
max_cpu_ratio (optional): Maximum CPU usage ratio (default: 0.8)
output (optional): Output format ["json", "csv", "html"] (default: "json")
nodes (optional): Nodes to include ("all" or comma-separated list)
batch_size (optional): Batch size for processing (default: 50)
table_filter (optional): SQL WHERE clause to filter rows for comparison
quiet (optional): Suppress output (default: false)

Example Request:

curl -X POST "http://localhost:5000/ace/table-diff" \
  -H "Content-Type: application/json" \
  --cert /path/to/client.crt \
  --key /path/to/client.key \
  -d '{
    "cluster_name": "my_cluster",
    "table_name": "public.users",
    "output": "html"
  }'

Example Response:

{
    "task_id": "td_20240315_123456",
    "submitted_at": "2024-03-15T12:34:56.789Z"
}

The table-repair API

Initiates a table repair operation.

Endpoint: GET /ace/table-repair

Request Body:

{
    "cluster_name": "my_cluster",        // required
    "diff_file": "/path/to/diff.json",   // required
    "source_of_truth": "primary",        // required unless fix_nulls is true
    "table_name": "public.users",        // required
    "dbname": "mydb",                    // optional
    "dry_run": false,                    // optional, default: false
    "quiet": false,                      // optional, default: false
    "generate_report": false,            // optional, default: false
    "upsert_only": false,                // optional, default: false
    "insert_only": false,                // optional, default: false
    "bidirectional": false,              // optional, default: false
    "fix_nulls": false,                  // optional, default: false
    "fire_triggers": false               // optional, default: false
}

Parameters:

cluster_name (required): Name of the cluster
diff_file (required): Path to the diff file
source_of_truth (required): Source node for repairs
table_name (required): Fully qualified table name
dbname (optional): Database name
dry_run (optional): Simulate repairs (default: false)
quiet (optional): Suppress output (default: false)
generate_report (optional): Create detailed report (default: false)
upsert_only (optional): Skip deletions (default: false)
insert_only (optional): Repair INSERT statements only (default: false)
bidirectional (optional): Insert missing rows in a bidirectional manner (default: false)
fix_nulls (optional): Fix NULL values by comparing across nodes (default: false)
fire_triggers (optional): fire triggers when ACE performs a repair; note that ENABLE ALWAYS triggers will always fire. (default: false)

Example Request:

curl -X POST "http://localhost:5000/ace/table-repair" \
  -H "Content-Type: application/json" \
  --cert /path/to/client.crt \
  --key /path/to/client.key \
  -d '{
    "cluster_name": "my_cluster",
    "diff_file": "/path/to/diff.json",
    "source_of_truth": "primary",
    "table_name": "public.users"
  }'

Example Response:

{
    "task_id": "tr_20240315_123456",
    "submitted_at": "2024-03-15T12:34:56.789Z"
}

The table-rerun API

Reruns a previous table diff operation.

Endpoint: POST /ace/table-rerun

Request Body:

{
    "cluster_name": "my_cluster",        // required
    "diff_file": "/path/to/diff.json",   // required
    "table_name": "public.users",        // required
    "dbname": "mydb",                    // optional
    "quiet": false,                      // optional, default: false
    "behavior": "multiprocessing"        // optional, default: "multiprocessing"
}

Parameters:

cluster_name (required): Name of the cluster
diff_file (required): Path to the previous diff file
table_name (required): Fully qualified table name
dbname (optional): Database name
quiet (optional): Suppress output (default: false)
behavior (optional): Processing behavior ["multiprocessing", "hostdb"]

Example Request:

curl -X POST "http://localhost:5000/ace/table-rerun" \
  -H "Content-Type: application/json" \
  --cert /path/to/client.crt \
  --key /path/to/client.key \
  -d '{
    "cluster_name": "my_cluster",
    "diff_file": "/path/to/diff.json",
    "table_name": "public.users"
  }'

Example Response:

{
    "task_id": "tr_20240315_123456",
    "submitted_at": "2024-03-15T12:34:56.789Z"
}

The task-status API

Retrieves the status of a submitted task.

Endpoint: GET /ace/task-status/<task_id>

Parameters:

task_id (required): The ID of the task to check

Example Request:

curl "http://localhost:5000/ace/task-status?task_id=td_20240315_123456" \
  --cert /path/to/client.crt \
  --key /path/to/client.key

Example Response:

{
    "task_id": "td_20240315_123456",
    "task_type": "table-diff",
    "status": "COMPLETED",
    "started_at": "2024-03-15T12:34:56.789Z",
    "finished_at": "2024-03-15T12:35:01.234Z",
    "time_taken": 4.445,
    "result": {
        "diff_file": "/path/to/output.json",
        "total_rows": 10000,
        "mismatched_rows": 5,
        "summary": {
            // Additional task-specific details
        }
    }
}

Spock Exception Update API

Updates the status of a Spock exception.

Endpoint: POST /ace/update-spock-exception

Request Body:

{
    "cluster_name": "my_cluster",                   // required
    "node_name": "node1",                           // required
    "dbname": "mydb",                               // optional
    "exception_details": {                          // required
        "remote_origin": "origin_oid",              // required
        "remote_commit_ts": "2024-03-15T12:34:56Z", // required
        "remote_xid": "123456",                     // required
        "command_counter": 1,                       // optional
        "status": "RESOLVED",                       // required
        "resolution_details": {                     // optional
            "details": "Issue fixed"
        }
    }
}

Parameters:

cluster_name (required): Name of the cluster
node_name (required): The name of the node
dbname (optional): The name of the database
exception_details (required)
- remote_origin (optional): The OID of the origin
- remote_commit_ts (optional): The timestamp of the exception
- remote_xid (optional): The XID of the transaction
- command_counter (optional): The number of commands executed
- status (optional): The current state of the exception
- resolution_details (optional):
  - details: Include details about the exception

Example Request:

curl -X POST "http://localhost:5000/ace/update-spock-exception" \
  -H "Content-Type: application/json" \
  --cert /path/to/client.crt \
  --key /path/to/client.key \
  -d '{
    "cluster_name": "my_cluster",
    "node_name": "node1",
    "exception_details": {
        "remote_origin": "origin1",
        "remote_commit_ts": "2024-03-15T12:34:56Z",
        "remote_xid": "123456",
        "status": "RESOLVED"
    }
  }'

Example Response:

{
    "message": "Exception status updated successfully"
}

API Error Responses

ACE API endpoints return error responses in the following format:

{
    "error": "Description of what went wrong"
}

Common HTTP status codes:

200: Success
400: Bad Request (missing or invalid parameters)
401: Unauthorized (missing or invalid client certificate)
415: Unsupported Media Type (request body is not JSON)
500: Internal Server Error

Using ACE Functions Scheduling ACE Monitoring