Postgres – RevOS Documentation & Support

The RevOS Postgres Connector allows seamless integration between your PostgreSQL database and the RevOS platform, enabling robust and flexible data replication. The connector supports multiple update methods, including Change Data Capture (CDC) using logical replication and replication based on the xmin system column.

Prerequisites

Before setting up the connector, ensure you have:

Access to a PostgreSQL database (version 10 or higher).

A user account with read-only privileges and replication rights.

Setting Up a Read-Only User

It is recommended to create a dedicated read-only user for data replication. Run the following SQL commands:

CREATE USER <username> PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database_name> TO <username>;
GRANT USAGE ON SCHEMA <schema_name> TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <username>;

Replace <username>, <password>, <database_name>, and <schema_name> with your actual database credentials.

Configuring the PostgreSQL Connector in RevOS

Connection Parameters:

Data Source Name: Enter a descriptive name for the data source.

Host: Specify the PostgreSQL server address.

Port: Default is 5432.

Database Name: Enter the PostgreSQL database name.

Username: Provide the username with read access.

Password: Provide the password for authentication.

Schemas: Specify schema names to replicate. Leave empty to replicate all accessible schemas.

Optional Parameters:

JDBC URL Parameters: Additional configuration options for JDBC connection.

SSL Mode: Choose the SSL mode (disable, allow, require, verify-ca, verify-full).

Data Update Methods:

Change Data Capture (CDC) using Write-Ahead Log (WAL): Enables continuous replication of database changes. Requires replication slot and publication.

Replication using the xmin system column: Suitable for databases where logical replication is not available.

Configuring Change Data Capture (CDC)

To use CDC with logical replication, follow these steps:

Enable Logical Replication:

Ensure that wal_level is set to logical, and both max_replication_slots and max_wal_senders are greater than zero in the PostgreSQL configuration (postgresql.conf).

Create a Publication:

Run the following command:

CREATE PUBLICATION <publication_name> FOR ALL TABLES;

Replace <publication_name> with your preferred publication name.

Set Up a Replication Slot:

RevOS will automatically create a replication slot upon initial connection if one does not exist.

Limitations and Recommendations

PostgreSQL Version Restrictions: The connector supports PostgreSQL version 10 and above. CDC is recommended for versions 10+.

Performance Considerations: Replication performance depends on server configuration and data volume. Testing is recommended before deploying to production.

Security Settings: When using SSL, it is recommended to use verify-ca or verify-full modes to ensure secure data transmission.