PostgreSQL

Documentation Index

Fetch the complete documentation index at: https://docs2.zenskar.com/llms.txt

Use this file to discover all available pages before exploring further.

​

Overview

​

Prerequisites

CREATE USER <user_name> PASSWORD 'your_password_here';

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>;

​

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

​

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

​

Addendum

​

SSL modes in PostgreSQL

​

Data replication

​

Change Data Capture (CDC) replication

• 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).

​

Set up CDC

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

   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:
   bash

   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:
   bash

   ALTER TABLE <table_name> REPLICA IDENTITY DEFAULT;
   
   Use FULL for tables with large or TOASTable fields:
   bash

   ALTER TABLE <table_name> REPLICA IDENTITY FULL;
   
5. Create a publication including all tables to be replicated:
   bash

   CREATE PUBLICATION zenskar_publication FOR TABLE <tbl1, tbl2, tbl3>;
   
   The publication name is customizable.

​

Xmin replication

• 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).