Search docs.molo17.com
    Configuration guide Connectors

    Set up a PostgreSQL source with WAL change data capture

    The PostgreSQL agent reads changes from the write-ahead log (WAL) through logical decoding. It creates one replication slot for each entity and decodes it with the wal2json output plugin. This guide prepares the server, the user and the tables, creates the source connection, runs a snapshot and live changes into a MySQL target, and shows the checks and the failures you are most likely to meet: a wrong wal_level, a user without the REPLICATION attribute, no free replication slot, a table with no replica identity, and a slot that was dropped while the entity was paused.

    In this guide

    You will check and set wal_level, max_replication_slots and max_wal_senders, make sure the wal2json plugin is available, and create a user with only the privileges Gluesync needs. You will decide the REPLICA IDENTITY of your tables, create the PostgreSQL source connection in the Control Plane, add an entity, and start it with a full copy followed by live changes. You will read the replication slot on the server to see what Gluesync created, and you will recover from the failures that stop CDC.

    Quick information

    Goal

    Replicate a PostgreSQL table with a snapshot followed by change data capture (CDC), and know how to keep the replication slot healthy

    Audience

    Data engineers and database administrators who own a PostgreSQL server

    Level

    Intermediate

    Time required

    About 45 minutes

    Area

    PostgreSQL → logical replication; Control Plane → Pipelines → PostgreSQL CDC agent → Base settings; entity → start dialog

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You have a PostgreSQL server, version 10 or later, and you can run SQL on it as a superuser. This guide was tested with PostgreSQL 16;

    2. You can restart the PostgreSQL service. A change of wal_level needs a restart;

    3. The wal2json plugin is installed on the server. Step 1 shows how to check it;

    4. Core Hub can open a connection to the server, and pg_hba.conf allows the Core Hub host to connect with the Gluesync user;

    5. You have a target to write to. This guide uses a MySQL database and its connection details: any target works, and the steps for the source are the same;

    6. You can sign in to the Control Plane with a role that can create pipelines;

    7. You have a source table with a primary key. This guide uses public.orders with 100,000 rows (id bigint primary key, customer varchar(50), status varchar(20), amount numeric(10,2), created_at timestamp).

    The messages quoted in this guide were captured in the Control Plane and in the Core Hub log with PostgreSQL 16.4 as the source and MySQL 8.0 as the target. The options of the agent are listed in the PostgreSQL CDC reference.

    Guide steps

    Check the server settings and the plugin

    SQL

    Connect to the source database as a superuser and read the three settings that logical replication depends on:

    SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;
SELECT version();

    1. wal_level must be logical. If it is not, set it and restart the PostgreSQL service. The value replica is the default of many distributions:

      ALTER SYSTEM SET wal_level = logical;

    2. Gluesync creates one replication slot for each entity that follows changes. Set max_replication_slots to at least the number of entities, plus the slots that other tools use. Keep max_wal_senders a little above the number of slots, so that orphan connections do not use them all. Both defaults are 10. Both changes need a restart:

      ALTER SYSTEM SET max_replication_slots = 20;
ALTER SYSTEM SET max_wal_senders = 25;

    3. Restart PostgreSQL, then read the settings again;

    4. Check that the plugin is installed. On a Linux host, look for the library:

      find / -name "wal2json.so" 2>/dev/null

      Install it with your package manager if it is missing, for example postgresql-<version>-wal2json on Debian and Ubuntu. For an easier check, create and drop a test slot as a user that can replicate (the user of step 2 can do it):

      SELECT pg_create_logical_replication_slot('gs_probe', 'wal2json');
SELECT pg_drop_replication_slot('gs_probe');
    Expected result

    SHOW wal_level returns logical, and the test slot is created and dropped without an error. Drop the test slot at once: an unused slot keeps the WAL of the server.

    On the PostgreSQL releases that restrict output plugins, the test slot fails with library "wal2json" may not be used as an output plugin. Add wal2json to output_plugin_libraries as described in PostgreSQL Troubleshooting and run the test again.
    Create the Gluesync user

    SQL

    Gluesync does not need a superuser. It needs the REPLICATION attribute to read the WAL, and SELECT on the tables it copies. Replace the password:

    CREATE USER gluesync WITH REPLICATION LOGIN PASSWORD '<a strong password>';
