ContextQMD
Back to Materialize

Ingest data from Amazon RDS

doc/user/content/ingest-data/postgres/amazon-rds.md

12318.7 KB
Original Source

This page shows you how to stream data from Amazon RDS 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 Amazon RDS

1. Enable logical replication

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

As a first step, you need to make sure logical replication is enabled.

  1. As a user with the rds_superuser role, use psql (or your preferred SQL client) to connect to your database.

  2. Check if logical replication is enabled:

    postgres
    SELECT name, setting
  FROM pg_settings
  WHERE name = 'rds.logical_replication';
    <p></p>
    nofmt
            name             | setting
-------------------------+---------
rds.logical_replication  | off
(1 row)

  3. Using the AWS Management Console, create a DB parameter group in RDS.

    • Set Parameter group family to your PostgreSQL version.
    • Set Type to DB Parameter Group.
    • Set Engine type to PostgreSQL.

  4. Edit the new parameter group and set the rds.logical_replication parameter to 1.

  5. Associate the DB parameter group with your database.

    Use the Apply Immediately option to immediately reboot your database and apply the change. Keep in mind that rebooting the RDS instance can affect database performance.

    Do not move on to the next step until the database Status is Available in the RDS Console.

  6. Back in the SQL client connected to PostgreSQL, verify that replication is now enabled:

    postgres
    SELECT name, setting
  FROM pg_settings
  WHERE name = 'rds.logical_replication';
    <p></p>
    nofmt
            name             | setting
-------------------------+---------
rds.logical_replication  | on
(1 row)

    If replication is still not enabled, reboot the database.

2. Create a publication and a replication user

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

B. (Optional) Configure network security

{{< note >}} If you are prototyping and your RDS 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 security group to allow connections from a set of static Materialize IP addresses.

  • Use AWS PrivateLink: If your database is running in a private network, you can use AWS PrivateLink to connect Materialize to the database. For details, see AWS PrivateLink.

  • 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.

{{< tabs >}}

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

  1. In the 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. In the AWS Management Console, add an inbound rule to your RDS security group for each IP address from the previous step.

    In each rule:

    • Set Type to PostgreSQL.
    • Set Source to the IP address in CIDR notation.

{{< /tab >}}

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

AWS PrivateLink lets you connect Materialize to your RDS instance without exposing traffic to the public internet. To use AWS PrivateLink, you create a network load balancer in the same VPC as your RDS instance and a VPC endpoint service that Materialize connects to. The VPC endpoint service then routes requests from Materialize to RDS via the network load balancer.

{{< note >}} Materialize provides a Terraform module that automates the creation and configuration of AWS resources for a PrivateLink connection. For more details, see the Terraform module repository. {{</ note >}}

  1. Get the IP address of your RDS instance. You'll need this address to register your RDS instance as the target for the network load balancer in the next step.

    To get the IP address of your RDS instance:

    1. Select your database in the RDS Console.

    2. Find your RDS endpoint under Connectivity & security.

    3. Use the dig or nslooklup command to find the IP address that the endpoint resolves to:

      sh
      dig +short <RDS_ENDPOINT>

  2. Create a dedicated target group for your RDS instance.

    • Choose the IP addresses type.

    • Set the protocol and port to TCP and 5432.

    • Choose the same VPC as your RDS instance.

    • Use the IP address from the previous step to register your RDS instance as the target.

    Warning: The IP address of your RDS instance can change without notice. For this reason, it's best to set up automation to regularly check the IP of the instance and update your target group accordingly. You can use a lambda function to automate this process - see Materialize's Terraform module for AWS PrivateLink for an example. Another approach is to configure an EC2 instance as an RDS router for your network load balancer.

  3. Create a network load balancer.

    • For Network mapping, choose the same VPC as your RDS instance and select all of the availability zones and subnets that you RDS instance is in.

    • For Listeners and routing, set the protocol and port to TCP and 5432 and select the target group you created in the previous step.

  4. In the security group of your RDS instance, allow traffic from the network load balancer.

    If client IP preservation is disabled, the easiest approach is to add an inbound rule with the VPC CIDR of the network load balancer. If you don't want to grant access to the entire VPC CIDR, you can add inbound rules for the private IP addresses of the load balancer subnets.

    • To find the VPC CIDR, go to your network load balancer and look under Network mapping.
    • To find the private IP addresses of the load balancer subnets, go to Network Interfaces, search for the name of the network load balancer, and look on the Details tab for each matching network interface.

  5. Create a VPC endpoint service.

    • For Load balancer type, choose Network and then select the network load balancer you created in the previous step.

    • After creating the VPC endpoint service, note its Service name. You'll use this service name when connecting Materialize later.

    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. Go back to the target group you created for the network load balancer and make sure that the health checks are reporting the targets as healthy.

{{< /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.

{{< note >}} Materialize provides a Terraform module that automates the creation and configuration of resources for an SSH tunnel. For more details, see the Terraform module repository. {{</ note >}}

  1. Launch an EC2 instance to serve as your SSH bastion host.

    • Make sure the instance is publicly accessible and in the same VPC as your RDS instance.
    • Add a key pair and note the username. You'll use this username when connecting Materialize to your bastion host.

    Warning: Auto-assigned public IP addresses can change in certain cases.

    For this reason, it's best to associate an elastic IP address 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. For each static egress IP, add an inbound rule to your SSH bastion host's security group.

      In each rule:

      • Set Type to PostgreSQL.
      • Set Source to the IP address in CIDR notation.

  3. In the security group of your RDS instance, add an inbound rule to allow traffic from the SSH bastion host.

    • Set Type to All TCP.
    • Set Source to Custom and select the bastion host's security group.

{{< /tab >}}

{{< /tabs >}}

{{< /tab >}}

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

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

{{< tabs >}}

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

  1. In the AWS Management Console, add an inbound rule to your RDS security group to allow traffic from Materialize IPs.

    In each rule:

    • Set Type to PostgreSQL.
    • Set Source to the IP address in CIDR notation.

{{< /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.

{{< note >}} Materialize provides a Terraform module that automates the creation and configuration of resources for an SSH tunnel. For more details, see the Terraform module repository. {{</ note >}}

  1. Launch an EC2 instance to serve as your SSH bastion host.

    • Make sure the instance is publicly accessible and in the same VPC as your RDS instance.

    • Add a key pair and note the username. You'll use this username when connecting Materialize to your bastion host.

    Warning: Auto-assigned public IP addresses can change in certain cases. For this reason, it's best to associate an elastic IP address to your bastion host.

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

  3. In the security group of your RDS instance, add an inbound rule to allow traffic from the SSH bastion host.

    • Set Type to All TCP.
    • Set Source to Custom and select the bastion host's security group.

{{< /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-rds" indent="true"%}}

{{< /tab >}}

{{< tab "Use AWS PrivateLink (Cloud-only)" >}}

  1. {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="create-privatelink-connection-rds-intro" indent="true" %}}

    {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="create-privatelink-connection-rds-in-region" indent="true" %}}

    {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="create-privatelink-connection-rds-in-region-options" indent="true" %}}

    {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="create-privatelink-connection-rds-cross-region" indent="true" %}}

    {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="create-privatelink-connection-rds-cross-region-options" indent="true" %}}

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

    {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="get-principal-privatelink-connection-results" indent="true" %}}

  3. {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="update-vpc-endpoint" indent="true" %}}

  4. {{% include-example file="examples/ingest_data/postgres/create_connection_privatelink_cloud" example="approve-connection-request" indent="true" %}}

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

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

  7. {{% 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-rds" 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" %}}

  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-rds" 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" %}}