Red Hat Bugzilla – Bug 893459
Installer pre-upgrade step should ensure DB consistency using fkvalidator.sh
Last modified: 2015-09-22 09:09 EDT
Description of problem:
When upgrading real customer databases we ha encounter sometimes that DB had inconsistent data, mostly by data added manually in a way that breaks a Foreign Key constraint.
Installer pre upgrade step should run fkvalidaor.sh to chekc DB consistency and if any such are found, the user should approve running fkvalidaor.sh with the -f flag that forces removal of the inconsistent data.
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1.take any parent/child tables (for example job & step)
2.force insertion of illegal data to the child table by disabling child table triggers
ALTER TABLE <child table> DISABLE TRIGGER ALL;
3.Insert a row in child that breaks the FK
4. ALTER TABLE <child table> ENABLE TRIGGER ALL;
5. use pg_dump to backup the database
6. use pg_restore to restore the database
You will get a FK violation error
FK violations should be trapped and resolved in the pre-upgrade step
can the fkvalidator.sh list of inconsistencies ?
Please add the --help of the tool to this BZ.
(In reply to comment #1)
> can the fkvalidator.sh list of inconsistencies ?
> Please add the --help of the tool to this BZ.
Usage: fkvalidator.sh [-h] [-s SERVERNAME [-p PORT]] [-d DATABASE] [-u USERNAME] [-l LOGFILE] [-f] [-v]
-s SERVERNAME - The database servername for the database (def. localhost)
-p PORT - The database port for the database (def. 5432)
-d DATABASE - The database name (def. engine)
-u USERNAME - The admin username for the database.
-l LOGFILE - The logfile for capturing output (def. fkvalidator.sh.log)
-f - Fix the non consistent data by removing it from DB.
-v - Turn on verbosity (WARNING: lots of output)
-h - This help text.
1) listing violations
fkvalidator.sh -u postgres -d engine
2) resolving violations
fkvalidator.sh -u postgres -d engine -f
Please do with this the same as with taskcleaner.sh and put it in the different folder.
We had found a problem in fkvalidator when the child table has a value that not exists in the parent table but this value is null.
Moving BZ to POST in order to fix that as suggested by Moran
3.2 has been released