Sink data from RisingWave to PostgreSQL
This guide will show you how to sink data from RisingWave to PostgreSQL using the JDBC connector. The sink parameters are similar to those for other JDBC-available databases, such as MySQL. However, we will cover the configurations specific to PostgreSQL and how to verify that data is successfully sunk.
You can test out this process on your own device by using the postgres-sink
demo in the integration_test directory
of the RisingWave repository.
Set up a PostgreSQL database
- AWS RDS
- Self-hosted
Set up a PostgreSQL RDS instance on AWS
Here we will use a standard class instance without Multi-AZ deployment as an example.
-
Log in to the AWS console. Search “RDS” in services and select the RDS panel.
-
Create a database with PostgreSQL as the Engine type. We recommend setting up a username and password or using other security options.
-
When the new instance becomes available, click on its panel.
-
From the Connectivity panel, we can find the endpoint and connection port information.
Connect to the RDS instance from Postgres
Now we can connect to the RDS instance. Make sure you have installed psql on your local machine, and start a psql prompt. Fill in the endpoint, the port, and login credentials in the connection parameters.
psql --host = pg-to-rw.xxxxxx.us-east-1.rds.amazonaws.com --port=5432 --username=awsuser --password
For more login options, refer to the RDS connection guide.
Launch and set up PostgreSQL
To install PostgreSQL locally, see their download options.
If you are using the demo version, connect to PostgreSQL with the following command. Ensure that all other programs are disconnected from port 5432.
psql postgresql://myuser:123456@127.0.0.1:5432/mydb
Ensure that the Postgres user is granted the following privileges on the used table with the following SQL query.
GRANT SELECT, INSERT, UPDATE, DELETE ON [table_name] TO [username];
Create a table in PostgreSQL
Use the following query to set up a table in PostgreSQL. We will sink to this table from RisingWave.
CREATE TABLE target_count (
target_id VARCHAR(128) PRIMARY KEY,
target_count BIGINT
);
Set up RisingWave
Install and launch RisingWave
To install and start RisingWave locally, see the Get started guide. We recommend running RisingWave locally for testing purposes.
Notes about running RisingWave from binaries
If you are running RisingWave locally from binaries and intend to use the native CDC source connectors or the JDBC sink connector, make sure you have JDK 11 or later versions installed in your environment.
Create a sink
Syntax
CREATE SINK [ IF NOT EXISTS ] sink_name
[FROM sink_from | AS select_query]
WITH (
connector='jdbc',
field_name = 'field', ...
);