PostgreSQL

Renta supports loading data from PostgreSQL databases using efficient replication strategies, including Change Data Capture (CDC). This allows you to sync your data with minimal latency and impact on your source database.

Prerequisites

To connect PostgreSQL to Renta, ensure you have the following:

1. Network access.
   Allow connections from Renta's IP addresses to your PostgreSQL instance.
2. User permissions.
   A database user with REPLICATION and SELECT privileges (scripts provided below).

Vendor-specific configuration

For WAL-based CDC, you must enable wal2json plugin. Renta uses wal2json to stream changes from your database.

Please follow the instructions below to enable wal2json for your specific database provider:

Database configuration

To replicate data from PostgreSQL, you need to configure your database settings and create a user with the appropriate permissions. The following steps guide you through setting up WAL-based CDC (recommended) and granting access to Renta.

Create user and grant permissions

Execute the following SQL commands in your database to create a dedicated user for Renta and grant the necessary permissions.

-- 1. Create user
CREATE USER <user_name>
WITH REPLICATION
IN ROLE cloudsqlsuperuser -- Include this line only for Google Cloud SQL
LOGIN PASSWORD '<password>';

-- 2. Grant database access
GRANT CONNECT ON DATABASE "<database_name>" TO <user_name>;
GRANT CREATE ON DATABASE "<database_name>" TO <user_name>;

-- 3. Grant schema access
GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;

-- 4. Grant table permissions (current and future)
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>;

-- 5. Change table ownership (if required)
-- Replace '<schema_name>.<table_name>' with your specific table name
ALTER TABLE <schema_name>.<table_name> OWNER TO <user_name>;

Configure WAL level (CDC only)

WAL-based CDC requires your PostgreSQL instance to use logical replication. If you plan to use Checkpoint-based or Offset/Limit-based replication instead, skip this section.

1. Open your postgresql.conf file.

2. Set the wal_level parameter to logical:

   

   wal_level = logical

3. Restart your PostgreSQL instance to apply the changes.

Once you have created and configured the database user, you can proceed to add the source in Renta.

Add PostgreSQL source to Renta

Follow these steps to add PostgreSQL as a source in Renta.

Navigate to sourcesStep 1

Navigate to the Connections > Sources section in the left sidebar.

Select PostgreSQLStep 2

1. Click Add source.
2. In the catalog, find and select PostgreSQL in the Database category.

This will open the connection configuration form.

Configure connectionStep 3

Fill in the connection details:

Field	Description
Source name	A display name for this connection in Renta.
Hostname	Enter the full hostname of the PostgreSQL server.
Port	Enter the port number (default: 5432).
Database name	Enter the name of the PostgreSQL database you want to connect to.
User	Enter the username for accessing the PostgreSQL database.
Password	Enter the password associated with the database user.

Click Save to finish.

Connection successfulStep 4

Once saved, your PostgreSQL source will appear in the Sources list and is ready to use in pipelines.

Pipeline configuration

Once your PostgreSQL source is connected, follow these steps to configure a data pipeline.

1. Select tables.
   Choose the tables you want to replicate.
2. Select columns.
   Specify which columns should be included in the sync.
3. Choose ingestion mode.
   Select one of the replication strategies described below.

Replication strategies

Renta provides three replication methods tailored to different table sizes and consistency requirements.

Offset/limit-based replication

The most basic method that reads the table in pages of 10,000 rows. It doesn't store state between runs, meaning each sync performs a full scan of the table.

1. Best for.
   Small tables where a full overwrite is acceptable.
2. Note.
   While it supports identifying deletes (by comparing the source snapshot with the destination), it is inefficient for large datasets.

Checkpoint-based replication

An incremental method that tracks progress using a "checkpoint" column (usually a timestamp like updated_at, or an auto-incrementing ID). Renta only fetches rows where the value in this column is greater than the last recorded checkpoint.

1. Best for.
   Large tables where you only need to sync new or updated rows and don't need to track hard deletes.
2. Requirement.
   A monotonically increasing column to serve as a cursor.

PostgreSQL WAL-based CDC

The most advanced method that uses the PostgreSQL Write-Ahead Log (WAL) to capture all changes (INSERT, UPDATE, DELETE) in near real-time.

1. Best for.
   Large, high-velocity tables where capturing deletes and maintaining low latency is critical.
2. Requirements.
   1. wal_level set to logical.
   2. wal2json plugin enabled.
   3. Tables must have a Primary key.

Configure strategy-specific settings

Depending on the chosen ingestion mode, you need to configure specific parameters.

For Checkpoint-based replication

1. Select Checkpoint-based Replication.
   Choose this method from the Ingestion mode dropdown.
2. Set checkpoint column.
   Select the column used to track the progress of the replication. It must be a monotonically increasing value (for example, created_at, updated_at, or an auto-incrementing id). Renta uses this to identify new or modified rows since the last sync.
3. Set merge key.
   Select a unique identifier for each row (usually the Primary key). Renta uses this to deduplicate data in the destination: existing rows with the same key will be updated, and new rows will be inserted.

For WAL-based CDC

1. Select PostgreSQL WAL-based CDC.
   Choose this method from the Ingestion mode dropdown.
2. Set merge key.
   Select the Primary key of the table. Since CDC captures all changes (activites), Renta needs this key to correctly apply UPDATE and DELETE operations in the destination warehouse to mirror the source state.

For Offset-based replication

1. Select Offset/Limit-based Replication.
   Choose this method from the Ingestion mode dropdown.
2. Set sorting (grouping) key.
   Select a column to determine the order of reading. While this method scans the full table, a deterministic sort order ensures consistent paging. Usually, the Primary key is the best choice.

Sync scheduling

Once your PostgreSQL source is configured, you can control when and how often data syncs occur. Renta provides flexible scheduling options to fit your data pipeline requirements.