Canonical: https://molo17.com/solutions/replicate-to-postgresql/ Markdown mirror: https://molo17.com/solutions/replicate-to-postgresql/index.md Title: Replicate to PostgreSQL in real time with Gluesync | MOLO17 Description: Real-time replication into PostgreSQL from Oracle, SQL Server, MySQL, and MongoDB, with JDBC batch writes and COPY bulk loads for each entity. Start a trial. [Solutions](/solutions/) Replicate to PostgreSQL SOLUTION · REPLICATE TO POSTGRESQL # Real-time data replication to PostgreSQL from your operational databases **Committed changes from your systems of record land in PostgreSQL continuously, through JDBC batches and COPY bulk loads, instead of waiting for the next batch job.** Gluesync by MOLO17 captures changes from Oracle, SQL Server, MySQL, MongoDB, IBM i, and other [heterogeneous sources](/integrations/?target=PostgreSQL#integration-finder) with a dedicated agent per database, then writes them to PostgreSQL through a target agent built on the bundled PostgreSQL JDBC driver. Snapshots seed each table first, CDC follows as changes happen, and Core Hub, the Gluesync control plane, runs every pipeline from one web UI and REST API. [Start a Gluesync trial](/get-gluesync/) [Talk to us](/contacts/) VENDOR COMPATIBILITY ## Battle-tested on every PostgreSQL you deliver to The same Gluesync PostgreSQL agent is tested against each vendor offering below, self-managed or fully managed, so delivery behaves the same wherever PostgreSQL runs. - **PostgreSQL** Source Target Tested - **Amazon Aurora PostgreSQL** Source Target Tested - **Amazon RDS for PostgreSQL** Source Target Tested - **EDB Postgres** Source Target Tested - **Google Cloud SQL PostgreSQL** Source Target Tested - **Microsoft Azure Database for PostgreSQL** Source Target Tested WHO THIS IS FOR ## Teams that need PostgreSQL to reflect operations now, not after the nightly extract - Analytics engineers and application teams who query PostgreSQL directly, with tables that keep the source keys and a reviewed `CREATE TABLE` generated from the source definition when a table does not exist yet - Data platform leads who want one Core Hub for every source feeding PostgreSQL instead of one connector product per source, backed by [best-in-class enterprise support, rated 4.9/5 by customers](/support/#customer-ratings) - Architects who choose the write path per table, optimized batches or native `COPY` bulk load for snapshots and CDC, with agents deployed on Docker, Docker Compose, or Kubernetes next to each source - Engineers running Debezium with a JDBC sink, or scheduled ELT scripts, who want [every source](/integrations/?target=PostgreSQL#integration-finder) delivered to PostgreSQL by one product, with snapshots, restarts, and monitoring built in THE PROBLEM ## PostgreSQL data that arrives in batches is already behind Most PostgreSQL databases that feed reporting or downstream services are loaded by scheduled extracts. A job queries the source, writes files or staging tables, and merges them, so reports reflect the last run and every run adds read load to the production database. Teams looking for real-time PostgreSQL ingestion or a PostgreSQL CDC pipeline usually weigh managed ELT from Fivetran or Airbyte, a Debezium and Kafka stack with a JDBC sink connector, PostgreSQL's native logical replication, which is built for PostgreSQL subscribers, or scripts they maintain themselves. Gluesync addresses that with **per-agent CDC into PostgreSQL**. A source agent reads each database's native change mechanism, Core Hub routes the changes, and the PostgreSQL target agent applies them with JDBC batches or COPY bulk loads. Oracle to PostgreSQL, SQL Server to PostgreSQL, and MongoDB to PostgreSQL pipelines share the same model, the same snapshot, and the same operations. HOW IT WORKS ## How Gluesync writes to PostgreSQL ### The write path: optimized batches and COPY bulk loads The PostgreSQL agent connects through the bundled JDBC driver, with TLS. It is a target agent: it applies the change stream from any Gluesync source agent in real time, in commit order per entity, after a snapshot seeds each table. Gluesync never writes one row at a time, and each entity uses one of two paths. - **Optimized batches, never row by row:** by default, changes are grouped into highly optimized JDBC batches, so each round-trip carries many rows and load on the server stays predictable. The chunk size is configurable on the target agent. - **Native bulk load:** switch it on per entity for the initial snapshot, for ongoing CDC, or both, and rows go through PostgreSQL's own `COPY`, as described below. ### Native bulk load for snapshots and CDC Bulk load is switched on per entity with two independent settings, one for the initial load and one for the ongoing change stream. Both can be changed on an existing entity without recreating it. In each mirroring cycle Core Hub collects every insert, update, and delete and collapses events on the same primary key: an insert followed by a delete is skipped, and consecutive updates become one update. The batch is ingested with fast `COPY` statements into a staging table in the Gluesync schema, so rows never go through single-row SQL. An optional update join or `MERGE` fills columns the source log left out, then deletes keyed on the old key and inserts of the current values are applied in one pass, and the staging table is cleared for the next cycle. ### Snapshot first, then continuous CDC - **Seed, then stream:** each entity loads its full table, then follows the source agent's change mechanism. - **TRUNCATE before snapshot:** the target table is truncated before snapshot inserts by default, which gives a clean reload. The option can be turned off in the agent's advanced settings. - **Pre and post SQL:** SQL commands can run before and after the snapshot and before and after CDC, for session settings, constraint handling, or cleanup. ### Schema, keys, and duplicate rows - **Table creation:** when a target table does not exist, Core Hub generates a `CREATE TABLE` from the source columns, data types, and primary key, converted to PostgreSQL syntax. You review and edit the statement before it runs. - **On duplicate key:** Upsert, the default, overwrites the existing row with the incoming one. Skip keeps the existing row and reports the skipped keys in a notification. Fail stops the entity so someone can review it. - **Gluesync schema:** a dedicated schema holds Gluesync's metadata and bulk staging objects. State tables take an optional name prefix, set in the advanced settings. ### What your PostgreSQL DBA sets up Create a role for Gluesync and the schema it uses, then enter the connection in Core Hub. For the full statements, see the [PostgreSQL target setup guide ↗](https://docs.molo17.com/gluesync/latest/agents/postgresql-target.html). 1. Create a role with read and write access to the destination database and tables. Grant it DDL rights on the schema where Core Hub creates tables. 2. Create a schema for Gluesync and give the role access to it for metadata and bulk staging. 3. In Core Hub, enter the host or IP address, the port (5432 by default), the database name, and the role's username and password. 4. For TLS, enable it and point the agent at the certificate file. ### Architecture around Core Hub Lightweight agents sit close to each source. Core Hub orchestrates them through its web UI and REST APIs and routes changes to the PostgreSQL target agent. A pipeline groups the source agents, the PostgreSQL agent, and the entities they replicate, and Core Hub and agents deploy with Docker, Docker Compose, or Kubernetes. For event-stream topologies, see [CDC streaming](/solutions/cdc-streaming/). [Explore the general CDC streaming architecture →](/solutions/cdc-streaming/) WRITE OPTIONS ## The PostgreSQL target agent: one agent, a write path per entity PostgreSQL has one Gluesync target agent. Every entity writes in optimized batches by default, and native COPY bulk load can be switched on for its snapshot, its CDC, or both. | Agent | Write technique | Versions | Best for | | --- | --- | --- | --- | | [PostgreSQL agent ↗](https://docs.molo17.com/gluesync/latest/agents/postgresql-intro.html) | Bundled JDBC driver writing optimized batches; native bulk load with COPY through a staging table for snapshots and CDC | PostgreSQL 9.0 and later | Any PostgreSQL database fed from Oracle, SQL Server, MySQL, MongoDB, or IBM i. Optimized batches by default, and native `COPY` bulk load for snapshots and CDC, switched on per entity. | SOURCES AND TOPOLOGIES ## Feed PostgreSQL from the systems of record you already run Any Gluesync source agent can feed PostgreSQL. Open the [integrations finder with PostgreSQL pre-selected](/integrations/?target=PostgreSQL#integration-finder) to see every source that pairs with it. One Core Hub runs Oracle to PostgreSQL, SQL Server to PostgreSQL, and MongoDB to PostgreSQL pipelines side by side, with the same snapshot, monitoring, and write settings for each. Gluesync keeps pace with your change volume at any scale, and [MOLO17 Professional Services](/solutions/professional-services/) can help choose the write path for each table. - Oracle to PostgreSQL from the redo logs through LogMiner or XStream: see [Oracle CDC](/solutions/oracle-cdc/) - SQL Server to PostgreSQL through Change Data Capture or Change Tracking: see [SQL Server CDC](/solutions/sql-server-cdc/) - MySQL to PostgreSQL from the binary log: see [MySQL CDC](/solutions/mysql-cdc/) - MongoDB to PostgreSQL from Change Streams: see [MongoDB CDC](/solutions/mongodb-cdc/) FAIR, HIGH-LEVEL COMPARISON ## Where Gluesync fits among PostgreSQL ingestion approaches | Approach | What buyers usually get | Where Gluesync fits | | --- | --- | --- | | Fivetran and Airbyte | Managed or open-source ELT with PostgreSQL destinations and large connector catalogs. Syncs run on a schedule, and change capture varies by source connector | Log-based capture for each source engine and continuous writes into PostgreSQL, operated from one Core Hub with MOLO17 enterprise support | | Debezium with Kafka Connect and a JDBC sink | Open-source capture into Kafka topics, then a JDBC sink that writes rows into PostgreSQL. You run Connect, offsets, and schema changes, and usually a merge step into current-state tables | Changes applied to PostgreSQL tables by key, with no Kafka cluster in the path. Read the [Debezium alternative](/solutions/debezium-alternative/) comparison | | AWS DMS, Qlik Replicate, and Striim | Commercial replication services with PostgreSQL among their targets. Capture and load options differ by product and version | Agents deployed next to each source, native capture per engine, and COPY bulk cycles into PostgreSQL, all run from one Core Hub | | PostgreSQL native logical replication | Built into PostgreSQL with publications and subscriptions. Subscribers are PostgreSQL instances, so other sources need separate tooling | Gluesync writes into PostgreSQL from Oracle, SQL Server, MySQL, MongoDB, and IBM i under one pipeline model, not only from another PostgreSQL server | | Scheduled ELT scripts | Full control. Your team owns the extract queries, staging files, MERGE logic, retries, and the load each run puts on the source database | Log-based capture, staged bulk cycles, snapshot restarts, and Core Hub monitoring, with no pipeline code to maintain. Read [batch ETL vs real-time data replication](/blog/batch-etl-vs-real-time-data-replication/) | RELATED CONTENT ## PostgreSQL replication reading and related pages - [Batch ETL vs real-time data replication: how to choose](/blog/batch-etl-vs-real-time-data-replication/) - [PostgreSQL logical replication: slots, WAL and pgoutput](/blog/postgresql-logical-replication-slots-wal-pgoutput/) - [SQL Server to PostgreSQL on AWS: SoftPI webinar](/blog/webinar-on-real-time-data-replication-from-ms-sql-to-postgresql-on-aws/) - [PostgreSQL CDC: capture from the write-ahead log](/solutions/postgresql-cdc/) - [Cloud migration: snapshot, then CDC until cutover](/solutions/cloud-migration/) - [Database offload: move reads off the primary](/solutions/database-offload/) - [PostgreSQL target setup guide ↗](https://docs.molo17.com/gluesync/latest/agents/postgresql-target.html) - [Bulk load: staging, COPY, and apply phases ↗](https://docs.molo17.com/gluesync/latest/core-hub/bulk-load.html) FAQ ## PostgreSQL replication questions What does replicating to PostgreSQL with Gluesync involve? A Gluesync source agent captures committed changes from your database through its native change mechanism, Core Hub routes them, and the PostgreSQL target agent applies them to PostgreSQL tables continuously after a snapshot seeds each table. How does Gluesync load data into PostgreSQL? Through the bundled PostgreSQL JDBC driver, in highly optimized batches with a configurable chunk size. Gluesync never writes one row at a time, and native COPY bulk load is available for snapshots and CDC. Which sources can replicate to PostgreSQL? Any Gluesync source agent, including Oracle, SQL Server, MySQL, MariaDB, IBM i, Db2 for LUW, SAP HANA, MongoDB, and Couchbase. The integrations finder on our website lists every pairing. Does Gluesync upsert or MERGE rows in PostgreSQL? Upsert is the default On duplicate key setting, so an incoming row overwrites the existing row with the same key. In bulk mode, Core Hub combines changes per primary key, deletes the changed keys, and inserts their current values, with an optional MERGE that fills columns the source log omitted. What does the PostgreSQL side need to set up? A role with read and write access to the destination tables, DDL rights on the schema where Core Hub creates tables, and an existing schema for Gluesync's metadata that the role can use. TLS is enabled with a certificate file when the server requires it. Can Gluesync create the tables in PostgreSQL? Yes. When a target table is missing, Core Hub generates a CREATE TABLE from the source definition, converted to PostgreSQL syntax, and you review it before it runs. Which PostgreSQL versions are supported? PostgreSQL 9.0 and later. The agent connects over TLS. Does Gluesync bulk load into PostgreSQL? Yes, for the initial snapshot and for ongoing CDC, with a separate switch for each on every entity. Changes are collapsed per primary key, ingested with COPY into a staging table in the Gluesync schema, and applied to the destination table in one pass. REPLICATE TO A TARGET ## Other targets Gluesync delivers to - [Replicate to Aerospike](/solutions/replicate-to-aerospike/) - [Replicate to DynamoDB](/solutions/replicate-to-dynamodb/) - [Replicate to Redshift](/solutions/replicate-to-redshift/) - [Replicate to Amazon S3 & S3-compatible](/solutions/replicate-to-amazon-s3/) - [Replicate to Cassandra](/solutions/replicate-to-cassandra/) - [Replicate to Kafka](/solutions/replicate-to-kafka/) - [Replicate to Cosmos DB](/solutions/replicate-to-cosmos-db/) - [Replicate to Azure Data Lake](/solutions/replicate-to-azure-data-lake/) - [Replicate to ClickHouse](/solutions/replicate-to-clickhouse/) - [Replicate to CockroachDB](/solutions/replicate-to-cockroachdb/) - [Replicate to Couchbase](/solutions/replicate-to-couchbase/) - [Replicate to file stores](/solutions/replicate-to-file-stores/) - [Replicate to BigQuery](/solutions/replicate-to-bigquery/) - [Replicate to Google Cloud Storage](/solutions/replicate-to-google-cloud-storage/) - [Replicate to Google Pub/Sub](/solutions/replicate-to-google-pubsub/) - [Replicate to GridGain](/solutions/replicate-to-gridgain/) - [Replicate to Db2](/solutions/replicate-to-db2/) - [Replicate to Informix](/solutions/replicate-to-informix/) - [Replicate to MariaDB](/solutions/replicate-to-mariadb/) - [Replicate to SQL Server](/solutions/replicate-to-sql-server/) - [Replicate to MongoDB](/solutions/replicate-to-mongodb/) - [Replicate to MySQL](/solutions/replicate-to-mysql/) - [Replicate to Oracle](/solutions/replicate-to-oracle/) - [Replicate to RavenDB](/solutions/replicate-to-ravendb/) - [Replicate to Redis](/solutions/replicate-to-redis/) - [Replicate to SAP ASE](/solutions/replicate-to-sap-ase/) - [Replicate to SAP HANA](/solutions/replicate-to-sap-hana/) - [Replicate to ScyllaDB](/solutions/replicate-to-scylladb/) - [Replicate to SingleStore](/solutions/replicate-to-singlestore/) - [Replicate to Snowflake](/solutions/replicate-to-snowflake/) - [Replicate to Solace PubSub+](/solutions/replicate-to-solace/) - [Replicate to Vertica](/solutions/replicate-to-vertica/) - [Replicate to YugabyteDB](/solutions/replicate-to-yugabytedb/) ## Evaluate Gluesync with your PostgreSQL databases Start a trial on your infrastructure, or talk to MOLO17 about your sources, the write path for each table, and the snapshot plan. [Start a Gluesync trial](/get-gluesync/) [Talk to MOLO17](/contacts/)