Ingest data from Amazon RDS

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

Before you begin

  • Make sure you are running PostgreSQL 11 or higher.

  • Make sure you have access to your PostgreSQL instance via a SQL client (e.g. psql).

You’ll use the Materialize SQL shell, or your preferred SQL client, to interact with Materialize.

Step 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 to connect to your database.

  2. Check if logical replication is enabled:

    SELECT name, setting
      FROM pg_settings
      WHERE name = 'rds.logical_replication';
    

            name             | setting
    -------------------------+---------
    rds.logical_replication  | off
    (1 row)
    
  3. Create a custom RDS parameter group.

    • Set Parameter group family to your PostgreSQL version.
    • Set Type to DB Parameter Group.
  4. Edit the new parameter group and set the rds.logical_replication parameter to 1.

  5. Associate the RDS parameter group to your database.

    Use the Apply Immediately option. The database must be rebooted in order for the parameter group association to take effect. 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 your psql shell, verify that replication is now enabled:

    SELECT name, setting
      FROM pg_settings
      WHERE name = 'rds.logical_replication';
    

            name             | setting
    -------------------------+---------
    rds.logical_replication  | on
    (1 row)
    

    If replication is still not enabled, reboot the database.

Step 2. Create a publication

Once logical replication is enabled, create a publication with the tables that you want to replicate to Materialize. You’ll also need a user for Materialize with sufficient privileges to manage replication.

  1. As a superuser, use psql to connect to your database.

  2. For each table that you want to replicate to Materialize, set the replica identity to FULL:

    ALTER TABLE <table1> REPLICA IDENTITY FULL;
    
    ALTER TABLE <table2> REPLICA IDENTITY FULL;
    

    REPLICA IDENTITY FULL ensures that the replication stream includes the previous data of changed rows, in the case of UPDATE and DELETE operations. This setting enables Materialize to ingest PostgreSQL data with minimal in-memory state. However, you should expect increased disk usage in your PostgreSQL database.

  3. Create a publication with the tables you want to replicate:

    For specific tables:

    CREATE PUBLICATION mz_source FOR TABLE <table1>, <table2>;
    

    For all tables in the database:

    CREATE PUBLICATION mz_source FOR ALL TABLES;
    

    The mz_source publication will contain the set of change events generated from the specified tables, and will later be used to ingest the replication stream.

    Be sure to include only the tables you need. If the publication includes additional tables, Materialize will waste resources on ingesting and then immediately discarding the data.

  4. Create a user for Materialize, if you don’t already have one:

    CREATE USER materialize PASSWORD '<password>';
    
  5. Grant the user permission to manage replication:

    GRANT rds_replication TO materialize;
    
  6. Grant the user the required permissions on the tables you want to replicate:

    GRANT CONNECT ON DATABASE <dbname> TO materialize;
    
    GRANT USAGE ON SCHEMA <schema> TO materialize;
    
    GRANT SELECT ON <table1> TO materialize;
    
    GRANT SELECT ON <table2> TO materialize;
    

    Once connected to your database, Materialize will take an initial snapshot of the tables in your publication. SELECT privileges are required for this initial snapshot.

    If you expect to add tables to your publication, you can grant SELECT on all tables in the schema instead of naming the specific tables:

    GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO materialize;
    

Step 3. Configure network security

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 or Use an SSH tunnel: If your database is running in a private network, you can use either AWS PrivateLink or an SSH tunnel to connect Materialize to the database.

Select the option that works best for you.

  1. In the psql shell connected to Materialize, find the static egress IP addresses for the Materialize region you are running in:

    SELECT * FROM mz_egress_ips;
    
  2. In the RDS 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.
PREVIEW This feature is in public preview. It may have performance or stability issues.

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

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

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.
  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 psql shell connected to Materialize, get the static egress IP addresses for the Materialize region you are running in:

      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.

Step 4. Create an ingestion cluster

