Обсуждение: DB Import Error...

"Senthil Kumar G" <senthil@zoniac.com> wrote:

> I am upgrading my staging environment from postgresql 8.2.3 to
> 9.0.3.
>
> I was able to do successfully.

> [errors while applying dump to new database]

> What could be the reason? What should i do to resolve this?

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.

-Kevin

Hi Kevin:

Thanks for your quick response.
I will try out this and come back if issue was not resolved still.

Thanks
Senthil

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Friday, April 01, 2011 8:49 PM
To: pgsql-admin@postgresql.org; Senthil Kumar G
Subject: Re: [ADMIN] DB Import Error...

"Senthil Kumar G" <senthil@zoniac.com> wrote:

> I am upgrading my staging environment from postgresql 8.2.3 to
> 9.0.3.
>
> I was able to do successfully.

> [errors while applying dump to new database]

> What could be the reason? What should i do to resolve this?

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.

-Kevin

> 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?

"Gnanakumar" <gnanam@zoniac.com> wrote:

>> 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.

> 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.

> 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?

If you're upgrading to 9.0, I would start by following the
recommended upgrade procedure and seeing whether these problems then
exist in the new database.  Fixing old issues like this is one big
reason for using the newer version of pg_dump.

-Kevin

> If you're upgrading to 9.0, I would start by following the
> recommended upgrade procedure and seeing whether these problems then
> exist in the new database.  Fixing old issues like this is one big
> reason for using the newer version of pg_dump.

How do I use the newer version of pg_dump (9.0.3 in this case) to take SQL
dump, when v8.2.3 is already installed in my server?  BTW, originally we've
installed v8.2.3 from the source (./configure, gmake, gmake install).  Even
if I try to install the new version 9.0.3 from source, will that not
overwrite existing directories, files, binaries, etc.?

Our current v8.2.3 setup:
Installation directory: /usr/local/pgsql
Port: 5432

Any procedure/steps/advice/suggestion to take dump using latest version of
pg_dump without disturbing old database cluster is appreciated.

NOTE: It's not one single database server, we've multiple onsite customer
servers that has to be upgraded, hence I would like to address this issue in
general.

"Gnanakumar" <gnanam@zoniac.com> wrote:

>> If you're upgrading to 9.0, I would start by following the
>> recommended upgrade procedure and seeing whether these problems
>> then exist in the new database.  Fixing old issues like this is
>> one big reason for using the newer version of pg_dump.
>
> How do I use the newer version of pg_dump (9.0.3 in this case) to
> take SQL dump, when v8.2.3 is already installed in my server?

You can install two different versions of PostgreSQL in different
directories, or you can run pg_dump from the machine which is your
conversion target.  We do both.  We always build from source, and we
use a --prefix on configure which includes the version.  For
example:

  --prefix=/usr/local/pgsql-9.0.3

To simplify usage we also create a symbolic link from
/usr/local/pgsql to the version we want as the default on the
machine, and we set our PATH to include /usr/local/pgsql/bin.  We
never use the symbolic link in our service scripts, though.  Anyway,
if you build with a different prefix you can pick your version by
explicitly specifying the full path when you run a client program
like pg_dump.

> Any procedure/steps/advice/suggestion to take dump using latest
> version of pg_dump without disturbing old database cluster is
> appreciated.

When we're moving from an old version on one machine to a newer
version on another machine, we don't need to install the new version
of PostgreSQL on the old machine.  On the new machine run:

pg_dump -h oldmachine -U username remotedbname | psql localdbname

-Kevin

> You can install two different versions of PostgreSQL in different
> directories, or you can run pg_dump from the machine which is your
> conversion target.  We do both.  We always build from source, and we
> use a --prefix on configure which includes the version.  For
> example:

I even tried out the recommended upgrade technique also. That is, installed
new version (9.0.3 in this case) of PostgreSQL in a different directory in
the same machine and used pg_dump of this latest version to access the old
database (running 8.2.3 in this case) to take a dump and then imported into
9.0.3. Still I'm facing *exactly* the same set of errors after importing (as
I was getting before).

