Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gXRy5-0006Wr-Pk for pgsql-admin@arkaria.postgresql.org; Thu, 13 Dec 2018 14:28:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gXRy2-00085V-5I for pgsql-admin@arkaria.postgresql.org; Thu, 13 Dec 2018 14:27:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gXRy1-00085O-QA for pgsql-admin@lists.postgresql.org; Thu, 13 Dec 2018 14:27:57 +0000 Received: from mail-ot1-x335.google.com ([2607:f8b0:4864:20::335]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gXRxy-0000FP-47 for pgsql-admin@lists.postgresql.org; Thu, 13 Dec 2018 14:27:56 +0000 Received: by mail-ot1-x335.google.com with SMTP id k98so2068237otk.3 for ; Thu, 13 Dec 2018 06:27:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=MFbgsTj1gIQ/NCAEcazM5WhKWOay3u7n9MW5o94BS+U=; b=Gus1Uavmc7D1q0LxuSiw9FhKz5EQw9MTioWGacLnKIcEZ2qWoD4UeeFMWJLgR0ubuo wzT6c7OntSCAx6ryjPsqWxGM3rAL31BPw1PBIK9GQTUi4Ijql3pNXk96ce0WL+OsapHW 3giSgCc+fga4Q2X8SCTVEQqJtMeuD/4bKG9AKUyurBJxvqpS0hyhKKnR8v4aTTUg9zYl W1aKUw9LnUQFq3fvdRmHVrES3KgpzV3S9KMtpWd0T7KbI9/Qcl6GeFZ+nAjKfn4ztkYo Nc5U0PcJVgZ+jliTgKwPkRODMc5nZulxCqK50xjuHKjORZV2/F+68wQcBBSB58uqqVNI TJpg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=MFbgsTj1gIQ/NCAEcazM5WhKWOay3u7n9MW5o94BS+U=; b=csxJEWcdsw3aHbV1PynrQ2YRZAfvbXhCCQ6bNFq5+JS3VU3mg5QQrJTRfvvLInvHZS CaU5MxkCSfX3IfTJQJLOIr0R1N74HuUEUr2+4A0HFDwhVqhGd2b98pHBTUm7IaJOALRQ 3kZiFTFZvVZHKMrpu9TMUTZt+2YsIdXwkwNTdRvWOpKcfQyc7XfKqYwFrS5ESzVABeug NHX673m+rThuK14iA7SJVTiWg4HZOYmAiqGb10XuXNPP/OHoe/xJ/p6HEzc7WmtVb2bi IcU1ytKMPRw6yhdH1alluyvJSIUIyMH5ZtSAMQ2+wOmeJEqmJeFpZIk5IXLA0DR3esCR QEHQ== X-Gm-Message-State: AA+aEWZa4YurDwJgmYb9nkEGD1Ac6Y7XoKXGPSRvo57w1CdV6J5vY8gC 77cWM++az+a9GWFzqDzRSqufgDcs X-Google-Smtp-Source: AFSGD/WLJllBqYvL7Ems1WUN3LQCtLcByW7w4QzSDx+cXH8vVj87Jb/r9ijLhgVxFA6UTVUZM8O4Ww== X-Received: by 2002:a9d:3a46:: with SMTP id j64mr14666960otc.103.1544711270878; Thu, 13 Dec 2018 06:27:50 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id k13sm1464011otj.19.2018.12.13.06.27.49 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 13 Dec 2018 06:27:50 -0800 (PST) Subject: Re: Pg_restore failing due to data inconsistency in dump file To: pgsql-admin@lists.postgresql.org References: From: Ron Message-ID: <3814e222-b3cc-2a0b-cf26-fd9057a1c498@gmail.com> Date: Thu, 13 Dec 2018 08:27:49 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------8F804B9B8543709246F346E7" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------8F804B9B8543709246F346E7 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit No.  That is *not* the first thing to do. OP needs to find out how the corruption got into the source in the first place.   There's probably corruption in one of the indexes in user_role_mapping anduser_entity which support fk_5hrm2vlf9ql5fu043kqepovbr. On 12/13/2018 06:21 AM, Shreeyansh Dba wrote: > Hi Soumitra, > > Normally this type of error encounter while insert or update data on the > table, which is dependable on the other table data. > > To avoid such error you should disable foreign keys before insert data and > enable it later. > > > > On Thu, Dec 13, 2018 at 4:50 PM soumitra bhandary > > wrote: > > Hello Team , > > I have encountered following issue while restoring the Postgres dump > using pg_restore . Please provide any suggestion to the same . > > 1. This dump was taken using bdr_dump as bdr enabled database and all > the BDR configuration and extension was excluded . > 2. DUMP was taken in a running DB server where BDR was running . > 3. Postgres Dump was taken in .dump format . > 4. During restoration of the same using pg_restore getting following > error . > > Error : > > pg_restore:[archiver (db)]Error from TOC entry 3061; 2606 17551 FK > CONSTRAINT fk_5hrm2vlf9ql5fu043kqepovbr DBNAME > pg_restore:[archiver (db)]could not execute query: ERROR: insert or > update on table "user_attribute" violates foreign key constraint > "fk_5hrm2vlf9ql5fu043kqepovbr" > DETAIL: Key (user_id)=(1f34ec07-7087-45ff-8a56-f36177752499) is not > present in table "user_entity". > Command was: ALTER TABLE ONLY user_attribute > ADD CONSTRAINT fk_5hrm2vlf9ql5fu043kqepovbr FOREIGN KEY (user_id) > REFERENCES user_entity... > pg_restore:[archiver (db)]Error from TOC entry 3065; 2606 17601 FK > CONSTRAINT fk_c4fqv34p1mbylloxang7b1q3l DBNAME > pg_restore:[archiver (db)]could not execute query: ERROR: insert or > update on table "user_role_mapping" violates foreign key constraint > "fk_c4fqv34p1mbylloxang7b1q3l" > DETAIL: Key (user_id)=(24d1ee2a-b05e-4c30-a292-84045c47611e) is not > present in table "user_entity". > > > Thanks , > > Soumitra > > -- Angular momentum makes the world go 'round. --------------8F804B9B8543709246F346E7 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit No.  That is not the first thing to do.

OP needs to find out how the corruption got into the source in the first place.   There's probably corruption in one of the indexes in user_role_mapping and user_entity which support fk_5hrm2vlf9ql5fu043kqepovbr.


On 12/13/2018 06:21 AM, Shreeyansh Dba wrote:
Hi Soumitra,

Normally this type of error encounter while insert or update data on the table, which is dependable on the other table data.

To avoid such error you should disable foreign keys before insert data and enable it later.



On Thu, Dec 13, 2018 at 4:50 PM soumitra bhandary <soumitra.bhandary@hotmail.com> wrote:
Hello Team , 

I have encountered following issue while restoring the Postgres dump using pg_restore . Please provide any suggestion to the same .
  1. This dump was taken using bdr_dump as bdr enabled database and all the BDR configuration and extension was excluded .
  2. DUMP was taken in a running DB server where BDR was running .
  3. Postgres Dump was taken in .dump format .
  4. During restoration of the same using pg_restore getting following error . 
Error : 

pg_restore: [archiver (db)] Error from TOC entry 3061; 2606 17551 FK CONSTRAINT fk_5hrm2vlf9ql5fu043kqepovbr DBNAME
pg_restore: [archiver (db)] could not execute query: ERROR: insert or update on table "user_attribute" violates foreign key constraint "fk_5hrm2vlf9ql5fu043kqepovbr"
DETAIL: Key (user_id)=(1f34ec07-7087-45ff-8a56-f36177752499) is not present in table "user_entity".
Command was: ALTER TABLE ONLY user_attribute
ADD CONSTRAINT fk_5hrm2vlf9ql5fu043kqepovbr FOREIGN KEY (user_id) REFERENCES user_entity...
pg_restore: [archiver (db)] Error from TOC entry 3065; 2606 17601 FK CONSTRAINT fk_c4fqv34p1mbylloxang7b1q3l DBNAME
pg_restore: [archiver (db)] could not execute query: ERROR: insert or update on table "user_role_mapping" violates foreign key constraint "fk_c4fqv34p1mbylloxang7b1q3l"
DETAIL: Key (user_id)=(24d1ee2a-b05e-4c30-a292-84045c47611e) is not present in table "user_entity".


Thanks , 

Soumitra 



--
Angular momentum makes the world go 'round.
--------------8F804B9B8543709246F346E7--