In Materialize, a cluster is an isolated environment, similar to a virtual warehouse in Snowflake. When you create a cluster, you choose the size of its compute resource allocation based on the work you need the cluster to do, whether ingesting data from a source, computing always-up-to-date query results, serving results to clients, or a combination.

In this case, you’ll create 1 new small cluster for ingesting source data from your PostgreSQL database.

  1. In the Materialize SQL shell, or your preferred SQL client connected to Materialize, use the CREATE CLUSTER command to create the new cluster:

    CREATE CLUSTER ingest_postgres (SIZE = 'small');
    

    A cluster of size small should be enough to process the initial snapshot of the tables in your publication. For very large snapshots, consider using a larger size to speed up processing. Once the snapshot is finished, you can readjust the size of the cluster to fit the volume of changes being replicated from your upstream PostgeSQL database.

Step 5. Start ingesting data

Now that you’ve configured your database network and created an ingestion cluster, you can connect Materialize to your PostgreSQL database and start ingesting data. The exact steps depend on your networking configuration, so start by selecting the relevant option.

  1. In the psql shell connected to Materialize, use the CREATE SECRET command to securely store the password for the materialize PostgreSQL user you created earlier:

    CREATE SECRET pgpass AS '<PASSWORD>';
    
  2. Use the CREATE CONNECTION command to create a connection object with access and authentication details for Materialize to use:

    CREATE CONNECTION pg_connection TO POSTGRES (
      HOST '<host>',
      PORT 5432,
      USER 'materialize',
      PASSWORD SECRET pgpass,
      SSL MODE 'require',
      DATABASE '<database>'
      );
    
    • Replace <host> with your RDS endpoint. To find your RDS endpoint, select your database in the RDS Console, and look under Connectivity & security.

    • Replace <database> with the name of the database containing the tables you want to replicate to Materialize.

  3. Use the CREATE SOURCE command to connect Materialize to your RDS instance and start ingesting data from the publication you created earlier:

    CREATE SOURCE mz_source
      IN CLUSTER ingest_postgres
      FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
      FOR ALL TABLES;
    

    To ingest data from specific schemas or tables in your publication, use FOR SCHEMAS (<schema1>,<schema2>) or FOR TABLES (<table1>, <table2>) instead of FOR ALL TABLES.

  4. After source creation, you can handle upstream schema changes for specific replicated tables using the ALTER SOURCE...{ADD | DROP} SUBSOURCE syntax.

  1. In the psql shell connected to Materialize, use the CREATE CONNECTION command to create an AWS PrivateLink connection:

    CREATE CONNECTION privatelink_svc TO AWS PRIVATELINK (
      SERVICE NAME 'com.amazonaws.vpce.us-east-1.vpce-svc-0356210a8a432d9e9',
      AVAILABILITY ZONES ('use1-az1', 'use1-az2', 'use1-az3')
    );
    
    • Replace the SERVICE NAME value with the service name you noted earlier.

    • Replace the AVAILABILITY ZONES list with the IDs of the availability zones in your AWS account.

      To find your availability zone IDs, select your database in the RDS Console and click the subnets under Connectivity & security. For each subnet, look for Availability Zone ID (e.g., use1-az6), not Availability Zone (e.g., us-east-1d).

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

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

       id   |                                 principal
    --------+---------------------------------------------------------------------------
     u1     | arn:aws:iam::664411391173:role/mz_20273b7c-2bbe-42b8-8c36-8cc179e9bbc3_u1
    
  3. Update your VPC endpoint service to accept connections from the AWS principal.

  4. If your AWS PrivateLink service is configured to require acceptance of connection requests, manually approve the connection request from Materialize.

    Note: It can take some time for the connection request to show up. Do not move on to the next step until you’ve approved the connection.

  5. Validate the AWS PrivateLink connection you created using the VALIDATE CONNECTION command:

    VALIDATE CONNECTION privatelink_svc;
    

    If no validation error is returned, move to the next step.

  6. Use the CREATE SECRET command to securely store the password for the materialize PostgreSQL user you created earlier:

    CREATE SECRET pgpass AS '<PASSWORD>';
    
  7. Use the CREATE CONNECTION command to create another connection object, this time with database access and authentication details for Materialize to use:

    CREATE CONNECTION pg_connection TO POSTGRES (
      HOST '<host>',
      PORT 5432,
      USER 'materialize',
      PASSWORD SECRET pgpass,
      DATABASE '<database>',
      AWS PRIVATELINK privatelink_svc
      );
    
    • Replace <host> with your RDS endpoint. To find your RDS endpoint, select your database in the RDS Console, and look under Connectivity & security.

    • Replace <database> with the name of the database containing the tables you want to replicate to Materialize.

  8. Use the CREATE SOURCE command to connect Materialize to your RDS instance via AWS PrivateLink and start ingesting data from the publication you created earlier:

    CREATE SOURCE mz_source
      IN CLUSTER ingest_postgres
      FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
      FOR ALL TABLES;
    

    To ingest data from specific schemas or tables in your publication, use FOR SCHEMAS (<schema1>,<schema2>) or FOR TABLES (<table1>, <table2>) instead of FOR ALL TABLES.

  1. In the psql shell connected to Materialize, use the CREATE CONNECTION command to create an SSH tunnel connection:

    CREATE CONNECTION ssh_connection TO SSH TUNNEL (
        HOST '<SSH_BASTION_HOST>',
        PORT <SSH_BASTION_PORT>,
        USER '<SSH_BASTION_USER>'
    );
    
    • Replace <SSH_BASTION_HOST> and <SSH_BASTION_PORT> with the public IP address and port of the SSH bastion host you created earlier.
    • Replace <SSH_BASTION_USER> with the username for the key pair you created for your SSH bastion host.
  2. Get Materialize’s public keys for the SSH tunnel connection you just created:

    SELECT
        mz_connections.name,
        mz_ssh_tunnel_connections.*
    FROM
        mz_connections JOIN
        mz_ssh_tunnel_connections USING(id)
    WHERE
        mz_connections.name = 'ssh_connection';
    
  3. Log in to your SSH bastion host and add Materialize’s public keys to the authorized_keys file, for example:

    # Command for Linux
    echo "ssh-ed25519 AAAA...76RH materialize" >> ~/.ssh/authorized_keys
    echo "ssh-ed25519 AAAA...hLYV materialize" >> ~/.ssh/authorized_keys
    
  4. Back in the psql shell connected to Materialize, validate the SSH tunnel connection you created using the VALIDATE CONNECTION command:

    VALIDATE CONNECTION ssh_connection;
    

    If no validation error is returned, move to the next step.

  5. Use the CREATE SECRET command to securely store the password for the materialize PostgreSQL user you created earlier:

    CREATE SECRET pgpass AS '<PASSWORD>';
    
  6. Use the CREATE CONNECTION command to create another connection object, this time with database access and authentication details for Materialize to use:

    CREATE CONNECTION pg_connection TO POSTGRES (
      HOST '<host>',
      PORT 5432,
      USER 'materialize',
      PASSWORD SECRET pgpass,
      DATABASE '<database>',
      SSH TUNNEL ssh_connection
      );
    
    • Replace <host> with your RDS endpoint. To find your RDS endpoint, select your database in the RDS Console, and look under Connectivity & security.

    • Replace <database> with the name of the database containing the tables you want to replicate to Materialize.

  7. Use the CREATE SOURCE command to connect Materialize to your RDS instance and start ingesting data from the publication you created earlier:

    CREATE SOURCE mz_source
      IN CLUSTER ingest_postgres
      FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
      FOR ALL TABLES;
    

    To ingest data from specific schemas or tables in your publication, use FOR SCHEMAS (<schema1>,<schema2>) or FOR TABLES (<table1>, <table2>) instead of FOR ALL TABLES.

