Postgres Source

Postgres Source enables data synchronization from Postgres to your desired destination.

info

OLake UI is live (beta)! You can now use the UI to configure your Postgres source, discover streams, and sync data. Check it out at OLake UI regarding how to setup using Docker Compose and running it locally.

Now, you can use the UI to configure your Postgres source, discover streams, and sync data.

Use OLake UI for Postgres

Follow the steps below to get started with the Postgres Source using the OLake UI (assuming the OLake UI is running locally on localhost:8000):

1. Navigate to Sources Tab.
2. Click on + Create Source.
3. Select Postgres as the source type from Connector type.
4. Fill in the required connection details in the form. For details regarding the connection details, refer to the Postgres Source Configuration section on the right side of UI.
5. Click on Create ->
6. OLake will test the source connection and display the results. If the connection is successful, you will see a success message. If there are any issues, OLake will provide error messages to help you troubleshoot.

This will create a Postgres source in OLake, now you can use this source in your Jobs Pipeline to sync data from Postgres to Apache Iceberg or AWS S3.

Use OLake CLI for Postgres

To sync data TLDR:

1. Create a source.json with your Postgres connection details.
2. Create a destination.json with your Writer (Apache Iceberg or AWS S3) connection details.
3. Run discover to generate a streams.json of available streams.
4. Run sync to replicate data to your specified destination.

Features

• Resumable Full Load: Supports restarting full loads without reprocessing all data.
• Fast Full Load via Chunking: • CTID-based chunking • Single split-column chunking • Evenly distributed chunks (for int/float PKs) • Uneven chunking support
• WAL2JSON-based CDC Sync for incremental updates.

caution

Currently plugin of pgoutput as replication slot is not supported, only WAL2JSON is supported.

• New streams are automatically synced when added.
• LSN mismatch handling triggers a full resync.

Postgres Driver

The Postgres Driver enables data synchronization from Postgres to your desired destination.

Below is an overview of the supported modes and writers for Postgres data replication, along with tables summarizing the details.

Supported Modes

Our replication process supports various modes to fit different data ingestion needs.

The Full Refresh mode retrieves the entire dataset from Postgres and is ideal for initial data loads or when a complete dataset copy is required.

In contrast, CDC (Change Data Capture) continuously tracks and synchronizes incremental changes in real time, making sure that your destination remains updated with minimal latency.

The Incremental mode is currently under development (WIP).

Mode	Description
Full Refresh	Fetches the complete dataset from Postgres.
CDC (Change Data Capture)	Tracks and syncs incremental changes from Postgres in real time.
Incremental	No, WIP

Supported Destinations

OLake replicates data to multiple destinations to cater to a variety of deployment scenarios.

Whether you're storing data locally for quick access or using cloud storage services like S3 for scalability, our system is designed to integrate seamlessly.

We are also working on adding support for Iceberg to facilitate advanced analytics and data lake management.

Destination	Supported	Docs
Local Filesystem	Yes	Link
S3	Yes	Link
Iceberg	Yes	Link

Setup and Configuration

To run the Postgres Driver, configure the following files with your specific credentials and settings:

• source.json: Postgres connection details.
• streams.json: List of collections and fields to sync (generated using the Discover command).
• write.json: Configuration for the destination where the data will be written.

Place these files in your project directory before running the commands.

Source File

Add Postgres credentials in following format in source.json file as shown here.

Commands

Discover Command

The Discover command generates json content for streams.json file, which defines the schema of the collections to be synced.

Usage

To run the Discover command, use the following syntax

OLake Docker

macOS / Linux

docker run --pull=always  \
  -v "$HOME/PATH_TO_OLAKE_DIRECTORY:/mnt/config" \
  olakego/source-postgres:latest \
  discover \
  --config /mnt/config/source.json

CMD

docker run --pull=always  ^
  -v "%USERPROFILE%\PATH_TO_OLAKE_DIRECTORY:/mnt/config" ^
  olakego/source-postgres:latest ^
  discover ^
  --config /mnt/config/source.json

Powershell

docker run --pull=always  `
  -v "$env:USERPROFILE\PATH_TO_OLAKE_DIRECTORY:/mnt/config" `
  olakego/source-postgres:latest `
  discover `
  --config /mnt/config/source.json

Locally run OLake

macOS / Linux

OLAKE_BASE_PATH="$HOME/PATH_TO_OLAKE_DIRECTORY/olake/drivers/postgres/config" && \
./build.sh driver-postgres discover \
  --config "$OLAKE_BASE_PATH/source.json"

CMD

set "OLAKE_BASE_PATH=%USERPROFILE%\PATH_TO_OLAKE_DIRECTORY\olake\drivers\postgres\config" && ^
./build.sh driver-postgres discover ^
  --config "%OLAKE_BASE_PATH%\source.json"

Powershell

$OLAKE_BASE_PATH = "$env:USERPROFILE\PATH_TO_OLAKE_DIRECTORY\olake\drivers\postgres\config"; `
./build.sh driver-postgres discover `
  --config "$OLAKE_BASE_PATH\source.json"

Streams File

After executing the Discover command, a streams.json file is created. Read more about Streams File here.

Writer File

Read about about :

1. Apache Iceberg Destination config
2. S3 writer
3. Local Filesystem writer