ERROR:  constraint "xxxx" for relation "xxxx" already exist
There are 260 occurrences of this type of error.

How do I resolve/fix this problem at this stage?  Since this issue we're
facing is with our Production database dump, timely suggestions/advice/help
to resolve this are highly appreciated.

> when i said "search for the  problematic constraint" i meant to open the
file in a text editor and search for the problematic constraint.

Yes,  I've even opened the dump file and analyzed those constraints.  In
fact, I've already explained in detail about my observations here:
http://archives.postgresql.org/pgsql-admin/2011-04/msg00033.php

As I said in the above link, though many FK constraints were dropped very
long (years) back in our application, but its associated "constraint
triggers" were not dropped properly by PostgreSQL from "pg_trigger" table in
v8.2.3.  After understanding about PostgreSQL system catalog table
(particularly pg_class, pg_trigger, pg_constraint), I wrote a function on my
own to delete the FK constraints that are still referred in pg_trigger
table.

After executing the below function in v8.2.3, I took pg_dump in v8.2.3 and
then imported the SQL dump into v9.0.3.  Now, I don't see any errors while
importing in v9.0.3.

NOTE: Previously, when there was errors while importing in v9.0.3, I could
see all those *dropped* FK constraints were again automatically *recreated*
in v9.0.3.  I confirmed this from 2 places: psql (using \d) and pgAdmin II
tool.  But after executing the below function in v8.0.3 before taking
pg_dump and then import in v9.0.3, I don't see any errors now and also at
the same time, I don't see those dropped FK constraints
reappearing/recreated here.

Any advice/suggestion/remarks/alternatives to my approach are highly
appreciated.

FUNCTION:
CREATE OR REPLACE FUNCTION delete_dropped_fk_constraints_from_pg_trigger()
RETURNS numeric AS'
DECLARE
    v_tgconstrname        VARCHAR;
    v_table1        VARCHAR;
    v_table2        VARCHAR;

    v_child_trigger_count    NUMERIC := 0;
    v_parent_trigger_count    NUMERIC := 0;
C1 CURSOR IS
    SELECT
        DISTINCT tgconstrname,
        (select relname from pg_class where oid = tgrelid) as
table1,
        (select relname from pg_class where oid = tgconstrrelid) as
table2
    FROM
        pg_trigger
    WHERE
        tgisconstraint = true
        AND tgconstrname IS NOT NULL
        AND LENGTH(tgconstrname) > 0
        AND tgconstrname != ''<unnamed>''
        AND tgconstrname NOT IN
            (SELECT conname FROM pg_constraint
                WHERE contype = ''f'')
    ORDER BY 1;
BEGIN
    OPEN C1;
    LOOP
    FETCH C1 INTO v_tgconstrname, v_table1, v_table2;

        IF NOT FOUND THEN
            EXIT;
        END IF;

        RAISE NOTICE ''FK Constraint Name=%, Table1=%, Table2=%'',
v_tgconstrname, v_table1, v_table2;

        SELECT COUNT(*) INTO v_child_trigger_count FROM pg_trigger
        WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname =
LOWER(v_table1)) AND
                tgconstrname = LOWER(v_constr_name);

        SELECT COUNT(*) INTO v_parent_trigger_count FROM pg_trigger
        WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname =
LOWER(v_table2)) AND
                tgconstrname = LOWER(v_constr_name);

        DELETE FROM pg_trigger WHERE LOWER(tgconstrname) =
LOWER(v_constr_name) AND tgrelid IN
        (SELECT oid FROM pg_class WHERE relname IN
(LOWER(v_table1),LOWER(v_table2)));

        UPDATE pg_class SET reltriggers = reltriggers -
v_child_trigger_count WHERE relname = LOWER(v_table1);

        IF (v_table1 <> v_table2) THEN -- This condition is checked
for self-referencing FK constraints
            UPDATE pg_class SET reltriggers = reltriggers -
v_parent_trigger_count WHERE relname = LOWER(v_table2);
        END IF;

    END LOOP;
    CLOSE C1;

    RETURN 1;
END;'
LANGUAGE 'plpgsql';

SELECT delete_dropped_fk_constraints_from_pg_trigger();