Ingest data from Azure DB

This page shows you how to stream data from Azure DB for PostgreSQL to Materialize using the PostgreSQL source.

{{< tip >}} {{< guided-tour-blurb-for-ingest-data >}} {{< /tip >}}

Before you begin

{{% include-from-yaml data="ingest_postgres" name="before-you-begin" %}}

A. Configure Azure DB

1. Enable logical replication

Materialize uses PostgreSQL's logical replication protocol to track changes in your database and propagate them to Materialize.

To enable logical replication in Azure DB, see the Azure documentation.

2. Create a publication and a replication user

{{% include-from-yaml data="ingest_postgres" name="create-a-publication-other" %}}

B. (Optional) Configure network security

{{< note >}} If you are prototyping and your AzureDB instance is publicly accessible, you can skip this step. For production scenarios, we recommend configuring one of the network security options below. {{</ note >}}

{{< tabs >}}

{{< tab "Cloud">}}

There are various ways to configure your database's network to allow Materialize to connect:

• Allow Materialize IPs: If your database is publicly accessible, you can configure your database's firewall to allow connections from a set of static Materialize IP addresses.

• Use an SSH tunnel: If your database is running in a private network, you can use an SSH tunnel to connect Materialize to the database.

Select the option that works best for you.

{{< tabs >}}

{{< tab "Allow Materialize IPs">}}

1. In the Materialize console's SQL Shell, or your preferred SQL client connected to Materialize, find the static egress IP addresses for the Materialize region you are running in:

   mzsqlCopy

   SELECT * FROM mz_egress_ips;
   

2. Update your Azure DB firewall rules to allow traffic from each IP address from the previous step.

{{< /tab >}}

{{< tab "Use an SSH tunnel">}}

To create an SSH tunnel from Materialize to your database, you launch an instance to serve as an SSH bastion host, configure the bastion host to allow traffic only from Materialize, and then configure your database's private network to allow traffic from the bastion host.

1. Launch an Azure VM with a static public IP address to serve as your SSH bastion host.

   • Make sure the VM is publicly accessible and in the same VPC as your database.
   • Add a key pair and note the username. You'll use this username when connecting Materialize to your bastion host.
   • Make sure the VM has a static public IP address. You'll use this IP address when connecting Materialize to your bastion host.

2. Configure the SSH bastion host to allow traffic only from Materialize.

   1. In the Materialize console's SQL Shell, or your preferred SQL client connected to Materialize, get the static egress IP addresses for the Materialize region you are running in:

      mzsqlCopy

      SELECT * FROM mz_egress_ips;
      

   2. Update your SSH bastion host's firewall rules to allow traffic from each IP address from the previous step.

3. Update your Azure DB firewall rules to allow traffic from the SSH bastion host.

{{< /tab >}}

{{< /tabs >}}

{{< /tab >}}

{{< tab "Self-Managed">}}

{{% include-md file="shared-content/self-managed/configure-network-security-intro.md" %}}

{{< tabs >}}

{{< tab "Allow Materialize IPs">}}

1. Update your Azure DB firewall rules to allow traffic from Materialize IPs.

{{< /tab >}}

{{< tab "Use an SSH tunnel">}}

To create an SSH tunnel from Materialize to your database, you launch an instance to serve as an SSH bastion host, configure the bastion host to allow traffic only from Materialize, and then configure your database's private network to allow traffic from the bastion host.

1. Launch an Azure VM with a static public IP address to serve as your SSH bastion host.

   • Make sure the VM is publicly accessible and in the same VPC as your database.
   • Add a key pair and note the username. You'll use this username when connecting Materialize to your bastion host.
   • Make sure the VM has a static public IP address. You'll use this IP address when connecting Materialize to your bastion host.

2. Configure the SSH bastion host to allow traffic only from Materialize.

3. Update your Azure DB firewall rules to allow traffic from the SSH bastion host.

{{< /tab >}}

{{< /tabs >}}

{{< /tab >}}

{{< /tabs >}}

C. Ingest data in Materialize

1. (Optional) Create a cluster

{{< note >}} If you are prototyping and already have a cluster to host your PostgreSQL source (e.g. quickstart), you can skip this step. For production scenarios, we recommend separating your workloads into multiple clusters for resource isolation. {{< /note >}}

{{% include-from-yaml data="ingest_postgres" name="create-a-cluster" %}}

2. Create a connection

Once you have configured your network, create a connection in Materialize per your networking configuration.

{{< tabs >}}

{{< tab "Allow Materialize IPs">}}

1. {{% include-example file="examples/ingest_data/postgres/create_connection_ips_cloud" example="create-secret" indent="true" %}}

2. {{% include-example file="examples/ingest_data/postgres/create_connection_ips_cloud" example="create-connection" indent="true" %}}

   {{% include-example file="examples/ingest_data/postgres/create_connection_ips_cloud" example="create-connection-options-general" indent="true" %}}

{{< /tab >}}

{{< tab "Use an SSH tunnel">}}

1. {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="create-ssh-tunnel-connection" indent="true" %}}

   {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="create-ssh-tunnel-connection-options" indent="true" %}}

2. {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="get-public-keys-general" indent="true" %}}

3. {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="login-to-ssh-bastion-host" indent="true" %}}

4. {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="validate-ssh-tunnel-connection" indent="true" %}}

5. {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="create-secret" indent="true" %}}

6. {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="create-connection" indent="true" %}}

   {{% include-example file="examples/ingest_data/postgres/create_connection_ssh_cloud" example="create-connection-options-general" indent="true" %}}

{{< /tab >}} {{< /tabs >}}

3. Start ingesting data

{{% include-example file="examples/ingest_data/postgres/create_source_cloud" example="ingest-data-step" %}}

4. Monitor the ingestion status

{{% include-from-yaml data="ingest_postgres" name="check-the-ingestion-status" %}}

5. Right-size the cluster

{{% include-from-yaml data="ingest_postgres" name="right-size-the-cluster" %}}

D. Explore your data

{{% include-from-yaml data="ingest_postgres" name="next-steps" %}}

Considerations

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