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.

Replication strategies

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

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).
3. WAL configuration (for CDC).
   If using WAL-based CDC, the database wal_level must be set to logical.

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.

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.

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 replication identity

For CDC to correctly identify updated and deleted rows, your tables must have a PRIMARY KEY. Renta does not support tables without a primary key for CDC replication.

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.

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.