> ## Documentation Index
> Fetch the complete documentation index at: https://control.datarelay.run/llms.txt
> Use this file to discover all available pages before exploring further.

# Collect Events from PostgreSQL

> Configure a read-only database Connector, query events, and verify end-to-end delivery.

# Collect Events from PostgreSQL

**Goal:** use the implemented **Database query (PostgreSQL)** Connector to read a controlled table and send events to an HTTP Destination. This requires a test PostgreSQL database owned by you.

> [!NOTE]
>
> **Screen version:** The numbered images are historical Control UI test captures. Follow the current five-step flow (Connect → Sample & Record Selection → Destinations → Route Processing → Deploy), not older step ordering. See the [numbered screenshot guide](https://control.datarelay.run/use-cases/screen-walkthrough.md).

## End-to-end workflow and acceptance

1. **Prepare requirements:** consult the vendor's official API/integration guide for endpoint, authentication, least-privilege scope, pagination/checkpoint and sample response. Obtain sanitized test events and actual Destination connectivity information.
2. **Use ChatGPT for unknowns:** supply public documentation and redacted samples; request a checklist of Connector inputs, request parameters, event root, timestamp/ID, cursor and unknowns. **Never share secrets.** Validate the response with the vendor guide and Run Test.
3. **Create Connector:** Connectors → Create Connector, choose this page's source type, enter real connection/auth details, save and test connectivity.
4. **Create Destination:** Destinations → Create, choose SYSLOG\_UDP/TCP/TLS or WEBHOOK\_POST, and configure the actual receiver's host/port/URL, TLS and authentication.
5. **Create Stream:** Streams → Create → Connect, choose the saved Connector and configure source collection/request settings.
6. **Confirm samples and schema:** Sample & Record Selection → Run Test; verify extracted records, Record Root, Union Schema, pagination and checkpoint.
7. **Process events:** select Destination/Route; configure Mapping, timestamp, enrichment, Masking/Drop/Quarantine in Route Processing. Use Tokenization **only if the deployed product supports it**. Verify effective Route state and actual output.
8. **Deploy and prove delivery:** resolve readiness findings, start the Stream, and correlate Run ID, Route Logs and the **independent Destination receipt**. All three are required for PASS.
9. **Diagnose:** for connection/auth check API scope, TLS, network and 429; for zero records check record root/pagination; for failed delivery follow [Delivery Incident](https://control.datarelay.run/use-cases/delivery-incident.md); for changed fields follow [Schema Drift](https://control.datarelay.run/use-cases/schema-change.md).

### Suggested ChatGPT prompt

> I want to collect this vendor's logs using DataRelay Control. Based only on the public API guide and redacted JSON sample below, list required Connector connection/auth fields, API request/pagination, event root, timestamp/ID/checkpoint, mapping and sensitive-field protection, and failure diagnostics. Mark undocumented information 'needs confirmation' rather than inventing values.

## 1. Prepare the test table

Run the following SQL in an authorized **test** PostgreSQL database using an administrator account:

```sql theme={null}
CREATE TABLE IF NOT EXISTS control_tutorial_events (
    id bigint PRIMARY KEY,
    occurred_at timestamptz NOT NULL,
    message text NOT NULL
);
INSERT INTO control_tutorial_events(id, occurred_at, message)
VALUES (101, '2026-10-18T00:00:00Z', 'synthetic db event')
ON CONFLICT (id) DO NOTHING;
```

Provision a separate read-only database user with `SELECT` on that table. Configure network access from Control to PostgreSQL; do not use a production write-enabled account.

## 2. Configure the Connector

1. Open **Connectors → Create Connector**, select **Database query (PostgreSQL)**.
2. Enter **Connector Name** = `Lab PostgreSQL`, **DB type** = `PostgreSQL`.
3. Set actual **Host**, **Port** (typically 5432), **Database name**, **Username**, and **Password**; choose your server's correct **SSL mode** (e.g. `VERIFY_FULL` with a valid certificate).
4. Save and execute the supported database connectivity test. Confirm `SELECT` permission and TLS.

## 3. Select and test the query

1. Open `/streams/new`, choose the saved Connector.
2. Use the database discovery/query builder supported by **Connect** and **Run Test**. Use a read-only query like:

```sql theme={null}
SELECT id, occurred_at, message
FROM control_tutorial_events
ORDER BY occurred_at, id
```

3. Confirm the query actually returns the synthetic row. Set **Record Selection** to the returned record set.
4. Review column types and checkpoint strategy. If incremental mode is required, use the documented stable cursor/tie-breaker field; the sample timestamp alone is not sufficient evidence of monotonicity or uniqueness.
5. Confirm Union Schema and mapped event output.

![run-test — numbered Control historical UI capture](https://mintcdn.com/datarelay-control/Tl9_UibGAprPlCr8/images/screenshots/annotated/run-test.webp?fit=max\&auto=format\&n=Tl9_UibGAprPlCr8\&q=85\&s=b608f03405b24c6b130a215599571177)

*Figure 1. **Testing collection and record count.** ① Run Test → ② verify HTTP status and extracted record count → ③ open Record Selection. An HTTP success is not proof of Destination delivery.*

![record-selection — numbered Control historical UI capture](https://mintcdn.com/datarelay-control/Tl9_UibGAprPlCr8/images/screenshots/annotated/record-selection.webp?fit=max\&auto=format\&n=Tl9_UibGAprPlCr8\&q=85\&s=196de1ecaf541296095434008afc05ad)

*Figure 2. **Choosing record path and checkpoint.** ① Review the suggested Record Path, ② inspect actual fields in JSON Tree, ③ select the Checkpoint Field. This capture shows the not-yet-confirmed state.*

## 4. Deploy and verify

1. Choose the [lab HTTP sink](https://control.datarelay.run/use-cases/lab-setup.md) as a `WEBHOOK_POST` Destination.
2. In **Route Processing**, review the field mapping/output preview.
3. **Deploy**, start the Stream if needed, and inspect **Logs** and the sink's `received-9011.ndjson`.
4. Add an additional synthetic test row with a greater ID/timestamp using your authorized database account; verify collection behavior and absence of unexpected duplicates.

**PASS:** the authorized read-only query returns the expected row, a Route delivery is recorded, and the sink contains the corresponding event.

![stream-actions — numbered Control historical UI capture](https://mintcdn.com/datarelay-control/Tl9_UibGAprPlCr8/images/screenshots/annotated/stream-actions.webp?fit=max\&auto=format\&n=Tl9_UibGAprPlCr8\&q=85\&s=f5462ebde85e05d0c95ab32096a1a24e)

*Figure 3. **Start and verify a newly created Stream.** ① Review Stream creation, ② use Start Stream only when runtime is available, ③ open View logs to verify execution. This historical capture says Runtime API unavailable.*

## If it fails

* Authentication: check DB host/port/SSL/readonly account.
* Query yields zero rows: inspect database/schema/table selection and timestamp filters.
* Duplicate or omitted rows: inspect event ordering, incremental checkpoint and tie-breaker.
* Database supports something other than PostgreSQL: the documented runtime is PostgreSQL-centric; **do not claim another engine is supported without source/fixture proof**.

**Related:** [HTTP API polling](https://control.datarelay.run/use-cases/http-api.md), [Schema drift](https://control.datarelay.run/use-cases/schema-change.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.