Troubleshooting
Finding Cluster Information

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 pgedge commands at the command line.

pgedge is the CLI installed with the pgEdge platform in the pgedge directory. You can invoke pgedge commands on the command line; ensure that you are in the pgedge directory, or that pgedge is in your path.

To find your installed version of pgEdge Platform, use the command:

./pgedge info

######################################################################
#  pgEdge CLI: v24.1.6  /home/susan/work/ace/pgedge
# User & Host: susan    127.0.0.1
#          OS: Rocky Linux 9.2 (Blue Onyx)  glibc-2.34-el9
#     Machine: 4 GB, 4 vCPU, Intel Core i7-4870HQ @ 2.50GHz
#       Langs: Python v3.9.16 | Perl v5.32.1 | Java v11.0.20
#    Repo URL: https://pgedge-upstream.s3.amazonaws.com/REPO
# Last Update: 2024-01-30 12:15:49
######################################################################

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:

./pgedge 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:

./pgedge 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:

./pgedge 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.

NameDescription
nodeThis 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_interfaceThis 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_nodeThis 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_setThis 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_seqThis table contains one row per replication set sequence. It contains set_id and set_seqoid.
replication_set_tableThis 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.
tablesThis 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).
piiThis 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.
subscriptionThis 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.
resolutionsThis 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.