PostgreSQL

Databases Analytics

Synopsis

Creates a PostgreSQL target that writes records into one table of an existing PostgreSQL database. Each field of a record fills the column with the same name, or the whole record goes into a single JSON column.

Schema

- name: <string>
  description: <string>
  type: postgres
  pipelines: <pipeline[]>
  status: <boolean>
  properties:
    address: <string>
    port: <integer>
    database: <string>
    schema: <string>
    table: <string>
    username: <string>
    password: <string>
    json_column: <string>
    batch_size: <integer>
    max_bytes: <integer>
    timeout: <integer>
    field_format: <string>
    tls:
      status: <boolean>
      verify: <boolean>
      ca_name: <string>
      cert_name: <string>
      key_name: <string>
      passphrase: <string>
      server_name: <string>
      min_tls_version: <string>
      max_tls_version: <string>
    debug:
      status: <boolean>
      dont_send_logs: <boolean>

Configuration

The following fields are used to define the target:

Field	Required	Default	Description
name	Y		Target name
description	N	-	Optional description
type	Y		Must be postgres
pipelines	N	-	Optional post-processor pipelines
status	N	true	Enable/disable the target

Connection

Field	Required	Default	Description
address	Y	-	Host name or IP address of the PostgreSQL server
port	N	5432	Port of the PostgreSQL server
database	Y	-	The database that holds the table
username	N	-	The user that writes to the table. It needs permission to insert into it
password	N	-	Password for that user
timeout	N	30	Seconds one database call may take. At least 1

Table

Field	Required	Default	Description
schema	N	public	The table's schema
table	Y	-	The table to write to. It must already exist
json_column	N	-	The column that receives the whole record. A json, jsonb or text column. Leave it out to fill columns by name. See Columns by Name or Whole Record as JSON

The database, schema, table and JSON column names must start with a letter or an underscore, and use only letters, digits and underscores. Any other name is refused and the target does not start.

Delivery

Field	Required	Default	Description
batch_size	N	1000	The most records in one write. The target's own value first, then the service-level batch_size, then 1000
max_bytes	N	16777216 (16 MiB)	The most bytes of JSON in one write
field_format	N	-	Converts each record to a format such as ECS or OCSF before it is written. See applicable Normalization section

note

max_bytes caps the size of a single request body or batch. It behaves differently from max_size on the file-writing targets, and the difference is easy to get wrong:

• An explicit max_bytes: 0 IS honoured and removes the ceiling on targets that have no ceiling of their own. The value is read with Int64, which returns what you wrote — unlike max_size, which is read with MustInt64 and silently substitutes its default for a zero. Where a target has a fixed ceiling of its own, its page says so and a zero is clamped to that ceiling instead.
• A negative value is rejected at configuration time with max_bytes must not be negative.

The ceiling is validated when the target is built and enforced again on every batch, so a value the receiving service will refuse fails early rather than per request.

TLS

Field	Required	Default	Description
tls.status	N	false	Connect over TLS. TLS stays off unless this is true

TLS material is resolved through the shared client builder, so these keys mean the same thing on every target that uses it. They are nested under a tls: block.

Field	Required	Default	Description
tls.verify	N	true	Verify the server certificate. On by default — set it to false only to accept an untrusted certificate, and only where you control the network path
tls.server_name	N	-	SNI hostname override for the handshake. Use it when the certificate's name does not match the address you connect to
tls.ca_name	N	-	CA bundle used to verify the server certificate. When unset the host trust store is used; when set it replaces the host trust store rather than adding to it
tls.cert_name	N*	-	Client certificate, for mutual TLS
tls.key_name	N*	-	Client private key, for mutual TLS
tls.passphrase	N	-	Passphrase for an encrypted client private key
tls.min_tls_version	N	tls1.2	Lowest protocol version accepted (tls1.0, tls1.1, tls1.2, tls1.3)
tls.max_tls_version	N	tls1.3	Highest protocol version accepted (tls1.0, tls1.1, tls1.2, tls1.3)

* cert_name and key_name are individually optional but must be supplied together — a certificate without its key, or a key without its certificate, is a configuration error.

