Skip to content

Function Reference

Connector Management

synchdb_add_conninfo

Purpose: Creates a new connector configuration

Parameters:

Parameter Description Required Example Notes
name Unique identifier for this connector 'mysqlconn' Must be unique across all connectors
hostname IP/hostname of heterogeneous database '127.0.0.1' Support IPv4, IPv6, and hostnames
port Port number for database connection 3306 Default: MySQL(3306), SQLServer(1433)
username Authentication username 'mysqluser' Requires appropriate permissions
password Authentication password 'mysqlpwd' Stored securely
source database Source database name 'inventory' Must exist in source system
destination database Target PostgreSQL database 'postgres' Must exist in PostgreSQL
table Table specification pattern '[db].[table]' Empty = replicate all tables, support regular expressions (for example, mydb.testtable*), use file: prefix to make connector read table list from a JSON file (for example, file:/path/to/filelist.json). See below for file format
connector Connector type (mysql/sqlserver) 'mysql' See supported connectors above
rule file Data type translation rules 'myrule.json' Must be in $PGDATA directory

Tablelist File Example:

{
    "table_list":
    [
        "mydb.table1",
        "mydb.table2",
        "mydb.table3",
        "mydb.table4"
    ]
}

Example Usage:

-- MySQL Example
SELECT synchdb_add_conninfo(
    'mysqlconn',    -- Connector name
    '127.0.0.1',    -- Host
    3306,           -- Port
    'mysqluser',    -- Username
    'mysqlpwd',     -- Password
    'inventory',    -- Source DB
    'postgres',     -- Target DB
    '',             -- Tables (empty for all)
    'mysql',        -- Connector type
    'myrule.json'   -- Rules file
);

-- SQL Server Example
SELECT synchdb_add_conninfo(
    'sqlserverconn',
    '127.0.0.1',
    1433,
    'sa',
    'MyPassword123',
    'testDB',
    'postgres',
    'dbo.orders',   -- Specific table
    'sqlserver',
    'mssql_rules.json'
);

Basic Control Functions

synchdb_start_engine_bgw

Purpose: Initiates a connector

SELECT synchdb_start_engine_bgw('mysqlconn');
You may also include snapshot mode to start the connector with, otherwise the initial mode will be used by default. See below for list of different snapshot modes.
SELECT synchdb_start_engine_bgw('mysqlconn', 'no_data');

synchdb_pause_engine

Purpose: Temporarily halts a running connector

SELECT synchdb_pause_engine_bgw('mysqlconn');

synchdb_resume_engine

Purpose: Resumes a paused connector

SELECT synchdb_resume_engine('mysqlconn');

synchdb_stop_engine_bgw

Purpose: Terminates a connector

SELECT synchdb_stop_engine('mysqlconn');

State Management

synchdb_state_view

Purpose: Monitors connector states and status

SELECT * FROM synchdb_state_view();

Return Fields:

Field Description Type
id Connector slot identifier Integer
connector Connector type (mysql or sqlserver) Text
name Associated connector name Text
pid Worker process ID Integer
state Current connector state Text
err Latest error message Text
last_dbz_offset Last recorded Debezium offset JSON

Possible States:

  • 🔴 stopped - Inactive
  • 🟡 initializing - Starting up
  • 🟠 paused - Temporarily halted
  • 🟢 syncing - Actively polling
  • 🔵 parsing - Processing events
  • 🟣 converting - Transforming data
  • executing - Applying changes
  • 🟤 updating offset - Updating checkpoint
  • 🟨 restarting - Reinitializing
  • dumping memory - JVM is prepaaring to dump memory info in log file
  • unknown - Indeterminate state

synchdb_stats_view

Purpose: Collects connector processing statistics cumulatiely

SELECT * FROM synchdb_stats_view();
Field Description Type
name Associated connector name Text
ddls Number of DDLs operations completed Bigint
dmls Number of DMLs operations completed Bigint
reads Number of READ events completed during initial snapshot stage Bigint
creates Number of CREATES events completed during CDC stage Bigint
updates Number of UPDATES events completed during CDC stage Bigint
deletes Number of DELETES events completed during CDC stage Bigint
bad_events Number of bad events ignored (such as empty events, unsupported DDL events..etc) Bigint
total_events Total number of events processed (including bad_events) Bigint
batches_done Number of batches completed Bigint
avg_batch_size Average batch size (total_events / batches_done) Bigint

synchdb_reset_stats

Purpose: Resets all statistic information of given connector name

SELECT synchdb_reset_stats('mysqlconn');

synchdb_set_offset

Purpose: Configures custom start position

Example for MySQL:

SELECT synchdb_set_offset(
    'mysqlconn', 
    '{"ts_sec":1725644339,"file":"mysql-bin.000004","pos":138466,"row":1,"server_id":223344,"event":2}'
);

Example for SQL Server:

SELECT synchdb_set_offset(
    'sqlserverconn',
    '{"event_serial_no":1,"commit_lsn":"00000100:00000c00:0003","change_lsn":"00000100:00000c00:0002"}'
);

synchdb_log_jvm_meminfo

Purpose: Cause the Java Virtual Machine (JVM) to log the current heap and non-heap usages statistics.

SELECT synchdb_log_jvm_meminfo('mysqlconn');

Check the PostgreSQL log file:

2024-12-09 14:34:21.910 PST [25491] LOG:  Requesting memdump for mysqlconn connector
2024-12-09 14:34:21 WARN  DebeziumRunner:297 - Heap Memory:
2024-12-09 14:34:21 WARN  DebeziumRunner:298 -   Used: 19272600 bytes
2024-12-09 14:34:21 WARN  DebeziumRunner:299 -   Committed: 67108864 bytes
2024-12-09 14:34:21 WARN  DebeziumRunner:300 -   Max: 2147483648 bytes
2024-12-09 14:34:21 WARN  DebeziumRunner:302 - Non-Heap Memory:
2024-12-09 14:34:21 WARN  DebeziumRunner:303 -   Used: 42198864 bytes
2024-12-09 14:34:21 WARN  DebeziumRunner:304 -   Committed: 45023232 bytes
2024-12-09 14:34:21 WARN  DebeziumRunner:305 -   Max: -1 bytes

Snapshot Management

synchdb_restart_connector

Purpose: Reinitializes connector with specified snapshot mode

Snapshot Modes:

Mode Description Use Case
always Full snapshot on every start Complete data verification
initial First-time snapshot only Normal operations
initial_only One-time snapshot, then stop Data migration
no_data Structure only, no data Schema synchronization
never Skip snapshot, stream only Real-time updates
recovery Rebuilds from source Disaster recovery
when_needed Conditional snapshot Automatic recovery

Example:

-- Restart with specific snapshot mode
SELECT synchdb_restart_connector('mysqlconn', 'initial');


📝 Additional Notes:

  • Always validate connector configuration before starting
  • Monitor system resources during snapshot operations
  • Back up PostgreSQL destination database before major operations
  • Test connectivity from PostgreSQL server to source database
  • Ensure source database has required permissions configured
  • Regular monitoring of error logs recommended