PostgreSQL

Datafly Signal delivers events to PostgreSQL for reliable, feature-rich relational database storage with advanced JSON querying, full-text search, and extension ecosystem support.

Prerequisites

Before configuring PostgreSQL in Signal, you need a PostgreSQL server (self-hosted or managed), a database with a schema and table, and a user with INSERT privileges.

Set Up a PostgreSQL Server

You have several options:

Option A: Managed PostgreSQL

Amazon RDS for PostgreSQL — Create an RDS instance
Azure Database for PostgreSQL — Create an Azure PostgreSQL server
Google Cloud SQL for PostgreSQL — Create a Cloud SQL instance
Supabase, Neon, Aiven, or other managed providers

Option B: Self-Hosted PostgreSQL

Install PostgreSQL using the official guide.
Start the PostgreSQL server.
Ensure it is accessible from your Signal infrastructure on port 5432.

Configure Network Access

Ensure your PostgreSQL server accepts connections from Signal’s IP addresses:

RDS: Configure the security group inbound rules on port 5432.
Azure: Add firewall rules in the Networking tab.
Cloud SQL: Add authorised networks.
Self-hosted: Configure pg_hba.conf and firewall rules.

Create a Database, Schema, and Table

Connect to the PostgreSQL server and create the target resources:

CREATE DATABASE datafly_events;
 
\c datafly_events
 
CREATE SCHEMA IF NOT EXISTS datafly;
 
CREATE TABLE datafly.events (
  event_id VARCHAR(64) NOT NULL PRIMARY KEY,
  type VARCHAR(20),
  event VARCHAR(256),
  anonymous_id VARCHAR(64),
  user_id VARCHAR(256),
  timestamp TIMESTAMPTZ,
  received_at TIMESTAMPTZ,
  sent_at TIMESTAMPTZ,
  context JSONB,
  properties JSONB,
  traits JSONB,
  source_id VARCHAR(64),
  integration_id VARCHAR(64)
);
 
CREATE INDEX idx_events_timestamp ON datafly.events (timestamp DESC);
CREATE INDEX idx_events_type_event ON datafly.events (type, event);

Using JSONB (binary JSON) instead of JSON enables indexing and efficient querying with operators like ->, ->>, @>, and ?. TIMESTAMPTZ stores timestamps with timezone information for correct ordering across time zones.

Create a User for Signal

CREATE USER datafly_signal WITH PASSWORD 'your_secure_password';
GRANT USAGE ON SCHEMA datafly TO datafly_signal;
GRANT INSERT ON TABLE datafly.events TO datafly_signal;

Configuration

Field	Type	Required	Description
`host`	string	Yes	The PostgreSQL server hostname.
`port`	string	Yes	The PostgreSQL server port. Defaults to `5432`.
`database`	string	Yes	The target database name.
`schema_name`	string	Yes	The schema to write to. Defaults to `public`.
`table_name`	string	Yes	The target table name to insert rows into.
`username`	string	Yes	The database username for authentication.
`password`	secret	Yes	The database password for authentication.
`ssl_mode`	select	No	The SSL mode for the connection: `disable`, `require`, `verify-ca`, `verify-full`. Defaults to `require`.

Signal Setup

Quick Setup

Navigate to Integrations in the sidebar.
Open the Integration Library tab.
Find PostgreSQL or filter by Cloud Storage.
Click Install, select a variant if available, and fill in the required fields.
Click Install Integration to create the integration with a ready-to-use default blueprint.

API Setup

curl -X POST http://localhost:8084/v1/admin/integration-catalog/postgresql/install \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "PostgreSQL",
    "variant": "default",
    "config": {
      "host": "db.example.com",
      "port": "5432",
      "database": "datafly_events",
      "schema_name": "datafly",
      "table_name": "events",
      "username": "datafly_signal",
      "password": "your_secure_password",
      "ssl_mode": "require"
    },
    "delivery_mode": "server_side"
  }'

Testing

Enable the integration in Signal and trigger a test event on your website.
Connect to the PostgreSQL server and query the target table:

SELECT * FROM datafly.events ORDER BY timestamp DESC LIMIT 10;

Verify that event rows are appearing with correct data.
Test JSONB querying:

SELECT event_id, properties->>'order_id' AS order_id, (properties->>'total')::numeric AS total
FROM datafly.events
WHERE event = 'Order Completed'
ORDER BY timestamp DESC
LIMIT 5;

In Signal, check the Live Events view to confirm delivery status shows as successful.

Troubleshooting

Problem	Solution
Events not appearing in the table	Verify the host, port, database, schema, and table name are correct.
Connection refused / timeout	Check that the PostgreSQL server accepts connections from Signal’s IP. Verify firewall rules, security groups, or `pg_hba.conf`.
`FATAL: password authentication failed`	The username or password is incorrect. Verify the credentials.
`permission denied for schema`	The user lacks `USAGE` on the schema. Run `GRANT USAGE ON SCHEMA datafly TO datafly_signal;`.
`permission denied for table`	The user lacks `INSERT` on the table. Run `GRANT INSERT ON TABLE datafly.events TO datafly_signal;`.
`relation does not exist`	The table does not exist or the schema is not in the search path. Use the fully qualified name `schema.table`.
SSL connection errors	Verify the `ssl_mode` setting. Most managed services require `require` or `verify-full`. Self-hosted servers may need SSL certificate configuration.
`value too long for type`	An event field exceeds the column’s VARCHAR length. Increase the column size with `ALTER TABLE`.

Visit PostgreSQL documentation for full SQL reference, JSONB operators, and performance tuning guides.

MySQL Amazon S3