Guides

Neon PostgreSQL

PostgreSQL Change Data Capture Setup on Neon with Streamkap

Prerequisites

Granting Privileges

It's recommended to create a separate user and role for Streamkap to access your PostgreSQL database. Below is an example script that does that. 

-- Replace { ... } placeholders as required
CREATE USER streamkap_user PASSWORD '{password}';

-- Create a role for Streamkap
CREATE ROLE streamkap_role nologin PASSWORD '{password}';
GRANT streamkap_role TO streamkap_user;
GRANT rds_replication TO streamkap_role;

-- Grant Streamkap permissions on the database, schema and all tables to capture
-- Create on database is used to create the publication. Not needed if you create yourself
GRANT CONNECT ON DATABASE "{database}" TO streamkap_role; 
GRANT CREATE, USAGE ON SCHEMA "{schema}" TO streamkap_role;
GRANT SELECT ON ALL TABLES IN SCHEMA "{schema}" TO streamkap_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA "{schema}" GRANT SELECT ON TABLES TO streamkap_role;

-- Grant replication role to the user
ALTER USER streamkap_user WITH REPLICATION;

Enable Snapshots

You will need to create the table in the source database and give permissions to the streamkap_role. Streamkap will use this collection for managing snapshots.

❗️

Please create the signal table with the name streamkap_signal. It will not be recognised if given another name.

-- Create the schema
CREATE SCHEMA streamkap;

-- Switch to the newly created schema
SET search_path TO streamkap;

-- Create the table
CREATE TABLE streamkap_signal (
  id VARCHAR(255) PRIMARY KEY, 
  type VARCHAR(32) NOT NULL, 
  data VARCHAR(2000) NULL
);

-- Grant necessary privileges on the table to the role
GRANT SELECT, UPDATE, INSERT ON TABLE streamkap_signal TO streamkap_role;

Consider Access Restrictions

Setup Neon Connector in Streamkap

  • Go to Sources and click Create New
  • Input
    • Name for your Connector
    • Hostname
    • Port (Default 5432)
    • Username (Username you chose earlier, our scripts use streamkap_user)
    • Password
    • Heartbeat - Required for low volume connectors. See PostgreSQL Heartbeats
    • Signal Table Schema - Streamkap will use a collection in this schema to manage snapshots e.g. streamkap. See Enable Snapshots for more information
    • Database Name
    • Replication Slot Name (Default streamkap_pgoutput_slot)
    • Publication Name (Default streamkap_pub)
    • Connect via SSH Tunnel. See SSH Tunnel
    • Advanced Parameters
      • Represent Binary Data As (Default bytes)
    • Add Schemas/Tables. Can also bulk upload here. The format is a simple list of each schema or table per row saved in csv format without a header.
    • Click Save
      The connector will take approximately 1 minute to start processing data.

Updated 2 months ago