PostgreSQL warehouse sync

PostgreSQL ingestion copies the selected tables, then keeps them current with continuous change capture when the source passes replication preflight. Cursor and xmin batch modes remain available when continuous replication is not possible.

If your database runs on Google Cloud SQL, follow the Google Cloud SQL guide for guided setup, Google permissions, networking, and sync options.

Prepare the endpoint

Use the database's direct PostgreSQL endpoint. PgBouncer, transaction poolers, and RDS Proxy do not expose the replication protocol needed for continuous sync. Require SSL and allow the Embrasure connector network path to reach the database port.

Enable continuous replication

• Set wal_level=logical. On Amazon RDS for PostgreSQL, set rds.logical_replication=1 and reboot the instance.
• Keep at least one logical replication slot and one WAL sender available.
• Give the ingestion role CONNECT on the database, USAGE on selected schemas, SELECT on selected tables, and either REPLICATION or the Amazon RDS rds_replication role.
• Give every selected table a stable primary key so updates and deletes can be merged safely.

Connect Embrasure

Create a PostgreSQL source in Embrasure with the direct endpoint and read-only ingestion credentials. Select the source tables and a destination database, then run preflight. Embrasure will explain any missing replication setting, permission, key, or network requirement before starting the sync.

Grant access only to the tables you need

The database login is a PostgreSQL role created by your database administrator. It is separate from your Embrasure account and, for Cloud SQL password connections, your Google account. Embrasure uses the grants of that saved database login to read source data.

You do not need to grant access to every table in a schema. For an existing login, an administrator can grant access to named tables in the intended database. For example, connect to orion before running:

-- Replace these database, schema, role, and table names with your own.
GRANT CONNECT ON DATABASE orion TO embrasure_sync;
GRANT USAGE ON SCHEMA public TO embrasure_sync;
GRANT SELECT ON TABLE public.orders, public.customers TO embrasure_sync;

These statements grant access to an existing role; they do not create it. Repeat the table grant for additional approved tables. This does not grant replication or publication permissions needed for Continuous sync. Avoid GRANT SELECT ON ALL TABLES or broad default privileges when the login should remain restricted.

Cloud SQL setup and connection editing require USAGE and at least one readable table in each selected schema. Table selection and ingestion preflight then check the tables you choose. A schema with no readable tables fails verification even if another selected schema has readable tables.

Row-level security is a separate check from SELECT. Enabling or forcing RLS does not itself exclude a table from discovery. Flow blocks Continuous sync if RLS actually filters that table for the saved login. BYPASSRLS does not grant SELECT; any broader row access must be reviewed by your database administrator. Do not disable RLS just to make discovery succeed.

Replica identity for Embrasure Flow

REPLICA IDENTITY is a table setting, not a replication-slot setting. Flow supports DEFAULT when the complete primary key is selected and every replicated column meets its fixed-width type checks. Eligible types include booleans, integers, floating-point values, dates, time without time zone, timestamps, UUIDs, and enums.

Selected variable-width columns, including text, JSON, arrays, bytea, and numeric, require FULL. Small values or a storage override do not make those columns eligible for DEFAULT. Review the table-specific preflight result; if needed, an administrator can apply:

ALTER TABLE public.orders REPLICA IDENTITY FULL;

Apply this only to the intended table. A primary key, supported types, replication access, and publication permissions are still required. Scheduled sync does not require this Continuous-sync setting.

Correct a saved connection

If discovery finds no tables or the wrong tables:

1. Open Sources and choose Edit connection for the source, or use Edit connection on the Select data screen.
2. Check the displayed instance, database, schemas, and login. A successful connection to the default postgres database does not discover tables stored in another database such as orion.
3. Correct Database, Schemas, or Username. Leave Password blank to keep the saved password, or enter the existing password for the login you selected. This form does not create a PostgreSQL role or grant it access.
4. Select Verify and discover tables. Embrasure verifies the candidate through the saved network path before saving; existing SSH and TLS settings are retained.
5. Review the newly discovered tables and choose what to sync. Saving clears the old catalog and table selection. Discovery does not start ingestion automatically.

Failed verification leaves the saved connection and catalog unchanged. Wait for running discovery to finish before saving an edit. If another user changed the connection, use Reload settings before trying again. Repeated checks can temporarily hit a rate limit; wait as instructed before retrying.

Sources already used by an ingestion sync, including a paused sync, cannot be edited in place through this form. Use Add a separate source for a different database so existing data and checkpoints remain intact. Google IAM connections use reauthorization rather than this password editor. Host, SSH, and TLS settings cannot be changed here.

No tables found means discovery completed without usable tables in the selected scope. Check the database and schema names, schema USAGE, and table SELECT grants before retrying. A saved connection or a completed discovery job is not proof that ingestion has run. Confirm a successful ingestion run, its progress, and actual destination rows separately.

Existing and future tables

Selecting every current table is not always a subscription to future tables. Check the explanation on Select data and again before Start sync:

New tables need read access for the source login and must meet ingestion requirements. PostgreSQL grants on existing tables do not automatically grant access to future tables; ask the administrator to configure default privileges for each role that creates tables if automatic inclusion is intended.

When automatic inclusion is off, discover and explicitly select new tables in a new sync, or ask your agent to help update the ingestion configuration. Catalog discovery alone does not add a table to an existing sync.

Batch fallback

Cursor mode needs a column that increases whenever a row changes. Index that cursor together with the primary key for large tables. xmin can be used when no cursor exists, but it may scan the full source table on each run. Cursor and xmin modes do not capture hard deletes.

Revoke and remove access

Remove the source in Embrasure to stop future synchronization. As a database administrator, revoke the ingestion role's database grants or remove the role when it is no longer needed.

Deleting a continuous sync also removes the replication slot and publication Embrasure created for it on the source. A publication your administrator created is left in place. The sync shows Deleting until cleanup finishes. If Embrasure cannot reach the source, delete the sync again to skip cleanup. Embrasure then lists the replication slot to drop with pg_drop_replication_slot; an abandoned slot keeps retaining WAL. A source connection cannot be removed while continuous syncs still use it, because Embrasure needs its credentials for this cleanup. Delete those syncs first.

Removing a connector does not automatically delete previously imported warehouse data; use the workspace's deletion controls or contact privacy@embrasure.ai, subject to the retention policy.

Column types

Review the column sync report before starting. Supported scalar values use analytical types where possible; JSON and scalar arrays use JSON text, and bytea uses base64 text. Source-specific values may use text to preserve their meaning. Composite and pseudo types are unsupported; primary-key and cursor columns have stricter requirements because they control sync correctness.

Limitations

Initial copies of large tables can take time; continuous changes are buffered until each table catches up. Flow does not silently add new tables to an existing replication configuration; add them through a new or rebuilt ingestion connection. Additive columns can evolve, but dropped, renamed, incompatible, and key columns require review.

What gets stored

Embrasure stores replicated tables and operational metadata: selected tables, checkpoints, row counts, sync status, errors, and preflight results.