CREATE LOCATION (Postgres)

Private Preview FeatureThis feature is currently in private preview. Contact support@firebolt.io to request early access.

Creates a location object that stores the connection details, credentials, and publication for a Postgres database, for use with change data capture streams.

Syntax

CREATE LOCATION [IF NOT EXISTS] <location_name> WITH (
    SOURCE      = 'POSTGRES',
    HOST        = '<host>',
    [PORT       = '<port>',]
    DATABASE    = '<database>',
    USER        = '<user>',
    PASSWORD    = '<password>',
    PUBLICATION = '<publication>'
    [, DESCRIPTION = '<description>']
);

Parameters

Parameter	Description
`<location_name>`	A unique identifier for the location within your account.
`SOURCE`	Must be `'POSTGRES'`.
`HOST`	Hostname of the Postgres server. Required.
`PORT`	Port of the Postgres server. Optional; defaults to `5432`.
`DATABASE`	Database to connect to. Required.
`USER`	Role to connect as. Needs the `REPLICATION` attribute and `SELECT` on the captured tables. Required.
`PASSWORD`	Password for the role. Required.
`PUBLICATION`	The Postgres publication that covers the tables to capture. Required. For partitioned source tables, each leaf partition additionally needs its own dedicated publication; see Partitioned source tables.
`DESCRIPTION`	Optional metadata describing the location’s purpose.

TLS

Connections to any host other than localhost use TLS, verified against the operating system trust store of the Firebolt nodes. There is no connection-level option to disable verification or point at a CA file; to use a private certificate authority, install its certificate into the nodes’ system trust store.

Example

CREATE LOCATION pg_prod WITH (
    SOURCE      = 'POSTGRES',
    HOST        = 'pg.example.internal',
    PORT        = '5432',
    DATABASE    = 'app',
    USER        = 'firebolt_cdc',
    PASSWORD    = '********',
    PUBLICATION = 'firebolt_cdc',
    DESCRIPTION = 'Production OLTP primary'
);

Postgres CDC: source prerequisites and end-to-end setup.
CREATE STREAM: bind a stream to a table in this location.
CREATE LOCATION: location objects in general.
DROP LOCATION

CREATE LOCATION (MongoDB)

CREATE LOCATION (Amazon S3)

⌘I

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

TLS

Example

​Syntax

​Parameters

​TLS

​Example

​Related

Syntax

Parameters

TLS

Example

Related