Ingest data from Self-hosted PostgreSQL
This page shows you how to stream data from a self-hosted PostgreSQL database to Materialize using the PostgreSQL source.
Before you begin
-
Make sure you are running PostgreSQL 11 or higher.
-
Make sure
psql
is installed on your workstation.
You’ll use psql
to interact with your PostgreSQL database and 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.
-
As a superuser, use
psql
to connect to your PostgreSQL database. -
Check if logical replication is enabled:
SHOW wal_level;
The
wal_level
setting must be set tological
. If it’s set to any other value, change it tological
in the database configuration file (postgresql.conf
). -
Restart the database in order for the new
wal_level
to take effect. Keep in mind that restarting can affect database performance. -
Back in your
psql
shell, verify that replication is now enabled:SHOW wal_level;
Step 2. Create a publication
Once logical replication is enabled, the next step is to 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.
-
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 ofUPDATE
andDELETE
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. -
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.
-
Create a user for Materialize, if you don’t already have one:
CREATE USER materialize PASSWORD '<password>';
-
Grant the user permission to manage replication:
ALTER ROLE materialize WITH REPLICATION;
-
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 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.
-
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;
-
Update your database firewall rules to allow traffic from each IP address from the previous step.
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.
-
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.
-
Configure the SSH bastion host to allow traffic only from Materialize.
-
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;
-
Update your SSH bastion host’s firewall rules to allow traffic from each IP address from the previous step.
-
-
Update your database firewall rules to allow traffic from the SSH bastion host.
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.
-
Create a target group
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.
-
Create a Network Load Balancer (NLB)
Create a Network Load Balancer that is enabled for the same subnets that the PostgreSQL instance is in.
-
Create TCP listener
Create a TCP listener for your PostgreSQL instance that forwards to the corresponding target group you created.
-
Verify security groups and health checks
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.
-
Create a VPC endpoint service
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.
-
Create an AWS PrivateLink Connection
In Materialize, create a
AWS PRIVATELINK
connection that references the endpoint service that you created in the previous step.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.
-
Configure the AWS PrivateLink service
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
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.
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 cluster containing 1 medium
replica for ingesting source data from your PostgreSQL database.
-
In the
psql
shell connected to Materialize, use theCREATE CLUSTER
command to create the new cluster:CREATE CLUSTER ingest_postgres SIZE = 'medium';
We recommend starting with a
medium
size replica or larger. This will help Materialize more quickly process the initial snapshot of the tables in your publication. Once the snapshot is finished, you’ll right-size the cluster.
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.
-
In the
psql
shell connected to Materialize, use theCREATE SECRET
command to securely store the password for thematerialize
PostgreSQL user you created earlier:CREATE SECRET pgpass AS '<PASSWORD>';
-
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 database endpoint. -
Replace
<database>
with the name of the database containing the tables you want to replicate to Materialize.
-
-
Use the
CREATE SOURCE
command to connect Materialize to your database 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>)
orFOR TABLES (<table1>, <table2>)
instead ofFOR ALL TABLES
.
-
In the
psql
shell connected to Materialize, use theCREATE 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.
- Replace
-
Get Materialize’s public keys for the SSH tunnel connection:
SELECT * FROM mz_ssh_tunnel_connections;
-
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
-
Back in the
psql
shell connected to Materialize, validate the SSH tunnel connection you created using theVALIDATE CONNECTION
command:VALIDATE CONNECTION ssh_connection;
If no validation error is returned, move to the next step.
-
Use the
CREATE SECRET
command to securely store the password for thematerialize
PostgreSQL user you created earlier:CREATE SECRET pgpass AS '<PASSWORD>';
-
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 database endpoint. -
Replace
<database>
with the name of the database containing the tables you want to replicate to Materialize.
-
-
Use the
CREATE SOURCE
command to connect Materialize to your Azure 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>)
orFOR TABLES (<table1>, <table2>)
instead ofFOR ALL TABLES
. -
After source creation, you can handle upstream schema changes for specific replicated tables using the
ALTER SOURCE...{ADD | DROP} SUBSOURCE
syntax.
-
Back in the
psql
shell connected to Materialize, use theCREATE SECRET
command to securely store the password for thematerialize
PostgreSQL user you created earlier:CREATE SECRET pgpass AS '<PASSWORD>';
-
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 postgres, PASSWORD SECRET pgpass, DATABASE <database>, AWS PRIVATELINK privatelink_svc );
-
Replace
<host>
with your database endpoint. -
Replace
<database>
with the name of the database containing the tables you want to replicate to Materialize.
-
-
Use the
CREATE SOURCE
command to connect Materialize to your database 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>)
orFOR TABLES (<table1>, <table2>)
instead ofFOR 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.
-
Back in the
psql
shell connected to Materialize, use themz_source_statuses
table to check the overall status of your source:WITH source_id AS (SELECT id FROM mz_objects 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 = (SELECT id FROM source_id) UNION SELECT id FROM source_id ) AS sources ON mz_source_statuses.id = sources.referenced_object_id;
For each
subsource
, make sure thestatus
isrunning
. If you seestalled
orfailed
, there’s likely a configuration issue for you to fix. Check theerror
field for details and fix the issue before moving on. Also, if thestatus
of any subsource isstarting
for more than a few minutes, contact our team. -
Once the source is running, use the
mz_source_statistics
table to check the status of the initial snapshot:WITH source_id AS (SELECT id FROM mz_objects WHERE name = 'mz_source') SELECT bool_and(snapshot_committed) AS snapshot_committed FROM mz_internal.mz_source_statistics JOIN ( SELECT referenced_object_id FROM mz_internal.mz_object_dependencies WHERE object_id = (SELECT id FROM source_id) UNION SELECT id FROM source_id ) AS sources ON mz_source_statistics.id = sources.referenced_object_id;
snapshot_committed -------------------- t (1 row)
Once
snapshot_commited
ist
, 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 youringest_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.
-
Still in the
psql
shell connected to Materialize, use theALTER CLUSTER
command to downsize the cluster toxsmall
:ALTER CLUSTER ingest_postgres SET (SIZE 'xsmall');
Behind the scenes, this command adds a new
xsmall
replica and removes themedium
replica. -
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)
-
Going forward, you can verify that your new replica size is sufficient as follows:
-
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;
-
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.
- Explore your data with
SHOW SOURCES
andSELECT
. - Compute real-time results in memory with
CREATE VIEW
andCREATE INDEX
or in durable storage withCREATE MATERIALIZED VIEW
. - Serve results to a PostgreSQL-compatible SQL client or driver with
SELECT
orSUBSCRIBE
or to an external message broker withCREATE SINK
. - Check out the tools and integrations supported by Materialize.