GRANT USAGE ON SCHEMA public TO gluesync;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gluesync;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO gluesync;

    The last statement gives the user SELECT on the tables you create later too. It applies to tables created by the role that runs it. Repeat the grants for every other schema you replicate.

    Check the user from the machine where Core Hub runs, or from any host that the same pg_hba.conf rule covers:

    psql "postgresql://gluesync@<host>:5432/<database>" -c "SELECT rolreplication FROM pg_roles WHERE rolname = current_user"
    Expected result

    The query returns t. A user without the attribute can sign in and can read the tables, and the connection test of the Control Plane still passes. The failure shows later, when the entity starts, see the troubleshooting table.

    pg_hba.conf needs an entry for the database and the Core Hub address, for example host <database> gluesync <core hub address>/32 scram-sha-256. In the test, the connection that Gluesync opens for logical decoding matched the entry for the database name. The reference page also lists replication entries. If you use a stricter file, add them too.
    Choose the replica identity of the tables

    SQL

    The replica identity decides which columns of the old row PostgreSQL writes to the WAL for an UPDATE or a DELETE. Check it for each table you replicate:

    SELECT c.relname, c.relreplident
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind = 'r'
ORDER BY c.relname;

    d is DEFAULT, f is FULL, n is NOTHING, and i is an index.

    1. A table with a primary key works with DEFAULT. A DELETE carries the key, and Gluesync deletes the row on the target. Change nothing;

    2. A table without a primary key has no old row in the WAL with DEFAULT. Set FULL on it:

      ALTER TABLE public.<table> REPLICA IDENTITY FULL;

    3. If you want the before image of every changed row, for example to see the old values in a change, set FULL on that table too. The table owner or a superuser runs the statement. It writes more WAL for each update.

    To see the difference, decode a few changes yourself with a test slot, then drop it:

    SELECT pg_create_logical_replication_slot('gs_probe', 'wal2json');
-- change a row of a keyless table, then read:
SELECT data FROM pg_logical_slot_get_changes('gs_probe', NULL, NULL, 'format-version', '1');
SELECT pg_drop_replication_slot('gs_probe');
    Expected result

    For an UPDATE or a DELETE on a table without a primary key and with DEFAULT identity, the server prints WARNING: table "<table>" without primary key or replica identity is nothing, and wal2json returns no change for that statement. Gluesync cannot replicate what the log does not contain, so the change never reaches the target and no error is raised. With FULL, the UPDATE carries the old values in oldkeys for every column, and the DELETE carries the whole old row.

    Gluesync does not change the replica identity of your tables. Set it yourself, before you start the entity. For tables without a primary key, also choose the keys that Gluesync uses on the target, see Choose keys for tables without a primary key.
    Create the PostgreSQL source connection

    UI

    1. In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

    2. In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings:

      • Host name / Address: the DNS name or address of the server, as Core Hub sees it;

      • Port: 5432, or the port of your server;

      • Database name: the database that holds the tables. A slot reads one database;

      • Username and Password: the user of step 2;

    3. Keep Enable Recursion Protection off, unless the same database is also a target of a bidirectional setup, see Set up bidirectional replication with recursion protection. Keep WAL sender timeout in seconds (supported from PostgreSQL 12+) at 60;

    4. Select Test and continue. The source appears with a green check mark. Select Next.

    Agents setup wizard with the PostgreSQL CDC agent
    Figure 1. The Base settings of the PostgreSQL CDC agent.

    1. In 2. Setup target(s), choose your target agent and fill its connection. For the MySQL target of this guide, enter the host, 3306, the database, the user and the password, then turn on Allow public key retrieval and Trust server certificate if your lab server uses the default self-signed certificate. Select Test and continue, then Next;

    2. Enter a Pipeline name and select Finish.

    Expected result

    The pipeline appears in the HEALTHY group of the pipeline list with the PostgreSQL and the target icons, and the entity table is empty. No slot exists yet: Gluesync creates it when an entity is started. The connection test checks the sign-in only. It does not check wal_level, the REPLICATION attribute or the free slots.

    When you point a second pipeline at the same host, port and database, the Control Plane shows the notice Connection shared between pipelines: a change made to the connection applies to all the pipelines that use it.
    Add the entity and create the target table

    UI

    1. In the pipeline, select Entities. The Add entities page opens with the Object browser. Expand the schema public and tick the table orders. The Field mapping dialog opens. The browser lists every table and view the user can read. PostGIS helper tables show up too;

    2. Under Target, choose the Target Schema, then type orders in Target Table and choose Create table "orders". The dialog shows New table orders will be created on confirm. and the proposed target types;

    3. Select Confirm. The Create table orders dialog shows the read-only SQL statement. Read it, then select Create table orders to run it on the target;

    4. Back on the Add entities page, select Confirm and continue.

    Field mapping dialog with the source table public.orders and the target table demo.orders
    Figure 2. The Field mapping of the table: PostgreSQL types on the left, the proposed MySQL types on the right.
    Expected result

    The entity appears in the pipeline on Hold as public.orders. With a MySQL target, the types are bigint, varchar(50), varchar(20), numeric(10, 2) and datetime(6), and the primary key stays on id.

    Data types follow the target. The agent reads numeric with every digit and keeps the offset of a timetz and of a timestamptz. It reads NaN and the infinities of real and double precision, BC dates and years after 9999, hstore as text, ranges, and arrays. A target that cannot hold a type shows it in the Field mapping dialog, where you change the target type or add a conversion. See Fix target length and truncation errors.
    Start the entity with a full copy and check the slot

    UI + SQL

    1. On the row of the entity, select the play button. The Sync changes (CDC) dialog opens;

    2. Under Start from keep Full copy, Recommended for the first start. It copies all the rows, then follows the changes. The other choices are Where it stopped, which is not available before the first run, and Now, which skips what changed before the start;

    3. Under Data already in the target keep Keep and merge, and under Speed keep Standard;

    4. Select Start entity.

    Sync changes CDC dialog for public.orders with Start from Full copy selected
    Figure 3. The start dialog of a new entity, with Full copy and Keep and merge.

    While the copy runs, read the replication slot on the source and the row count on the target:

    SELECT slot_name, plugin, active, wal_status FROM pg_replication_slots;
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;
    -- on the target
