> The recommended upgrade technique is to us pg_dump (or pg_dumpall)
> from the newer version (9.0.3 in this case) to access the old
> database (running the 8.2.3 server in this case). If you followed
> some other procedure, like using the 8.2.3 software to dump the
> database, you should try the recommended approach instead.
> I generally dump in plain text format (the default) and pipe it to
> psql.
Before trying out to use pg_dump from the newer version (9.0.3 in this
case), I analyzed (.sql dump file) to my knowledge on what's going wrong.
Reason for this analysis is that the same .sql dump file is importing into
8.2.3 successfully without any errors, but why it should error when the same
.sql dump is imported into 9.0.3. There are 260 occurrences of this type of
error during import (ERROR: constraint "xxxx" for relation "xxxx" already
exist). I then located the statement in .sql dump file which was throwing
this error. It points out here:
CREATE CONSTRAINT TRIGGER ifb_ci_ciid_ciid_fk
AFTER UPDATE ON consultantinterview
FROM interviewfeedback
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('ifb_ci_ciid_ciid_fk',
'interviewfeedback', 'consultantinterview', 'FULL', 'consultantinterviewid',
'consultantinterviewid');
After seeing this statement, I was shocked and surprised that the constraint
"ifb_ci_ciid_ciid_fk" was dropped very long (years) back in our application
and it's still being referred internally somewhere by PostgreSQL. I then
tried to query from "pg_trigger" table: "select * from pg_trigger where
tgconstrname = 'ifb_ci_ciid_ciid_fk' " and I'm seeing 9 records with
"tgname" starting with " RI_ConstraintTrigger_xxxx". Also, on describing
table using \d, I don't see this constraint listed in "Foreign-key
constraints" section, but I could see this constraint listed in "Triggers"
section starting with RI_ConstraintTrigger_xxxx.
What I could infer from this based on my knowledge is that the FK
constraints are dropped from the table but its associated constraint
triggers are not dropped properly.
How do I resolve/clean-up our database at this stage?