Flyway Migrations Do Not Work With PostgreSQL Replication #12880
Description
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.