PostgreSQL

Overview

PostgreSQL is a powerful, open-source relational database known for its reliability, extensibility, and standards compliance. In Zenskar, you can use PostgreSQL as a data source to import transactional, usage, or customer data for metering, billing, and analytical reporting.

Prerequisites

Create a dedicated read-only PostgreSQL user for replicating data. Alternatively, you can use an existing user:

CREATE USER <user_name> PASSWORD 'your_password_here';

Provide the user read-only access to relevant schemas and tables:

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

📖

You must re-run the above command for each schema from which you expect to replicate data.

🚧

You must allow inbound traffic from Zenskar IP addresses .

Set up a PostgreSQL data-source connector via Zenskar dashboard

1. Log into your Zenskar dashboard.
2. In the left side bar, click Usage > Data Sources.
3. In the top-right corner, click + ADD DATA SOURCE.
4. On the Add New Data Source page, configure the PostgreSQL connector.
5. Click on the SAVE SOURCE button.

Connector configuration

General configuration

Connector configuration

Set up a PostgreSQL data-source connector via API

💡

The connector_config object is the only part of the request that differs across connector types (such as BigQuery, Snowflake, or Redshift). The create data-source connector API reference provides a generic overview, while this document explains the PostgreSQL-specific structure of connector_config.

Request example

curl --location 'https://api.zenskar.com/datasources' \
  -H 'x-api-key: <your-api-key>' \
  -H 'apiversion: 20240301' \
  -H 'Content-Type: application/json' \
  -d '{
    "name":"test_postgresql",
    "connector_type":"Postgres",
    "destination":"Postgres",
    "status":"active",
    "connector_config":{
        "port":5432,
        "schemas":["public"],
        "ssl":true,
        "ssl_mode":{
            "mode":"require",
            "client_certificate":"certificate",
            "client_key":"key"},
        "database":"db",
        "username":"zenskar",
        "password":"password",
        "host":"acme"},
        "source_definition_id":"decd3333-5647-4c0b-adf4-da0e75f5a750",
        "remote_conn":true
  }'

PostgreSQL-specific connector configuration

The connector_config object contains configuration fields specific to the PostgreSQL data source.

Addendum

SSL modes in PostgreSQL

❗️

If SSL mode is set to Require, the connection will fail if the PostgreSQL instance doesn’t support SSL.

Data replication

Change Data Capture (CDC) replication

Zenskar uses logical replication of PostgreSQL’s write-ahead log (WAL) to incrementally capture data changes, including deletions.

You should configure the PostgreSQL data source with CDC when:

• You need a record of deletions.
• Your database is large (500 GB or more).
• Your table has a primary key but lacks a suitable incremental cursor field (for example, updated_at).

⚠️

CDC must connect to the primary database. Configuring CDC on a replica host will result in failures.

Set up CDC

1. Grant REPLICATION privileges to the read-only user:

   SQL

   ALTER USER <user_name> REPLICATION;

2. Enable logical replication on your PostgreSQL database.

   • For self-hosted or VM environments (bare metal, EC2, GCE, Docker): Edit the postgresql.conf file:

     Parameter	Description	Set value to
     wal_level	Type of logging in the WAL	logical
     max_wal_senders	Maximum number of WAL sender processes	Minimum 1
     max_replication_slots	Maximum number of replication slots allowed	1 (or higher if other services read from WAL)

   • For AWS RDS or Aurora:

     • Go to the Configuration tab of your DB cluster.
     • Locate your cluster parameter group and set rds.logical_replication = 1.
     • Restart your instance manually or during the next maintenance window.

   • For Azure Database for PostgreSQL:

     • Change the replication mode to logical from the Azure portal, or run:

       Bash

       az postgres server configuration set --resource-group <group> \
         --server-name <server> \
         --name azure.replication_support \
         --value logical
       az postgres server restart --resource-group <group> --name <server>

3. Create a dedicated replication slot for Zenskar using the pgoutput plugin:

   SQL

   SELECT pg_create_logical_replication_slot('zenskar_slot', 'pgoutput');

   The output will include the slot name to be filled in the Zenskar setup page.

4. Define replication identities for all tables you want to replicate:

   SQL

   ALTER TABLE <table_name> REPLICA IDENTITY DEFAULT;

   Use FULL for tables with large or TOASTable fields:

   SQL

   ALTER TABLE <table_name> REPLICA IDENTITY FULL;

5. Create a publication including all tables to be replicated:

   SQL

   CREATE PUBLICATION zenskar_publication FOR TABLE <tbl1, tbl2, tbl3>;

   The publication name is customizable.

💡

The Zenskar UI allows table selection for CDC, but if a table is not part of the publication, it will not be replicated. If a table lacks a replication identity, Zenskar can create it automatically if the user has sufficient permissions.

Xmin replication

The xmin method is a cursorless replication mode that tracks inserts and updates using PostgreSQL’s internal xmin system column.

Use xmin when:

• There’s no suitable cursor field (for example, updated_at).
• You want to replace a full-refresh sync.
• You are replicating PostgreSQL tables smaller than 500 GB.
• You are not replicating non-materialized views (these are unsupported by xmin).