PostgreSQL

A PostgreSQL connection holds reusable database credentials. Each generator names a table and produces one row object whose field names become insert columns.

Suppose the public.customers table should receive:

{
  "table": "public.customers",
  "row": {
    "customer_id": "CUST-001",
    "full_name": "Asha Rao",
    "status": "active"
  }
}

Insert customer rows

connections:
  customerDb:
    type: postgres
    host: localhost
    port: 5432
    database: synthtraffic
    user: postgres
    sslmode: disable

generators:
  - name: customers
    connection: customerDb
    config:
      maxEvents: 2
    vars:
      customerId: =seq(format=CUST-%03d)
    table: public.customers
    schema:
      policy: create-if-missing
      columns:
        customer_id: text primary key
        full_name: text not null
        status: varchar(20) default 'active'
    row:
      customer_id: $customerId
      full_name: =cycle('Asha Rao', 'Liam Smith')
      status: active

The connection and generator divide the work:

• connections.customerDb describes how to reach the database.
• connection: customerDb selects it for the customers generator.
• table and row are required PostgreSQL output fields.
• schema says what Synthtraffic may do when the table is missing or incomplete.
• vars.customerId calculates one ID for reuse in the row.

Preview without PostgreSQL

Neither command opens a database connection:

sample prints a short preview to your terminal. It does not wait for real-time pacing, and it never opens Kafka, PostgreSQL, or other destinations.

synthtraffic sample postgresql.yaml --events 2 --seed 42

docker run --rm \
  --env-file ./license.env \
  --volume "${PWD}/postgresql.yaml:/work/postgresql.yaml:ro" \
  synthtraffic/synthtraffic:latest \
  sample /work/postgresql.yaml --events 2 --seed 42

run --stdout prints the destination-shaped envelope locally without opening the configured connection. Remove --stdout when you are ready to send to that destination.

synthtraffic run postgresql.yaml --stdout --events 2 --seed 42

docker run --rm \
  --env-file ./license.env \
  --volume "${PWD}/postgresql.yaml:/work/postgresql.yaml:ro" \
  synthtraffic/synthtraffic:latest \
  run /work/postgresql.yaml --stdout --events 2 --seed 42

Full flag lists: sample and run. Install and license: Install.

Connection forms

Choose one of these forms. url takes precedence over dsn; either takes precedence over separate host fields.

# URL
connections:
  customerDb:
    type: postgres
    url: =env(POSTGRES_URL)

# libpq-style DSN
connections:
  customerDb:
    type: postgres
    dsn: =env(POSTGRES_DSN)

# Separate fields
connections:
  customerDb:
    type: postgres
    host: localhost
    port: 5432
    database: synthtraffic
    user: postgres
    password: =env(POSTGRES_PASSWORD)
    sslmode: disable

Connection fields may contain literals or env() only. There is no configurable pool-size field in the DSL.

Generator fields

Table and column identifiers must start with a letter or underscore and then contain only letters, digits, or underscores. PostgreSQL generators cannot use Kafka or HTTP output fields such as topic, headers, or body.

Schema policies

columns is required for create-if-missing and drop-and-create. manual is the safest policy for a database whose schema is managed elsewhere.

Column declarations

Each schema.columns value is a literal, constrained column declaration—not arbitrary SQL:

schema:
  policy: create-if-missing
  columns:
    customer_id: text primary key
    full_name: text not null
    status: varchar(20) default 'active'
    account_id: bigint references public.accounts(id)

Supported types are uuid, text, integer, bigint, double precision, boolean, bytea, timestamptz, timestamp with time zone, jsonb, numeric, varchar(N), and numeric(P,S).

Supported clauses are NULL, NOT NULL, DEFAULT, PRIMARY KEY, and REFERENCES table(column). Defaults may be a quoted string, number, true, false, null, or now(). Semicolons, comments, and unsupported clauses are rejected.

You may omit a type only when the same column exists in row; Synthtraffic then infers a type from that generated value. Explicit types are easier to review and are recommended for shared scenarios.

Row values and failures

• Strings fit text, varchar, and UUID columns.
• Integers fit integer types; integers and decimals fit numeric types.
• Booleans fit boolean columns and timestamps from now() fit timestamptz.
• Objects and lists are encoded as JSON bytes for json or jsonb columns.
• A missing table under manual, an incompatible value, a constraint violation, or an insert failure stops the run.

Parents must exist before rows that reference them. Use schedule stages to insert parent generators before child generators.

Insert for real

Before removing --stdout:

1. Create the database and user.
2. Set the password, URL, or DSN environment variable used by the scenario.
3. Create the table yourself when using manual, or review the declared columns when using another policy.
4. Run:

synthtraffic run postgresql.yaml --events 2 --seed 42

Synthtraffic batches inserts and flushes pending rows before a stage or successful run completes. It validates the table structure before insertion; it does not silently coerce incompatible row values.

Related

• Connection mental model: Connections
• Parent rows before child rows: Schedules
• Environment secrets: Strings and env