Fixing "must be superuser to create FOR ALL TABLES publication" error from Debezium with PostgreSQL on Aiven

README.md

Issue

You're trying to use Kafka Connect, using the Debezium to Postgres, and your Postgres database is running on Aiven. You have pgoutput selected as the plugin name (this is the best choice).

You're getting this error: Caused by: org.postgresql.util.PSQLException: ERROR: must be superuser to create FOR ALL TABLES publication.

Why This is Happening

Aiven doesn't allow you to have a superuser. Debezium tries to create a publication, and fails, because it's not using a superuser.

How To Fix It

We have to create the publication before configuring the connector. Publications are database specific, so you have to do this when you're connected to the database you're going to use for CDC.

There is a package called aiven-extras that allows you to create a publication.

1. Connect to the database you'll use.

\connect foodb;

2. Install aiven-extras

CREATE EXTENSION aiven_extras CASCADE;

3. Create a publication for all tables

SELECT *
FROM aiven_extras.pg_create_publication_for_all_tables('dbz_publication', 'INSERT,UPDATE,DELETE');

It must be called dbz_publication. That is what Debezium is expecting.

Now, you can run your connector and it should work (or you might have other, likely fixable issues).

It seems the name of that publication has changed

SELECT * FROM aiven_extras.pg_create_publication_for_all_tables('debezium_publication', 'INSERT,UPDATE,DELETE')'
was required to get this working for me...

Could be; I haven't touched debezium + Aiven in a couple years now. Thanks for the update!

No, the default value is still dbz_publication if you don't change it. Aiven official documentation mentions this fix, including the need to check for the right publication name.