Overview

The OLake Postgres Source connector supports multiple synchronization modes. It offers features like parallel chunking, checkpointing, and automatic resume for failed full loads. This connector can be used within the OLake UI or run locally via Docker for open-source workflows.

Sync Modes Supported

• Full Refresh
• Full Refresh + CDC
• CDC Only
• Full Refresh + Incremental

Prerequisites

Version Prerequisites

A Postgres server version 9.5 or above (for Full Load and Incremental(Bookmark/cursor)).

CDC Prerequisite

To set up PostgreSQL, follow the appropriate setup guide for your PostgreSQL environment:

• Aurora Postgres – CDC Setup Guide
• Amazon RDS Postgres – CDC Setup Guide
• Postgres via Docker Compose – Local Setup
• Azure Database for Postgres – CDC Setup Guide
• Google Cloud SQL for Postgres – CDC Setup Guide
• Generic Postgres – CDC Setup Guide

Connection Prerequisites

• Read access to the tables for the PostgreSQL user.
• For Supabase related setup, refer the Connecting to Supabase section.

After initial Prerequisites are fulfilled, the configurations for PostgreSQL can be configured.

---

Configuration

Use Olake UI for Postgres

1. Navigate to the Source Configuration Page

1. Complete the OLake UI Setup Guide
2. After logging in to the OlakeUI, select the Sources tab from the left sidebar.
3. Click Create Source on the top right corner.
4. Select Postgres from the connector dropdown
5. Provide a name for this source.

2. Provide Configuration Details

• Enter Postgres credentials.
  

Field	Description	Example Value
Postgres Host required	Hostname or IP address of the Postgres database server.	pg-host
Postgres Port required	TCP port on which the Postgres listener is accepting connections.	5432
Database Name required	The name of the target database to connect to.	olake-db
Password required	The password corresponding to the provided username for authentication.	pgpwd
Username required	Database user used to authenticate the connection.	pg-user
Update Method	Standalone : If only Full refresh and Incremental will be used. CDC : If CDC related modes will be used.	CDC
Initial Wait Time	Idle timeout for WAL log reading	120
Max Threads	Maximum number of worker threads the connector can spin up for parallel tasks.	10
Replication Slot required for CDC	Slot to retain WAL logs for consistent replication.	olake-repl-slot
JDBC URL Parameters	Extra JDBC URL parameters for fine-tuning the connection.	{"connectTimeout":"20"}
SSL Mode	SSL configuration for the database connection. Contains details such as the SSL mode.	disable

3. Test Connection

• Once the connection is validated, the Postgres source is created. Jobs can then be configured using this source.

• In case of connection failure, refer to the Troubleshooting section.

Use OLake CLI for Postgres

1. Create Configuration File

• Once the Olake CLI is setup, create a folder to store configuration files such as source.json and destination.json.

The source.json file for postgres must contain these mandatory fields.

2. Provide Configuration Details

An example source.json file will look like this:

source.json

{
    "host": "localhost",
    "port": 5432,
    "database": "main",
    "username": "main",
    "password": "password",
    "jdbc_url_params": {"connectTimeout":"20"},
    "retry_count": 3,
    "ssl": {
        "mode": "disable"
    },
    "update_method": {
        "replication_slot": "postgres_slot",
        "initial_wait_time": 120
    },
    "max_threads": 5
}

Field	Description	Example Value	Type
host required	Hostname or IP address of the Postgres database server.	"pg-host"	String
port required	TCP port on which the Postgres listener is accepting connections.	5432	Integer
database required	The name of the target database to connect to.	"olake-db"	String
password required	The password corresponding to the provided username for authentication.	"pgpwd"	String
username required	Database user used to authenticate the connection.	"pg-user"	String
update_method required for CDC	Required for CDC sync configuration	object	{"replication_slot": "postgres_slot","initial_wait_time": 120}
initial_wait_time required for CDC	Idle timeout for WAL log reading	120	Integer
replication_slot required for CDC	Slot to retain WAL logs for consistent replication.	"olake-repl-slot"	String
max_threads	Maximum number of worker threads the connector can spin up for parallel tasks.	10	Integer
retry_count	Number of times the retry will take place incase of timeout before failing the sync.	3	Integer
jdbc_url_params	Extra JDBC URL parameters for fine-tuning the connection.	{"connectTimeout":"20"}	Object
ssl	SSL configuration for the database connection. Contains details such as the SSL mode.	{"mode": "disable"}	Object

