tencent cloud

Tencent Cloud DataBuddy

DocumentaçãoTencent Cloud DataBuddyData IntegrationList of Data SourcesPostgreSQL Data Source

PostgreSQL Data Source

Baixar
Modo Foco
Tamanho da Fonte
Última atualização: 2026-10-09 16:09:39
Traduzido por IA

Overview

PostgreSQL is a powerful open-source object-relational database system. DataBuddy data ingestion supports using PostgreSQL as a source, covering scenarios such as offline synchronization, real-time full-database synchronization, and sharded database and table synchronization. This document describes its connection configuration, supported capabilities, prerequisites, and common issues.

Supported Versions

Type
Supported Version
Self-Built PostgreSQL
9.6 / 10.x / 11.x / 12.x / 13.x / 14.x / 15.x / 16.x
Tencent Cloud PostgreSQL
11.x / 12.x / 13.x / 14.x / 15.x / 16.x / 17.x / 18.x
JDBC Driver
postgresql-42.2.26

Read Capability

Capability
Offline read
Real-time full database read
Sharded database and table read
Supported
✓
✓
✓
For a complete comparison of data source capabilities, see Supported Data Sources and Read Capabilities.

Use Limits

Real-Time Sync Limitations

Limit
Description
Logical Replication Version Requirement
Native logical replication is supported only in PostgreSQL 10+.
wal_level
Must be set to logical
Replication Slot
Create a Replication Slot.
Table without primary key
Set REPLICA IDENTITY FULL.
Publication
Create a publication to specify the tables to be synchronized.

Offline Sync Limitations

Limit
Description
View
Supports view reading
Split Key
Only integer fields are supported
Table Name with Special Characters
Table names / field names cannot start with a digit or contain uppercase letters, lowercase letters, or hyphens.

Supported Field Types

For complete field types, see PostgreSQL official documentation.
Field Type
Offline read
Real-time read
SMALLINT / SMALLSERIAL / INTEGER / SERIAL / BIGINT / BIGSERIAL
✅
✅
REAL / DOUBLE PRECISION / NUMERIC / DECIMAL / MONEY
✅
✅
BOOLEAN
✅
✅
CHAR / VARCHAR / TEXT / UUID
✅
✅
JSON / JSONB / ARRAY
✅
✅
DATE / TIME / TIMETZ / TIMESTAMP / TIMESTAMPTZ
✅
✅
INTERVAL
❌
✅
BYTEA / BIT / BIT VARYING
✅
✅
CIDR / INET / MACADDR
❌
❌
POINT / LINE / LSEG / BOX / PATH / POLYGON / CIRCLE
❌
❌

Supported DML/DDL for Real-Time Sync

DML

Operation
Whether Supported
INSERT / DELETE / UPDATE
✅

DDL

Operation
Whether Supported
Add table / Add column / Delete column / Modify column type
✅
Delete table / Clear table / Rename table (RENAME TABLE)
❌
PostgreSQL logical replication has limited support for DDL changes. After Schema changes, you may need to reconfigure the synchronization task and publication.

Prerequisites

1. Configuring Account Permissions

Offline synchronization:
CREATE USER wedata_user WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE your_database TO wedata_user;
GRANT USAGE ON SCHEMA public TO wedata_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO wedata_user;
Real-time synchronization:
CREATE USER wedata_cdc_user WITH REPLICATION PASSWORD 'your_password';
GRANT CONNECT ON DATABASE your_database TO wedata_cdc_user;
GRANT USAGE ON SCHEMA public TO wedata_cdc_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO wedata_cdc_user;

2. Configuring the WAL Level (Real-Time Sync Only)

SHOW wal_level;
For TencentDB for PostgreSQL, set wal_level to logical in Parameter Settings in the console. For self-managed PostgreSQL, modify postgresql.conf:
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
After the modification, restart the database for the changes to take effect.

3. Configuring pg_hba.conf (Real-Time Sync Only)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     wedata_cdc_user 10.0.0.0/8              md5

4. Creating a Publication (Real-Time Sync Only)

CREATE PUBLICATION wedata_pub FOR TABLE table1, table2;
-- Or: CREATE PUBLICATION wedata_pub FOR ALL TABLES;

5. Setting REPLICA IDENTITY (Tables Without Primary Keys)

ALTER TABLE your_table REPLICA IDENTITY FULL;

Creating a Data Source

Operation Steps

1. Log in to the DataBuddy console.
2. Switch to the target region and Workspace at the top.
3. Go to Data Integration > Task Management > Data Source Management (or Platform Management > Data Source Management).
4. Click Add Data Source and select PostgreSQL.
5. Enter the connection configuration and authentication information. For details, see Parameter Description.
6. Click Test Connection (see Data Source Connectivity Test).
7. After the test passes, click Save or Save & Create Task.

Parameter Description

Parameter
Description
Required
Data Source Name
Unique identifier of the data source within the workspace
Yes
JDBC URL
jdbc:postgresql://host:port/database
Yes
Username.
Database connection account
Yes
Password
Database connection password (SSM credential hosting supported)
Yes
Data Source Version
12 / 13 / 14 / 15 / 16
Yes
Example JDBC URL:
jdbc:postgresql://10.0.0.1:5432/mydb
jdbc:postgresql://10.0.0.1:5432/mydb?reWriteBatchedInserts=true
Common URL parameters:
Parameter
Description
reWriteBatchedInserts
Optimize batch insert performance (recommended: true)
ssl / sslmode
SSL connection configuration
connectTimeout
Connection timeout (seconds)

Using in Data Ingestion Tasks

Real-Time Full Database Sync

Configuration Item
Description
Source Table Scope
Specified tables (only selected tables are synchronized; new tables require a restart) / regex matching (specify database name + table name regex; new tables that match the rule are automatically added).
Read Mode
Full + incremental / Incremental only

Offline Single Table Sync

Parameter
Description
Database / Schema / Table
Required. Schema defaults to public. Table supports the range table_[0-99]
Split Key
Supports integer fields only; primary key or index columns recommended.
Filtering Condition
Standard WHERE syntax

Best Practices

Account Permission Minimization: Use a dedicated account and grant only the necessary permissions.
WAL Log Retention: ALTER SYSTEM SET wal_keep_size = '1GB';
Split Key Selection: Primary key or index column.
Network Connectivity: Use a private network connection whenever possible.

FAQs

Q: Real-time sync reports an error that wal_level is not supported?

A: wal_level is not set to logical.
ALTER SYSTEM SET wal_level = 'logical';
-- Restart the database after the modification.

Q: Error indicating the replication slot cannot be created?

A: max_replication_slots is too small:
ALTER SYSTEM SET max_replication_slots = 20;

Q: UPDATE/DELETE cannot be correctly synchronized for tables without primary keys?

A: REPLICA IDENTITY is not set:
ALTER TABLE your_table REPLICA IDENTITY FULL;

Q: Connection refused?

A: pg_hba.conf does not allow connections from the DataBuddy network segment. Add the following to the file:
host    all             wedata_user     10.0.0.0/8     md5
host    replication     wedata_cdc_user 10.0.0.0/8     md5

Q: Tables with special characters in their names cannot be synchronized?

A: Table names and field names cannot start with a digit or contain uppercase letters, lowercase letters, or hyphens.

References

Add Data Source Connection
Data Source Connectivity Test
Supported Data Sources and Read Capabilities
Create a Single-Table Sync Task
Real-Time Whole-Database Multi-Table Task Configuration
Anterior: Oracle Data SourcePróximo: Rest API Data Source

Ajuda e Suporte

Esta página foi útil?

comentários

Search
    Enter keywords to search documentation