PostgreSQL

📘

Whitelist IPs

If your database network-access policy requires whitelisting our IPs first, you can find them here: https://docs.polytomic.com/docs/whitelist-ips.

1. Create a read-only account on your PostgreSQL database. You will supply this account's credentials to Polytomic.

2. In Polytomic, go to Connections → Add Connection → PostgreSQL.

3. Enter host and credential details.

PostgreSQL as a destination

Polytomic can write to PostgreSQL. Please refer to the MySQL as a destination page for more information.

PostgreSQL as a source: CDC replication

When syncing data from PostgreSQL into your data warehouse, it's preferred for Polytomic to utilize CDC (change data capture) replication. This will avoid Polytomic running full table scans to figure out changes since the last sync. Rather, Polytomic will be able to capture changes in real-time without scanning your tables.

To enable this, Polytomic requires the following of your PostgreSQL instance:

• PostgreSQL 10 or later (Polytomic uses the pgoutput plugin).
• wal_level = logical.
  The Postgres wal_level setting must be set to logical. You can determine the current setting by running this query:

my_database=> show wal_level;
 wal_level
-----------
 logical
(1 row)

• Available replication slot.
  Polytomic requires a replication slot for each bulk sync; the same slot will be used for all tables synced as part of that bulk sync configuration. You can inspect current replication slots using the pg_replication_slots system table:

select * from pg_replication_slots;

The total number of replication slots is set using the max_replication_slots Postgres configuration parameter.

• User with REPLICATION property set.
  The REPLICATION property signals that a Postgres user (role) is able to manage replication slots. Polytomic will use this property to create a replication slot for each bulk sync configured. You can set it with the following query:

ALTER ROLE polytomic_user WITH REPLICATION;

• Create a Publication for the tables you wish to replicate.
  Postgres uses a Publication to track changes to one or more tables. Create a Publication for Polytomic that includes the tables you wish to sync incrementally:

CREATE PUBLICATION polytomic FOR TABLE <table>, <table>, ...;

You may also create the publication for all tables, including tables created in the future (note that this means Postgres will track changes for all tables - this can result in increased storage consumption):

CREATE PUBLICATION polytomic FOR ALL TABLES;

Your Publication name can be anything; you’ll enter it in Polytomic's PostgreSQL connection configuration.