CREATE LOCATION (Postgres)

Reference and syntax for CREATE LOCATION with a Postgres source.

Private Preview Feature

This feature is currently in private preview. Contact [e-mail] 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'
);

Related

• 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