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.
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
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
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
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