Materialize + Postgres RDS + Terraform

Terraform module for deploying a new RDS Postgres instance and connecting it to Materialize.

For the manual setup, see the Materialize + RDS documentation.

Warning This is provided on a best-effort basis and Materialize cannot offer support for this module.

Overview

This module will create the following resources:

• VPC with public and private subnets
• Security group for RDS Postgres instance: allows inbound traffic from Materialize and the user's IP address
• RDS Postgres instance
• RDS Parameter Group for RDS Postgres instance to enable logical replication

To override the default AWS provider variables, you can export the following environment variables:

export AWS_PROFILE=<your_aws_profile> # eg. default
export AWS_CONFIG_FILE=<your_aws_config_file> # eg. ["~/.aws/config"]

Prerequisites

Before using this module, you must have the following:

• An AWS account

• Materialize instance

• Get your Materialize instance egress IP addresses from the mz_egress_ips table:

  Access the Materialize instance and run the following query:

  SELECT jsonb_agg(egress_ip || '/32') egress_cidrs FROM mz_egress_ips;

  The query above will return a JSON array of egress IP addresses. Define the following variable in your terraform.tfvars file:

  mz_egress_ips = ["123.456.789.0/32", "123.456.789.1/32"]

Running the module

Variables

Copy the terraform.tfvars.example file to terraform.tfvars and fill in the variables:

cp terraform.tfvars.example terraform.tfvars

Name	Description	Type	Example	Required
mz_egress_ips	Materialize instance egress IP addresses	list(string)	["123.456.789.0/32", "123.456.789.1/32"]	yes
rds_instance_name	The name of the RDS instance	string	mz-rds-demo-db	yes
publicly_accessible	Whether the RDS is publicly accessible	bool	true	no
rds_instance_class	The RDS instance class	string	db.m5.large	no
engine_version	The RDS engine version	string	14	no

Apply the Terraform Module

1. Add the Materialize instance egress IP addresses to the mz_egress_ips variable in terraform.tfvars:

   mz_egress_ips = ["123.456.789.0/32", "123.456.789.1/32"]

2. Apply the Terraform configuration:

   terraform apply

   Once you run the command, it might take a few minutes for the RDS instance to be created.

3. Check the output:

   terraform output -raw mz_rds_details

Output

    -- On the RDS instance side:
    -- 1. Connect to the RDS instance
    PGPASSWORD=YOUR_SECURE_PASSWORD psql -h mz-rds-demo-db.some-db-url.us-east-1.rds.amazonaws.com -U materialize -d materialize
    -- 2. Create a new table
    CREATE TABLE test_table (id int, name varchar(255));
    -- 3. Insert some data
    INSERT INTO test_table VALUES (1, 'test'), (2, 'test2');
    -- 4. Verify the data
    SELECT * FROM test_table;
    -- 5. Set the replica identity to full
    ALTER TABLE test_table REPLICA IDENTITY FULL;
    -- 6. Create a publication
    CREATE PUBLICATION mz_source FOR TABLE test_table;

    -- On the Materialize side:
    -- 1. Create a secret for the RDS password
    CREATE SECRET mz_rds_password AS 'YOUR_SECURE_PASSWORD';
    -- 2. Create a connection to the RDS instance
    CREATE CONNECTION pg_connection TO POSTGRES (
        HOST 'mz-rds-demo-db.some-db-url.us-east-1.rds.amazonaws.com',
        PORT 5432,
        USER 'materialize',
        PASSWORD SECRET mz_rds_password,
        SSL MODE 'require',
        DATABASE 'materialize'
    );

    -- 3. Create a source
    CREATE SOURCE mz_source
        FROM POSTGRES CONNECTION pg_connection (PUBLICATION 'mz_source')
        FOR ALL TABLES;

    -- 4. Query the source
    SELECT * FROM test_table;

Security

The RDS instance is publicly accessible, but the module creates a security group that allows inbound traffic from the Materialize egress IPs and the user's IP address on port 5432.

If you create a Materialize source from the RDS instance and leave the RDS instance idle for a long time, the replication slot might grow in size. If left unchecked a replication slot in an inactive state can consume all your disk space. To avoid this, add alarms on the RDS metric TransactionLogsDiskUsage and OldestReplicationSlotLag to alert you when the transaction logs disk usage increased above a threshold or when a replication slot started lagging.

Helpful links

• Materialize
• Postgres Connection
• Postgres CDC
• Materialize Terraform Provider