ContextQMD
Back to Materialize

Ingest data from self-hosted PostgreSQL

doc/user/content/ingest-data/postgres/self-hosted.md

12313.1 KB
Original Source

This page shows you how to stream data from a self-hosted PostgreSQL database 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 PostgreSQL

1. Enable logical replication

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

  1. As a superuser, use psql (or your preferred SQL client) to connect to your PostgreSQL database.

  2. Check if logical replication is enabled; that is, check if the wal_level is set to logical:

    postgres
    SHOW wal_level;

  3. If wal_level setting is not set to logical:

    1. In the database configuration file (postgresql.conf), set wal_level value to logical.

    2. Restart the database in order for the new wal_level to take effect. Restarting can affect database performance.

    3. In the SQL client connected to PostgreSQL, verify that replication is now enabled (i.e., verify wal_level setting is set to logical).

      postgres
      SHOW wal_level;

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 PostgreSQL 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:

    mzsql
    SELECT * FROM mz_egress_ips;

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

{{< /tab >}}

{{< tab "Use AWS PrivateLink">}}

Materialize can connect to a PostgreSQL database through an AWS PrivateLink service. Your PostgreSQL database must be running on AWS in order to use this option.

  1. Create a dedicated target group for your Postgres instance with the following details:

    a. Target type as IP address.

    b. Protocol as TCP.

    c. Port as 5432, or the port that you are using in case it is not 5432.

    d. Make sure that the target group is in the same VPC as the PostgreSQL instance.

    e. Click next, and register the respective PostgreSQL instance to the target group using its IP address.

  2. Create a Network Load Balancer that is enabled for the same subnets that the PostgreSQL instance is in.

  3. Create a TCP listener for your PostgreSQL instance that forwards to the corresponding target group you created.

  4. Once the TCP listener has been created, make sure that the health checks are passing and that the target is reported as healthy.

    If you have set up a security group for your PostgreSQL instance, you must ensure that it allows traffic on the health check port.

    Remarks:

    a. Network Load Balancers do not have associated security groups. Therefore, the security groups for your targets must use IP addresses to allow traffic.

    b. You can't use the security groups for the clients as a source in the security groups for the targets. Therefore, the security groups for your targets must use the IP addresses of the clients to allow traffic. For more details, check the AWS documentation.

  5. Create a VPC endpoint service and associate it with the Network Load Balancer that you’ve just created.

    Note the service name that is generated for the endpoint service.

    Remarks:

    By disabling Acceptance Required, while still strictly managing who can view your endpoint via IAM, Materialze will be able to seamlessly recreate and migrate endpoints as we work to stabilize this feature.

  6. In Materialize, create a AWS PRIVATELINK connection that references the endpoint service that you created in the previous step.

    mzsql
    CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
   SERVICE NAME 'com.amazonaws.vpce.<region_id>.vpce-svc-<endpoint_service_id>',
   AVAILABILITY ZONES ('use1-az1', 'use1-az2', 'use1-az3')
);

    Update the list of the availability zones to match the ones that you are using in your AWS account.

  7. Retrieve the AWS principal for the AWS PrivateLink connection you just created:

    mzsql
    SELECT principal
FROM mz_aws_privatelink_connections plc
JOIN mz_connections c ON plc.id = c.id
WHERE c.name = 'privatelink_svc';

                                     principal
---------------------------------------------------------------------------
 arn:aws:iam::664411391173:role/mz_20273b7c-2bbe-42b8-8c36-8cc179e9bbc3_u1

    Follow the instructions in the AWS PrivateLink documentation to configure your VPC endpoint service to accept connections from the provided AWS principal.

    If your AWS PrivateLink service is configured to require acceptance of connection requests, you must manually approve the connection request from Materialize after executing the CREATE CONNECTION statement. For more details, check the AWS PrivateLink documentation.

    Note: It might take some time for the endpoint service connection to show up, so you would need to wait for the endpoint service connection to be ready before you create a source.

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

To create an SSH tunnel from Materialize to your database, you launch an VM 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 a VM 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:

      mzsql
      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 database 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 database firewall rules to allow traffic from Materialize.

{{< /tab >}}

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

To create an SSH tunnel from Materialize to your database, you launch an VM 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 a VM 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 database 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 AWS PrivateLink (Cloud-only)" >}}

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

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

    {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="create-connection-options-self-hosted" 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-aurora-rds-self-hosted" 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" %}}