ContextQMD
Back to Materialize

CREATE SOURCE: PostgreSQL (New Syntax)

doc/user/content/sql/create-source/postgres-v2.md

1233.7 KB
Original Source

{{< private-preview />}}

{{< source-versioning-disambiguation is_new=true other_ref="old reference page" include_blurb=true >}}

{{% create-source-intro external_source="PostgreSQL" version="11+" create_table="/sql/create-table/" %}}

Prerequisites

{{% include-from-yaml data="postgres_source_details" name="postgres-source-prereq" %}}

Syntax

{{% include-syntax file="examples/create_source_postgres" example="syntax" %}}

Details

Ingesting data

After a source is created, you can create tables from the source, referencing the tables in the publication, to start ingesting data. You can create multiple tables that reference the same table in the publication.

See CREATE TABLE FROM SOURCE for details.

Handling table schema changes

The use of the CREATE SOURCE with the new CREATE TABLE FROM SOURCE allows for the handling of certain upstream DDL changes without downtime.

See CREATE TABLE FROM SOURCE for details.

Supported types

With the new syntax, after a PostgreSQL source is created, you CREATE TABLE FROM SOURCE to create a corresponding table in Matererialize and start ingesting data.

{{% include-from-yaml data="postgres_source_details" name="postgres-supported-types" %}}

For more information, including strategies for handling unsupported types, see CREATE TABLE FROM SOURCE.

Upstream table truncation restrictions

{{% include-from-yaml data="postgres_source_details" name="postgres-truncation-restriction" %}}

For additional considerations, see also CREATE TABLE.

Publication membership

{{% include-from-yaml data="postgres_source_details" name="postgres-publication-membership" %}}

{{% include-from-yaml data="postgres_source_details" name="postgres-publication-membership-mitigation-legacy" %}}

PostgreSQL replication slots

When you define a source, Materialize will automatically create a replication slot in the upstream PostgreSQL database (see PostgreSQL replication slots). Each source ingests the raw replication stream data for all tables in the specified publication using a single replication slot. This allows you to minimize the performance impact on the upstream database as well as reuse the same source across multiple materializations.

The name of the replication slot created by Materialize is prefixed with materialize_. In Materialize, you can query the mz_internal.mz_postgres_sources to find the replication slots created:

mzsql
SELECT id, replication_slot FROM mz_internal.mz_postgres_sources;

    id   |             replication_slot
---------+----------------------------------------------
  u8     | materialize_7f8a72d0bf2a4b6e9ebc4e61ba769b71

{{< tip >}}

{{% include-from-yaml data="postgres_source_details" name="postgres-replication-slots-tip-list" %}}

{{</ tip >}}

Examples

Prerequisites

{{% include-from-yaml data="postgres_source_details" name="postgres-source-prereq" %}}

Create a source {#create-source-example}

{{% include-example file="examples/create_source_postgres" example="example-prereq" %}}

{{% include-example file="examples/create_source_postgres" example="create-source" %}}

{{% include-example file="examples/create_source_postgres" example="create-table" %}}