Step 6. Check the ingestion status

Before it starts consuming the replication stream, Materialize takes a snapshot of the relevant tables in your publication. Until this snapshot is complete, Materialize won’t have the same view of your data as your PostgreSQL database.

In this step, you’ll first verify that the source is running and then check the status of the snapshotting process.

  1. Back in the SQL client connected to Materialize, use the mz_source_statuses table to check the overall status of your source:

    WITH
      source_ids AS
      (SELECT id FROM mz_sources WHERE name = 'mz_source')
    SELECT *
    FROM
      mz_internal.mz_source_statuses
        JOIN
          (
            SELECT referenced_object_id
            FROM mz_internal.mz_object_dependencies
            WHERE
              object_id IN (SELECT id FROM source_ids)
            UNION SELECT id FROM source_ids
          )
          AS sources
        ON mz_source_statuses.id = sources.referenced_object_id;
    

    For each subsource, make sure the status is running. If you see stalled or failed, there’s likely a configuration issue for you to fix. Check the error field for details and fix the issue before moving on. Also, if the status of any subsource is starting for more than a few minutes, contact our team.

  2. Once the source is running, use the mz_source_statistics table to check the status of the initial snapshot:

    WITH
      source_ids AS
      (SELECT id FROM mz_sources WHERE name = 'mz_source')
    SELECT sources.referenced_object_id AS id, mz_sources.name, snapshot_committed
    FROM
      mz_internal.mz_source_statistics
        JOIN
          (
            SELECT object_id, referenced_object_id
            FROM mz_internal.mz_object_dependencies
            WHERE
              object_id IN (SELECT id FROM source_ids)
            UNION SELECT id, id FROM source_ids
          )
          AS sources
        ON mz_source_statistics.id = sources.referenced_object_id
        JOIN mz_sources ON mz_sources.id = sources.referenced_object_id;
    

    object_id | snapshot_committed
    ----------|------------------
     u144     | t
    (1 row)
    

    Once snapshot_commited is t, move on to the next step. Snapshotting can take between a few minutes to several hours, depending on the size of your dataset and the size of the cluster replica you chose for your ingest_postgres cluster.

