geonetwork / geonetwork/core-geonetwork
Migration / Cleaning up database tables and columns / PostGres DB / Version 3.12.6 to 4.2.2
- Dominant language
- Java
- Stars
- 521
- Forks
- 514
- Avg merge
- 6d 13h
- Merged PRs (30d)
- 19
Description
**Describe the bug**
Cleanup of database tables and columns no longer needed after a database migration from GN 3.12.6 to GN 4.2.2 (Postgres). Obsolete tables and columns are not deleted during migration.
The following tables are still present after the database migration, but not in a bare new GN 4.2.2 instance. These tables should be cleaned up during the migration:
- services & serviceparameters: Contains data on the virtual CSW, which is not supported with GN 4.2.2, so these tables are no longer required.
- cswservercapabilitiesinfo
- guf_citation
Additionally, the guf_userfeedbacks table contains columns and one constraint that is no longer required. They might be deleted during migration.
**To Reproduce**
Steps to reproduce the behavior:
1. Start GeoNetwork migration from version 3.12.6 to version 4.2.2
2. Set up a bare new GN 4.2.2 instance
3. Compare the mentioned database tables between the migrated database and the database of the new GN 4.2.2 instance
**Expected behavior**
Migration takes cleaning up database tables and columns into account. The migrated database does not contain obsolete tables and columns.
**Desktop (please complete the following information):**
- GeoNetwork Version 4.2.2
- Server Application Tomcat 8
**Additional context**
Partial solution proposal:
```
-- Postgres SQL statement to clean up the guf_userfeedbacks table
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP CONSTRAINT IF EXISTS fk_e9nt5jgp7gi12nvavaacs7bbr;
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP COLUMN IF EXISTS comment;
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP COLUMN IF EXISTS date;
ALTER TABLE IF EXISTS geonetwork.guf_userfeedbacks DROP COLUMN IF EXISTS citation_id;
```
Contributor guide
Research direction
Start by inspecting the migration path from GeoNetwork 3.12.6 to 4.2.2 and comparing it with the schema of a bare 4.2.2 instance. Verify how migrations define database changes, then ensure the obsolete services, serviceparameters, cswservercapabilitiesinfo, guf_citation, and guf_userfeedbacks columns and constraint are removed. Done when the migrated schema matches the fresh schema for these objects.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100