Ingest data from Neon CDC
Neon is a Serverless Postgres designed for the cloud that separates compute and storage to offer modern developer features such as autoscaling, branching, bottomless storage, and others.
Follow these steps to ingest CDC data from Neon into RisingWave:
1. Sign up for a Neon Cloud account
Start by signing up for a free Neon Cloud account, which will grant you access to PostgreSQL services. To create your account, visit Neon Cloud Account.
2. Create your first Neon project
Navigate to the Neon Console and select "Create a project." Assign a name and region to your first Neon PostgreSQL project. You will be presented with connection details for your Neon project, so be sure to save them for later use when connecting to your PostgreSQL server.
3. Connect to the Neon PostgreSQL server
You can connect to Neon through the SQL Editor in Neon, psql, or from other clients or applications.
For more information about Neon, please refer to the official documentation of Neon.
4. Configure Neon for CDC
-
Ensure that
wal_level
islogical
. Check by using the following statement.SHOW wal_level;
By default, it is
replica
. For CDC, you will need to set it to logical through the SQL Editor in Neon, psql, or from other clients. The following command will change thewal_level
.ALTER SYSTEM SET wal_level = logical;
-
Assign
REPLICATION
,LOGIN
,andCREATEDB
role attributes to the user.For an existing user, run the following statement to assign the attributes:
ALTER USER <username> REPLICATION LOGIN CREATEDB;
For a new user, run the following statement to create the user and assign the attributes:
CREATE USER <username> REPLICATION LOGIN CREATEDB;
You can check your role attributes by using the
\du
psql command:dev-# \du
List of roles
Role name | Attributes | Member of
-----------+-----------------------------------------------------------+---------
rw | Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} -
Grant required privileges to the user.
Run the following statements to grant the required privileges to the user.
GRANT CONNECT ON DATABASE <database_name> TO <username>;
GRANT USAGE ON SCHEMA <schema_name> TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT CREATE ON DATABASE <database_name> TO <username>;You can use the following statement to check the privileges of the user to the tables:
postgres=# SELECT table_name, grantee, privilege_type
FROM information_schema.role_table_grants
WHERE grantee='<username>';An example result based on three tables that we created in Neon:
table_name | grantee | privilege_type
-----------+---------+----------------
customer | rw | SELECT
orders | rw | SELECT
supplier | rw | SELECT
(3 rows)
5. Ingest CDC data from Neon into RisingWave
The following example creates a table in RisingWave that reads CDC data from the customer
table in Neon. The customer
table is in the public
schema within the dev
database. When connecting to a specific table in Neon, use the CREATE TABLE
command.
CREATE TABLE customer (
customer_id INTEGER,
customer_name VARCHAR,
email VARCHAR,
phone_number VARCHAR
PRIMARY KEY (customer_id)
) WITH (
connector = 'postgres-cdc',
hostname = '127.0.0.1',
port = '5432',
username = 'postgres',
password = 'postgres',
database.name = 'dev',
schema.name = 'public',
table.name = 'customer'
);
Similarly, you can create another table reading CDC data in RisingWave based on the orders
table in Neon with a public
schema within the dev
database.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_timestamp TIMESTAMP,
total_amount NUMERIC,
status VARCHAR
) WITH (
connector = 'postgres-cdc',
hostname = '127.0.0.1',
port = '5432',
username = 'postgres',
password = 'postgres',
database.name = 'dev',
schema.name = 'public',
table.name = 'order'
);
After the table is created, you can view and transform the CDC data from Neon based on your needs.