Similarly, destination.json file can be created inside this folder. For more information, see destination documentation.

3. Check Source Connection

To verify the database connection following command needs to be run:

check command

docker run --pull=always \
-v "[PATH_OF_CONFIG_FOLDER]:/mnt/config" \
olakego/source-postgres:latest \
check \
--config /mnt/config/source.json

• If OLake is able to connect with Postgres {"connectionStatus":{"status":"SUCCEEDED"},"type":"CONNECTION_STATUS"} response is returned.

• In case of connection failure, refer to the Troubleshooting section.

---

Datatype mapping

PostgreSQL Data Types	Destination Data Type
int, int2, int4, smallint, integer, serial, serial2, serial4	int
bigint, bigserial, int8, serial8	bigint
float, float4, real, numeric	float
double precision, float8	double
boolean	boolean
date, timestamp without time zone, timestamp with time zone	timestamptz
box, bpchar, char, character, character varying, json, jsonb, jsonpath, name, numrange, path, text, tid, tsquery, tsrange, tstzrange, uuid, varbit, varchar, xml, inet, bit, int2vector, daterange, time with time zone, time without time zone	string

timestamptz timezone

OLake always ingests timestamp data in UTC format, independent of the source timezone.

---

Troubleshooting

1. No encryption (SQLSTATE 28000)

FATAL failed to ping database: failed to connect to `host=product-team-testing.postgres.database.azure.com user=product_team database=postgres`: server error (FATAL: no p
g_hba.conf entry for host "4.240.65.100", user "product_team", database "postgres", no encryption (SQLSTATE 28000))

Solution: Change the ssl.mode: disable to ssl.mode: allow to resolve this issue. This is a known issue with Azure Postgres and the solution is to set the ssl.mode to allow in the connection string.

2. Connecting to Supabase

OLake can connect seamlessly to Supabase Postgres databases using any of the three connection modes that Supabase provides:

• Direct Connection – Connects directly to the Postgres instance.
  Note: By default, Supabase's direct connection supports only IPv6. IPv4 support can be enabled via the paid IPv4 add-on.

• Transaction Pooler – Uses PgBouncer in transaction pooling mode. Suitable for most use cases that do not require session-level features.

• Session Pooler – Recommended when the client network supports only IPv4. In this mode, each client gets a dedicated database session.

Supabase CDC Support

The Direct Connection mode is required for CDC (Change Data Capture) operations, as Supabase permits replication slot creation only when connecting directly to the Postgres instance.

IP Version Compatibility

OLake fully supports both IPv4 and IPv6 connections. However, Supabase’s IP compatibility depends on the connection mode and account configuration.

To check your machine or cloud environment’s IP version, you can run:

curl -6 https://ifconfig.co/ip

The format of the returned address indicates the IP version:

• Addresses with four decimal numbers (0–255) separated by dots (.), for example 192.168.1.1, indicate IPv4.
• Addresses containing hexadecimal segments separated by colons (:), such as 2404:6800:4007:821::200e, indicate IPv6.

Docker IPv6 Support

By default, Docker containers support only IPv4, which may block access to Supabase’s Direct Connection if IPv6 is required. IPv6 can be enabled as follows:

1. Open the Docker Desktop Dashboard.
2. Click the Settings icon (⚙️) in the top-right corner.
3. Select Docker Engine from the left-hand sidebar.
4. In the JSON configuration editor, add the following line

"ipv6":true

5. Click Apply & Restart to save the changes and restart Docker.

RLS (Row-Level Security) Considerations

When using a read-only database role for syncing with OLake, ensure that Row-Level Security (RLS) is either disabled or properly configured. If RLS policies restrict access, sync operations may complete without error but result in zero rows being replicated.

To allow a specific role to bypass RLS entirely, execute the following command (replace <rolename> with the actual role name):

ALTER USER <rolename> WITH BYPASSRLS;

If the issue is not listed here, post the query on Slack to get it resolved within a few hours.

Changelog

Date of Release	Version	Description
27 August 2025	0.1.11	override default timeout in Discover