Step 7. Right-size the cluster

After the snapshotting phase, Materialize starts ingesting change events from the PostgreSQL replication stream. For this work, Materialize generally performs well with an xsmall replica, so you can resize the cluster accordingly.

  1. Still in a SQL client connected to Materialize, use the ALTER CLUSTER command to downsize the cluster to xsmall:

    ALTER CLUSTER ingest_postgres SET (SIZE 'xsmall');
    

    Behind the scenes, this command adds a new xsmall replica and removes the small replica.

  2. Use the SHOW CLUSTER REPLICAS command to check the status of the new replica:

    SHOW CLUSTER REPLICAS WHERE cluster = 'ingest_postgres';
    

         cluster     | replica |  size  | ready
    -----------------+---------+--------+-------
     ingest_postgres | r1      | xsmall | t
    (1 row)
    
  3. Going forward, you can verify that your new replica size is sufficient as follows:

    1. In Materialize, get the replication slot name associated with your PostgreSQL source from the mz_internal.mz_postgres_sources table:

      SELECT
          d.name AS database_name,
          n.name AS schema_name,
          s.name AS source_name,
          pgs.replication_slot
      FROM
          mz_sources AS s
          JOIN mz_internal.mz_postgres_sources AS pgs ON s.id = pgs.id
          JOIN mz_schemas AS n ON n.id = s.schema_id
          JOIN mz_databases AS d ON d.id = n.database_id;
      
    2. In PostgreSQL, check the replication slot lag, using the replication slot name from the previous step:

      SELECT
          pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn)
          AS replication_lag_bytes
      FROM pg_replication_slots
      WHERE slot_name = '<slot_name>';
      

      The result of this query is the amount of data your PostgreSQL cluster must retain in its replication log because of this replication slot. Typically, this means Materialize has not yet communicated back to PostgreSQL that it has committed this data. A high value can indicate that the source has fallen behind and that you might need to scale up your ingestion cluster.

Next steps

With Materialize ingesting your PostgreSQL data into durable storage, you can start exploring the data, computing real-time results that stay up-to-date as new data arrives, and serving results efficiently.

Back to top ↑