Sync Command

The Sync command fetches data from Postgres and ingests it into the destination.

OLake Docker

macOS / Linux

docker run --pull=always  \
  -v "$HOME/PATH_TO_OLAKE_DIRECTORY:/mnt/config" \
  olakego/source-postgres:latest \
  sync \
  --config /mnt/config/source.json \
  --catalog /mnt/config/streams.json \
  --destination /mnt/config/destination.json

CMD

docker run --pull=always  ^
  -v "%USERPROFILE%\PATH_TO_OLAKE_DIRECTORY:/mnt/config" ^
  olakego/source-postgres:latest ^
  sync ^
  --config /mnt/config/source.json ^
  --catalog /mnt/config/streams.json ^
  --destination /mnt/config/destination.json

Powershell

docker run --pull=always  `
  -v "$env:USERPROFILE\PATH_TO_OLAKE_DIRECTORY:/mnt/config" `
  olakego/source-postgres:latest `
  sync `
  --config /mnt/config/source.json `
  --catalog /mnt/config/streams.json `
  --destination /mnt/config/destination.json

Locally run OLake

macOS / Linux

OLAKE_BASE_PATH="$HOME/PATH_TO_OLAKE_DIRECTORY/olake/drivers/postgres/config" && \
./build.sh driver-postgres sync \
  --config "$OLAKE_BASE_PATH/source.json" \
  --catalog "$OLAKE_BASE_PATH/streams.json" \
  --destination "$OLAKE_BASE_PATH/destination.json"

CMD

set "OLAKE_BASE_PATH=%USERPROFILE%\PATH_TO_OLAKE_DIRECTORY\olake\drivers\postgres\config" && ^
./build.sh driver-postgres sync ^
  --config "%OLAKE_BASE_PATH%\source.json" ^
  --catalog "%OLAKE_BASE_PATH%\streams.json" ^
  --destination "%OLAKE_BASE_PATH%\destination.json"

Powershell

$OLAKE_BASE_PATH = "$env:USERPROFILE\PATH_TO_OLAKE_DIRECTORY\olake\drivers\postgres\config"; `
./build.sh driver-postgres sync `
  --config "$OLAKE_BASE_PATH\source.json" `
  --catalog "$OLAKE_BASE_PATH\streams.json" `
  --destination "$OLAKE_BASE_PATH\destination.json"

To run sync with state:

OLake Docker

macOS / Linux

docker run --pull=always  \
  -v "$HOME/PATH_TO_OLAKE_DIRECTORY:/mnt/config" \
  olakego/source-postgres:latest \
  sync \
  --config /mnt/config/source.json \
  --catalog /mnt/config/streams.json \
  --destination /mnt/config/destination.json \
  --state /mnt/config/state.json

CMD

docker run --pull=always  ^
  -v "%USERPROFILE%\PATH_TO_OLAKE_DIRECTORY:/mnt/config" ^
  olakego/source-postgres:latest ^
  sync ^
  --config /mnt/config/source.json ^
  --catalog /mnt/config/streams.json ^
  --destination /mnt/config/destination.json ^
  --state /mnt/config/state.json

Powershell

docker run --pull=always  `
  -v "$env:USERPROFILE\PATH_TO_OLAKE_DIRECTORY:/mnt/config" `
  olakego/source-postgres:latest `
  sync `
  --config /mnt/config/source.json `
  --catalog /mnt/config/streams.json `
  --destination /mnt/config/destination.json `
  --state /mnt/config/state.json

Locally run OLake

macOS / Linux

OLAKE_BASE_PATH="$HOME/PATH_TO_OLAKE_DIRECTORY/olake/drivers/postgres/config" && \
./build.sh driver-postgres sync \
  --config "$OLAKE_BASE_PATH/source.json" \
  --catalog "$OLAKE_BASE_PATH/streams.json" \
  --destination "$OLAKE_BASE_PATH/destination.json" \
  --state "$OLAKE_BASE_PATH/state.json"

CMD

set "OLAKE_BASE_PATH=%USERPROFILE%\PATH_TO_OLAKE_DIRECTORY\olake\drivers\postgres\config" && ^
./build.sh driver-postgres sync ^
  --config "%OLAKE_BASE_PATH%\source.json" ^
  --catalog "%OLAKE_BASE_PATH%\streams.json" ^
  --destination "%OLAKE_BASE_PATH%\destination.json" ^
  --state "%OLAKE_BASE_PATH%\state.json"

Powershell

$OLAKE_BASE_PATH = "$env:USERPROFILE\PATH_TO_OLAKE_DIRECTORY\olake\drivers\postgres\config"; `
./build.sh driver-postgres sync `
  --config "$OLAKE_BASE_PATH\source.json" `
  --catalog "$OLAKE_BASE_PATH\streams.json" `
  --destination "$OLAKE_BASE_PATH\destination.json" `
  --state "$OLAKE_BASE_PATH\state.json"

Find more about state file and its configuration here.

Changelog

Expand to review

Version	Date	Pull Request	Subject
v0.0.3	14.04.2025	https://github.com/datazip-inc/olake/pull/203
v0.0.4	31.04.2025	https://github.com/datazip-inc/olake/pull/250