Scheduling

See Scheduling and Pool Behavior for interval and cron fields shared by all targets.

Debug Options

Field	Required	Default	Description
debug.status	N	false	Enable debug logging
debug.dont_send_logs	N	false	Process logs but don't send to target (testing)

Details

The PostgreSQL target writes records into one table that already exists. It never creates the table or changes it, and it only adds rows. Each batch is written in one go, and each worker uses its own connection to the database.

Columns by Name or Whole Record as JSON

The target works in one of two modes:

• Columns by name, the default: each top-level field of a record fills the column with the same name, with the same upper and lower case. A field with no matching column is skipped. A record that is not a JSON object, or that fills no column, is skipped and counted as dropped.
• Whole record as JSON, when json_column is set: the whole record goes into that one column, and every other column keeps its default. A record that is not valid JSON is written as a JSON string.

Names match exactly, including case. PostgreSQL stores a name created without quotes in lowercase, so a column created as EventTime without quotes is eventtime, and a field named EventTime does not fill it.

The target leaves out generated columns, and identity columns that are always generated, because PostgreSQL refuses a value written to them.

warning

A field with no matching column is skipped without an error. If no field of a record matches any column, the record is dropped, and when a whole batch keeps nothing the Director logs a warning. Check the column names against the fields your pipeline emits before relying on the target.

Prerequisites

1. A running PostgreSQL server, reachable from the Director host
2. The database and the table, already created
3. A user with permission to insert into the table

Examples

Basic

Writing each record's fields into the columns of the same name:

targets:
  - name: postgres_events
    type: postgres
    properties:
      address: "postgres.example.com"
      database: "analytics"
      table: "events"
      username: "datastream"
      password: "secret"

port and schema are left out, so they default to 5432 and public.

Whole Record as JSON

Writing each whole record into a jsonb column:

targets:
  - name: postgres_raw
    type: postgres
    properties:
      address: "postgres.example.com"
      database: "analytics"
      schema: "logs"
      table: "raw_events"
      json_column: "record"
      username: "datastream"
      password: "secret"

Every other column of logs.raw_events keeps its default, such as a timestamp column that defaults to the time of the insert.

With TLS

Connecting over TLS to a server whose certificate comes from a private certificate authority:

targets:
  - name: postgres_tls
    type: postgres
    properties:
      address: "postgres.example.com"
      database: "analytics"
      table: "events"
      username: "datastream"
      password: "secret"
      tls:
        status: true
        ca_name: "postgres-ca.pem"

Troubleshooting

Situation	Result
The database refuses a value, for example one that does not fit its column	The batch is sent again one record at a time. Only the records that still fail are dropped, and they are counted as dropped
The connection or the server has a temporary problem	The batch is retried
A column was removed from the table	The target reads the table's columns again once and retries the batch
The table does not exist when the target starts	The target stops with an error. Create the table first and give the user access to it

The table was not found

postgres table analytics.public.events was not found, or it has no columns this target can write. Create the table first, and give the user access to it

Cause: the table named in schema and table does not exist, the user cannot see it, or every one of its columns is generated. The target reads the table's columns when it starts, so this shows up at startup rather than on the first batch.

Fix: create the table, or correct schema and table, and grant the user access to it. Remember that a name created without quotes is stored in lowercase.

Records are dropped although nothing fails

Cause: the fields of the records match no column of the table. Names match exactly, including case.

Fix: rename the columns after the fields your pipeline emits, or set json_column to store the whole record in one column.

Configuration errors

These stop the target from starting:

Reason text	Fix
address is required for postgres target	Set address
database is required for postgres target	Set database
table is required for postgres target	Set table
invalid database name "..." for postgres target	Use a name of letters, digits and underscores that starts with a letter or an underscore. The same rule applies to schema, table and json_column
timeout must be at least 1 second for postgres target, got ...	Set timeout to 1 or more
batch_size must be a whole number greater than 0 for postgres target, got ...	Use a whole number of at least 1, or leave batch_size out to use the service-level value
failed to build TLS config: ...	The rest of the line names the TLS setting at fault