PostgreSQL
PostgreSQL is the world's most advanced open-source relational database, renowned for its robustness, extensibility, and standards compliance, making it the preferred choice for mission-critical applications.
Supported Versions and Architectures
- Versions: PostgreSQL 9.x to 16.x
- Architectures: Standalone or replication
Supported Data Types
| Category | Data Types |
|---|---|
| Strings & Text | character, character varying, text |
| Numeric | integer, bigint, smallint, numeric, real, double precision |
| Binary | bytea |
| Bit | bit, bit varying |
| Boolean | boolean |
| Date & Time | timestamp without time zone, timestamp with time zone, date, time without time zone, time with time zone, interval |
| Spatial Data | geometry, point, polygon, circle, path, box, line, lseg |
| Network Types | inet, cidr, macaddr |
| Identifier | uuid, oid, regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, regdictionary |
| Text Search | tsvector, tsquery |
| Others | xml, json, array |
Limitations
- Partitioned tables: Incremental sync for partitioned parent tables requires PostgreSQL 13+ with pgoutput plugin
- WAL accumulation: Multiple replication slots can cause WAL log buildup and disk pressure
Quick Setup Guide
1. Create Database User
CREATE USER xpipes WITH PASSWORD 'your_password';
2. Grant Permissions
- Full sync only
- Full + incremental sync
\c your_database
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xpipes;
GRANT USAGE ON SCHEMA public TO xpipes;
\c your_database
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xpipes;
GRANT USAGE ON SCHEMA public TO xpipes;
ALTER USER xpipes REPLICATION;
3. Configure for Incremental Sync
For incremental data capture, configure replica identity:
ALTER TABLE your_schema.your_table REPLICA IDENTITY FULL;
4. Choose CDC Plugin
- wal2json (Recommended): Easy setup, requires primary keys
- pgoutput: Built-in, no installation needed (PostgreSQL 10+)
5. Configure PostgreSQL
Add to postgresql.conf:
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
Add to pg_hba.conf:
host replication xpipes 0.0.0.0/0 md5
Restart PostgreSQL after configuration changes.