How to use the Postgres Connector.

Postgres is one of the popular and widely used SQL databases. It also now supports JSON payloads as well as can be used as a data warehouse with the right configuration. The MovingLake Postgres connector uses CDC (change data capture) which means it listens in real-time to any changes made to any table in the database and sends this information downstream.

The connector works well for self-hosted Postgres instance as well as for RDS instances. If you are hosting your database elsewhere and need assistance, please reach out.

Connector guide.

Standalone Postgres

Use the following commands to get started. We’ll create a database, a user and setup postgres to create a logical replication slot.

create database pg_webhook;
create user pg_webhook with replication password 'pg_webhook';

Next you need to add some lines to Postgres’ configuration files. If you do not know where this files are, you can run ps -ef | grep postgres. You shoud see a line such as:

/opt/homebrew/opt/postgresql/bin/postgres -D /opt/homebrew/var/postgres

The last piece of this line (/opt/homebrew/var/postgres) is where your postgres configuration files will be stored. Now open pg_hba.conf and add the following line:

host replication pg_webhook 127.0.0.1/32 md5

Next open postgres.conf and add these following lines:

wal_level=logical
max_wal_senders=5
max_replication_slots=5

Finally when specifying the postgres DNS string when running pg_webhook, make sure it has the replication query parameter ?replication=database. Eg postgres://pg_webhook:pg_webhook@localhost:5432/pg_webhook?replication=database

RDS Postgres

RDS does not let you run with a real superuser, and also doesn’t let you change the configuration files. Most likely because of multitenant systems. To circumvent this, the easiest way to go is to use Postgres extension pglogical.

Please follow this link to set-up your RDS instance with the extension. Also checkout this other link if don’t know how to turn on the preloaded libraries for RDS.

Once you follow these steps, try and follow the rest from #standalone-postgres

Entities supported with this connector.

By default every table in the database will become an entity for movinglake. By default we also only extract data from the public schema. The entity name is constructed as $schema_$tableName.