Finding Cluster Information
Information about your replication scenario is stored in the spock schema. You can retreive information
about your configuration by querying the spock schema, or by invoking the following NodeCtl
commands at the command line.
NodeCtl is the CLI installed with the pgEdge platform in the pgedge/nodectl directory. You can
invoke NodeCtl commands on the command line; ensure that you are in the pgedge directory, or that
pgedge is in your path.
To list the nodes in your replication scenario, use the spock node-list (opens in a new tab)command; provide the database name when you invoke the command:
./nc spock node-list database_name
To list the tables in your replication set, use the repset-list-tables (opens in a new tab) command; provide the schema name and the database name when you invoke the command:
./nc spock repset-list-tables schema_name database_name
To display the subscription status, use the sub-show-status (opens in a new tab) command; provide the subscription name and the database name when you invoke the command:
./nc spock sub-show-status subscription_name database_name
The following table describes all of the tables in the spock schema. You can query the spock schema at the psql command line, while connected to the PostgreSQL server installed by the pgEdge installer. Unless noted below, these tables are not replicated.
| Name | Description |
|---|---|
node | This table contains one row per replication node. This table is replicated, and contains the node_id, node_name, and optionally the location, country, and info. |
node_interface | This table contains one row per node interface. This table is replicated, and contains the if_id, if_name, if_nodeid, and if_dsn which represent the connection information for that node. |
local_node | This table contains one row with information for the local node. It contains the node_id and node_local_interface and can be joined with the previous two tables to show all local node information only. |
replication_set | This table contains one row per replication set. It contains the set_id, set_nodeid, set_name and four boolean columns: replicate_insert, replicate_update, replicate_delete, and replicate_truncate. This table contains three rows by default for the default, default_insert_only, and ddl_sql replication sets. |
replication_set_seq | This table contains one row per replication set sequence. It contains set_id and set_seqoid. |
replication_set_table | This table contains one row per table that is in any replication set. It contains the set_id, set_reloid (the table name), set_att_list, and set_row_filter. The last two columns contain the row and column filters on that table for replication. |
tables | This table contains one row per table in the database. It contains the relid, nspname (the schema), relname (the tablename), and set_name (null if that table is not added to any replication set). |
pii | This is an optional table in which you can define columns that contain personally identifiable information (pii). You can add information such as the schema, table, and column names and then use this table to createcolumn filters when adding tables to replication sets. |
subscription | This table contains one row per subscription. It contains the sub_id, sub_name, sub_origin, sub_target, sub_origin_if, sub_target_if, sub_enabled, sub_slot_name, sub_replication_sets (an array of replication sets that have been added to the subscription), sub_forward_origins, sub_apply_delay, and sub_force_text_transfer. |
resolutions | This table contains one row per resolution made on this node. It contains the id, node_name, log_time, relname, idxname, conflict_type (update_update), conflict_resolution (keep_local), local_origin, local_tuple, local_xid, local_timestamp, remote_origin, remote_tuple, remote_xid, remote_timestamp, remote_lsn. |