PostgreSQL Ingress

PostgreSQL Ingress

The PostgreSQL ingress automatically syncs data from a PostgreSQL table to a Bright search index.

Features

• Two sync modes: Polling (interval-based) and LISTEN/NOTIFY (real-time)
• Automatic schema setup: Creates required tracking tables and triggers
• Hard delete support: Tracks deleted rows via trigger
• Offline recovery: Catches up on changes after service restarts

Quick Start

1. Create an Index

curl -X POST "http://localhost:3000/indexes?id=products"

2. Create a PostgreSQL Ingress

curl -X POST "http://localhost:3000/indexes/products/ingresses" \
  -H "Content-Type: application/json" \
  -d '{
    "id": "pg-products",
    "type": "postgres",
    "config": {
      "dsn": "postgres://user:password@localhost:5432/mydb",
      "table": "products",
      "primary_key": "id",
      "updated_at_column": "updated_at"
    }
  }'

3. Check Status

curl "http://localhost:3000/indexes/products/ingresses/pg-products"

Configuration Options

Field	Type	Required	Default	Description
dsn	string	Yes	-	PostgreSQL connection string
table	string	Yes	-	Table name to sync
schema	string	No	public	PostgreSQL schema
primary_key	string	No	id	Primary key column
columns	string[]	No	*	Columns to sync (empty = all)
column_mapping	object	No	-	Rename columns: {"db_col": "doc_field"}
updated_at_column	string	No	updated_at	Timestamp column for change detection
where_clause	string	No	-	Filter rows: active = true
sync_mode	string	No	polling	polling or listen
poll_interval	string	No	30s	Polling interval (e.g., 10s, 1m)
batch_size	int	No	1000	Documents per batch
auto_triggers	bool	No	true	Auto-create PostgreSQL triggers

Full Configuration Example

{
  "id": "pg-products",
  "type": "postgres",
  "config": {
    "dsn": "postgres://user:password@localhost:5432/mydb",
    "table": "products",
    "schema": "public",
    "primary_key": "id",
    "columns": ["id", "name", "description", "price", "category"],
    "column_mapping": {
      "product_name": "name"
    },
    "updated_at_column": "updated_at",
    "where_clause": "active = true AND deleted_at IS NULL",
    "sync_mode": "polling",
    "poll_interval": "30s",
    "batch_size": 1000,
    "auto_triggers": true
  }
}

Sync Modes

Polling Mode (Default)

Uses the updated_at column to detect changes. Bright queries for rows where updated_at > last_sync_time at regular intervals.

Requirements:

• Table must have an updated_at timestamp column
• Column must be updated on every INSERT and UPDATE

Pros:

• Simple setup
• Works with any PostgreSQL version
• No special permissions needed

Cons:

• Changes are not immediate (depends on poll interval)

LISTEN/NOTIFY Mode

Uses PostgreSQL's LISTEN/NOTIFY for near real-time updates. Bright creates triggers that send notifications on INSERT, UPDATE, and DELETE.

{
  "config": {
    "sync_mode": "listen",
    "auto_triggers": true
  }
}

Requirements:

• PostgreSQL 9.0+
• Permission to create triggers (if auto_triggers: true)

Pros:

• Near real-time sync
• Lower database load than polling

Cons:

• Requires trigger creation permissions
• Initial sync still uses polling

Auto-Created Tables

When the ingress starts, it creates these tables in your PostgreSQL database:

__bright_synchronization

Stores sync state (last sync time, progress).

CREATE TABLE IF NOT EXISTS __bright_synchronization (
    table_name VARCHAR(255) PRIMARY KEY,
    last_sync_at TIMESTAMPTZ,
    last_id TEXT,
    full_sync_complete BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

__bright_synchronization_deletes

Tracks hard deletes via trigger.

CREATE TABLE IF NOT EXISTS __bright_synchronization_deletes (
    id SERIAL PRIMARY KEY,
    source_table VARCHAR(255) NOT NULL,
    deleted_id TEXT NOT NULL,
    deleted_at TIMESTAMPTZ DEFAULT NOW()
);

Auto-Created Triggers

When auto_triggers: true, Bright creates:

Delete Tracking Trigger

Captures deleted row IDs for sync:

CREATE TRIGGER __bright_delete_trigger_<table>
AFTER DELETE ON <table>
FOR EACH ROW EXECUTE FUNCTION __bright_track_deletes_<table>();

LISTEN/NOTIFY Trigger (listen mode only)

Sends notifications on changes:

CREATE TRIGGER __bright_notify_trigger_<table>
AFTER INSERT OR UPDATE OR DELETE ON <table>
FOR EACH ROW EXECUTE FUNCTION __bright_notify_<table>();

Managing Ingresses

Pause Sync

curl -X PATCH "http://localhost:3000/indexes/products/ingresses/pg-products" \
  -H "Content-Type: application/json" \
  -d '{"state": "paused"}'

Resume Sync

curl -X PATCH "http://localhost:3000/indexes/products/ingresses/pg-products" \
  -H "Content-Type: application/json" \
  -d '{"state": "running"}'

Trigger Full Resync

Clears sync state and reimports all data:

curl -X PATCH "http://localhost:3000/indexes/products/ingresses/pg-products" \
  -H "Content-Type: application/json" \
  -d '{"state": "resyncing"}'

Delete Ingress

curl -X DELETE "http://localhost:3000/indexes/products/ingresses/pg-products"

Handling Downtime

When Bright restarts after being offline:

1. Loads last_sync_at from __bright_synchronization table
2. Queries all rows where updated_at > last_sync_at
3. Queries __bright_synchronization_deletes for missed deletes
4. Processes all changes before resuming normal sync

This ensures no data is lost during service interruptions.

Troubleshooting

Check Ingress Status

curl "http://localhost:3000/indexes/products/ingresses/pg-products"

Look at the statistics field:

• last_error: Most recent error message
• error_count: Total errors encountered
• full_sync_complete: Whether initial sync finished
• documents_synced: Total documents synced

Common Issues

Issue	Cause	Solution
connection refused	Wrong DSN or PostgreSQL not running	Check DSN and PostgreSQL status
permission denied	User lacks table access	Grant SELECT on table
trigger creation failed	User lacks CREATE TRIGGER	Grant permission or set auto_triggers: false
Documents not updating	updated_at not being set	Ensure column updates on every change
Deletes not syncing	Trigger not created	Check auto_triggers or create manually