Flyway Migrations Do Not Work With PostgreSQL Replication

[AMcManigal]

Affected module It impacts installation of OpenMetadata in K8s.

Describe the bug A clear and concise description of what the bug is.

When installing OpenMetadata using a PostgreSQL DB that has replication enabled the flyway scripts will fail with many errors like this.

Message : ERROR: cannot update table "tag" because it does not have a replica identity and publishes updates
--
Tue, Aug 15 2023 12:32:27 pm | Hint: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
Tue, Aug 15 2023 12:32:27 pm | Location : /opt/openmetadata/bootstrap/sql/org.postgresql.Driver/v006__create_db_connection_info.sql (/opt/openmetadata/bootstrap/sql/org.postgresql.Driver/v006__create_db_connection_info.sql)
Tue, Aug 15 2023 12:32:27 pm | Line : 55
Tue, Aug 15 2023 12:32:27 pm | Statement : UPDATE tag
Tue, Aug 15 2023 12:32:27 pm | SET json = JSONB_SET(json::jsonb, '{provider}', '"system"', true)
Tue, Aug 15 2023 12:32:27 pm | WHERE fullyQualifiedName in ('PersonalData.Personal', 'PersonalData.SpecialCategory',
Tue, Aug 15 2023 12:32:27 pm | 'PII.None', 'PII.NonSensitive', 'PII.Sensitive',
Tue, Aug 15 2023 12:32:27 pm | 'Tier.Tier1', 'Tier.Tier2', 'Tier.Tier3', 'Tier.Tier4', 'Tier.Tier5')

This can be fixed as follows.

ALTER TABLE tag REPLICA IDENTITY USING INDEX tag_fullyqualifiedname_key;

To Reproduce

Simply try to do a fresh install on a PostgreSQL instance that has replication enabled. In this case the DB was an AWS RDS instance with a read replica.

Screenshots or steps to reproduce

Expected behavior A clear and concise description of what you expected to happen.

Version:

• OS: [e.g. iOS]
• Python version:
• OpenMetadata version: 1.1.1
• OpenMetadata Ingestion package version: 1.1.1.1

Additional context Add any other context about the problem here.

[harshach]

@AMcManigal which version of Postgres are you running?

[AMcManigal]

@AMcManigal which version of Postgres are you running?

Ah, sorry, I should have added that. PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit