PostgreSQL
Key Capabilities
- Data Replication Scope: Syncs data from PostgreSQL tables, views, and materialized views (excludes indexes and access controls).
- Flexible Synchronization Options: Supports Change Data Capture (CDC), system column
xmin
, and traditional sync approaches.
- Scalable Performance: Capable of handling tables of any size, featuring checkpointing and chunked read functionality for enhanced efficiency.
Getting Started Quickly
Set Up a Read-Only Database User
Create a dedicated user in PostgreSQL with read-only privileges on the required schemas and tables.
Configure the Data Source via xGen Interface
- Input the necessary details: host, port, database name, username, and password.
- (Optional) Indicate specific schemas for synchronization (case-sensitive; defaults to
public
).
- Choose an SSL mode (recommended:
require
or verify-ca
for secure encryption).
- Select a replication strategy: standard,
xmin
, or CDC.
- For databases larger than 500GB, CDC is the preferred option.
Advanced Configuration: Change Data Capture (CDC)
When to Opt for CDC
Use CDC when deletion tracking is required, the dataset is very large, or no reliable cursor field exists.
Steps to Set Up CDC
- Assign the
REPLICATION
role to the read-only user.
- Enable logical replication in PostgreSQL settings:
- Modify
wal_level
, max_wal_senders
, and max_replication_slots
.
- Create a logical replication slot using the
pgoutput
plugin.
- Define a publication that includes all tables to be synced.
- Set replication identities for relevant tables.
- Enable CDC in the xGen interface, providing the slot and publication names.
Available Replication Strategies
- CDC (Change Data Capture)
Leverages PostgreSQL’s Write-Ahead Log (WAL) to track changes, including deletions. Ideal for large-scale databases or when deletion logging is essential.
- Xmin-Based Sync
Utilizes the system’s xmin
column for incremental updates. Avoid this method if the database experiences frequent writes or uses non-materialized views.
- Standard Sync
Relies on a user-specified cursor column to perform incremental synchronization.
Securing Connections
- SSL Encryption
Use require
or verify-ca
modes to ensure encrypted data transfer.
- SSH Tunneling
Connection via a bastion (jump) host is supported. Choose either SSH key-based or password-based authentication and provide the corresponding credentials during setup.