A foreign data wrapper in PostgreSQL allows you to directly virtualize data stored in an external database as a local external table, also known as a foreign table. This tutorial will demonstrate how to interact between PostgreSQL and RisingWave. In this example, RisingWave will use CDC (Change Data Capture) to extract data from PostgreSQL, and analyze it using a materialized view. Then PostgreSQL will directly retrieve the computation results stored in RisingWave.
psql
connections. One connection links to PostgreSQL, providing the source data for analysis and obtaining the analysis results from RisingWave. In our demo, the command to connect to PostgreSQL is psql -h localhost -p 5432 -d myd -U postgresuser
, with the password being postgrespw
. The other connection links to RisingWave to establish a job for analyzing the data. In our demo, the command to connect to RisingWave is psql -h localhost -p 4566 -d dev -U root
, with no password required. You can choose to exit from psql
and log in to the other database when you need to operate in another database. Alternatively, you can use tmux
to open two terminals simultaneously, connecting to the respective databases with psql
.
postgres_fdw
extension.postgresuser
in this demo) and in RisingWave (root
in this demo) have the necessary permissions to create tables and materialized views.postgres_fdw
. You can check the PostgreSQL’s doc here for more details.
Aspect | Sinking to PostgreSQL | Using PostgreSQL FDW to access data |
---|---|---|
Data Access | Data is physically stored in PostgreSQL | Data is physically stored in RisingWave |
Performance | Potential latency for RisingWave to write to PostgreSQL | Potential latency when reading data from RisingWave |
Message Delivery Guarantee | At-least-once while sinking into PostgreSQL tables | Exactly-once for MVs and the data is not moved |
Extra Requirement | None | Requires the postgres_fdw extension and involves more setup steps |