SELECT count(*) FROM demo.orders;
    Expected result

    pg_replication_slots shows one slot named gs_<entity id>_slot, with the plugin wal2json, active set to t and wal_status reserved. The target count grows until it equals the source count, 100000. When the copy ends, the entity is Active and the lag of the slot is a few bytes (for example 56 bytes).

    Pipeline orders-to-mysql with the entity public.orders in the Active group
    Figure 4. The entity is active and follows the changes.

    An entity that is snapshot-only never gets a slot, and a slot that an entity does not use is dropped when you delete the entity. A slot that nothing reads keeps WAL on the source server, so drop the slots of the entities you no longer use.

    Follow live changes and check the three operations

    SQL

    On the source, insert, update and delete one row each:

    INSERT INTO public.orders VALUES (100001, 'customer-new', 'new', 19.90, now());
UPDATE public.orders SET status = 'paid', amount = 25.00 WHERE id = 1;
DELETE FROM public.orders WHERE id = 2;

    After a few seconds, read the three keys on the target:

    SELECT id, customer, status, amount FROM demo.orders WHERE id IN (1, 2, 100001) ORDER BY id;
SELECT count(*) FROM demo.orders;
    Expected result

    Row 1 shows paid and 25.00, row 100001 exists, and row 2 is gone. The count is 100000: one row was added and one was deleted. The delete worked with the default replica identity because the table has a primary key.

    Pause, resume and keep the slot

    UI + SQL

    Pausing an entity keeps its replication slot, so the source keeps the WAL that the entity has not read. Resuming with Where it stopped continues from the last checkpoint and loses nothing.

    1. Select the pause button of the entity, then Pause entity;

    2. Check the slot. It stays, with active set to f:

      SELECT slot_name, active, wal_status FROM pg_replication_slots;

    3. Change a row on the source. Select the play button, choose Where it stopped and select Start entity.

    Expected result

    The slot is active again and the change reaches the target. A long pause makes the source keep a lot of WAL. Watch the size of the pg_wal directory and the lag query of step 6. If you pause for days, plan the disk for it, or delete the entity and start it again with a full copy later.

    If somebody drops the slot while the entity is paused, Where it stopped does not warn you. Gluesync creates a new slot at the start, and the changes written to the source in the meantime are never replicated. In the test, a row inserted during the pause was missing on the target after the resume, and no error appeared in the Control Plane or in the Core Hub log. Start the entity again with Full copy and Keep and merge to realign the target: the row appeared and the count matched the source again. Check the slots before you resume if you share the server with other tools.
    Tune the speed

    UI

    1. In the start dialog, under Speed select Faster or Custom to raise the number of readers and of writers. More lanes copy faster and put more load on both databases. The dialog shows the lanes between Source, Gluesync and Target;

    2. For the source, the number of parallel queries of the snapshot is the Query parallelism setting, 4 by default. Open the source from the pipeline list, select Advanced settings and add the property if you want another value, see PostgreSQL source advanced settings;

    3. For live changes, the agent merges the consecutive commits that are already waiting in the stream, so many small transactions cost less than one emission each. Truncates and chunked transactions are never merged;

    4. If the target supports bulk operations, select Custom and switch the Writing method to Bulk load for the copy and for live changes. See Tune replication throughput and Bulk load.

    The slot is confirmed only up to the last commit that the target has stored, so a slow target shows as lag on the slot. If the lag keeps growing, the target is the bottleneck.

    Troubleshooting

    Symptom or message Cause Fix

    FATAL: permission denied to start WAL sender with the detail Only roles with the REPLICATION attribute may start a WAL sender process.

    The Gluesync user has no REPLICATION attribute. The connection test passed, because it signs in only. The entity fails when it opens the replication connection.

    Run ALTER ROLE gluesync REPLICATION; and start the entity again. The same user in SQL gets permission denied to use replication slots when it creates a slot.

    ERROR: logical decoding requires wal_level >= logical

    wal_level is replica or minimal, so the server cannot decode the log.

    Run ALTER SYSTEM SET wal_level = logical;, restart PostgreSQL and start the entity again.

    PostgreSQL does not start and its log shows FATAL: logical replication slot "gs_<entity id>_slot" exists, but wal_level < logical

    wal_level was lowered while a logical slot still exists. The server refuses to start.

    Set wal_level = logical in postgresql.auto.conf or postgresql.conf while the service is stopped, then start it. Do not drop the slot to get around it: the entity then loses its position.

    ERROR: all replication slots are in use with the hint Free one or increase max_replication_slots.

    The server has no free slot for a new entity.

    List the slots with SELECT slot_name, active FROM pg_replication_slots;, drop the ones that nothing uses with SELECT pg_drop_replication_slot('<name>');, or raise max_replication_slots and restart. Keep max_wal_senders above it.

    SQL Error: Code 0, State 42501, Message ERROR: permission denied for table orders

    The user cannot read the table. The snapshot reads it with a normal query.

    Run GRANT SELECT ON public.orders TO gluesync; and start the entity again.

    ERROR: library "wal2json" may not be used as an output plugin

    The server restricts the output plugins and wal2json is not in output_plugin_libraries.

    See PostgreSQL Troubleshooting. Extend the list, do not replace it.

    Updates and deletes of a table do not reach the target, and no error appears. Inserts do.

    The table has no primary key and its replica identity is DEFAULT. The server logs WARNING: table "<table>" without primary key or replica identity is nothing.

    Run ALTER TABLE <table> REPLICA IDENTITY FULL;. Changes made before that are not in the log, so realign the target with a full copy.

    Changes written while an entity was paused are missing after the resume

    The slot was dropped during the pause. Gluesync created a new one at the start, from the current position.

    Start the entity with Full copy and Keep and merge. Check pg_replication_slots before a resume.

    The disk of the source server fills up and pg_wal grows

    A slot is not advancing: the entity is paused, in error, or the target is slow, or a slot is left from a deleted pipeline.

    Check active, wal_status and the lag query of step 6. Resume or fix the entity, or drop the slot that nothing reads.
    Sync changes CDC dialog for public.orders with the notification Entity Upsert Failed and the message FATAL permission denied to start WAL sender
    Figure 5. A start that fails because the user has no REPLICATION attribute: the dialog stays open and the notification shows the server message.

    Related guides