Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gAD1u-0003Go-HG for pgsql-sql@arkaria.postgresql.org; Wed, 10 Oct 2018 11:51:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gAD1r-0005Nh-SL for pgsql-sql@arkaria.postgresql.org; Wed, 10 Oct 2018 11:51:51 +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_SHA384:256) (Exim 4.89) (envelope-from ) id 1gAD1r-0005NW-Lf; Wed, 10 Oct 2018 11:51:51 +0000 Received: from mx0b-0021dc01.pphosted.com ([148.163.152.220] helo=mx0a-0021dc01.pphosted.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gAD1i-0002pz-AE; Wed, 10 Oct 2018 11:51:49 +0000 Received: from pps.filterd (m0096563.ppops.net [127.0.0.1]) by mx0b-0021dc01.pphosted.com (8.16.0.23/8.16.0.23) with SMTP id w9ABlFUF031508; Wed, 10 Oct 2018 06:51:31 -0500 Received: from mail.rglholdings.com (dns152.b.register.com [98.102.82.53] (may be forged)) by mx0b-0021dc01.pphosted.com with ESMTP id 2mxq6eskmk-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Wed, 10 Oct 2018 06:51:31 -0500 Received: from mail.rglholdings.com (localhost [127.0.0.1]) by mail.rglholdings.com (Postfix) with ESMTPS id A57CA14C65; Wed, 10 Oct 2018 06:51:30 -0500 (CDT) Received: from localhost (localhost [127.0.0.1]) by mail.rglholdings.com (Postfix) with ESMTP id 974DF14BDA; Wed, 10 Oct 2018 06:51:30 -0500 (CDT) X-Virus-Scanned: amavisd-new at rglholdings.com Received: from mail.rglholdings.com ([127.0.0.1]) by localhost (mail.rglholdings.com [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id so3lIEItcWfJ; Wed, 10 Oct 2018 06:51:30 -0500 (CDT) Received: from mail.rglholdings.com (mail.ipa.rglholdings.com [10.1.20.35]) by mail.rglholdings.com (Postfix) with ESMTP id 7995414AFF; Wed, 10 Oct 2018 06:51:30 -0500 (CDT) Date: Wed, 10 Oct 2018 06:51:30 -0500 (CDT) From: Stanton Schmidt To: "Rossi, Maria" Cc: Laurenz Albe , pgsql-sql , "pgsql-novice@lists.postgresql.org" Message-ID: <26359686.2839648.1539172290439.JavaMail.zimbra@rglholdings.com> In-Reply-To: <99b52306364e41068b6851db669e3db8@DC03PXMBP003.jacksonnational.com> References: <323742d3468543f590fc081c7fba0c7a@DC03PXMBP003.jacksonnational.com> <47175ca8aea768568fb51e1a91c94d436ef79d97.camel@cybertec.at> <99b52306364e41068b6851db669e3db8@DC03PXMBP003.jacksonnational.com> Subject: Re: pg_dumpall and restore MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_b0c5bc60-abdc-4bff-a432-7f0ec9ed607c" X-Originating-IP: [10.1.20.35] X-Mailer: Zimbra 8.7.11_GA_3012 (ZimbraWebClient - GC69 (Win)/8.7.11_GA_3012) Thread-Topic: pg_dumpall and restore Thread-Index: AdRf82EKpgBRUa2CQrS18+mj5Fx3JAALAOmAAAWSJQB29eeRVQ== X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2018-10-10_08:,, signatures=0 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 priorityscore=1501 malwarescore=0 suspectscore=0 phishscore=0 bulkscore=0 spamscore=0 clxscore=1011 lowpriorityscore=0 mlxscore=0 impostorscore=0 mlxlogscore=999 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1807170000 definitions=main-1810100120 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --=_b0c5bc60-abdc-4bff-a432-7f0ec9ed607c Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit When this happened to me the only explanation I could find was that the original table/index had been corrupted. The only way to "fix" things was to create a copy of the table and insert select distinct * from orig_table. Hope this helps. Stanton From: "Rossi, Maria" To: "Laurenz Albe" , "pgsql-sql" , "pgsql-novice@lists.postgresql.org" Sent: Tuesday, October 9, 2018 3:05:59 PM Subject: RE: pg_dumpall and restore The table has only 2 columns, name and value. Select count(*) from table1 at the old database returned 115, on the new database, it returned 117. This a simple select without any WHERE clause. Thanks. Maria -----Original Message----- From: Laurenz Albe Sent: Tuesday, October 9, 2018 2:28 PM To: Rossi, Maria ; 'pgsql-sql@lists.postgresql.org' ; 'pgsql-novice@lists.postgresql.org' Subject: Re: pg_dumpall and restore EXTERNAL EMAIL Rossi, Maria wrote: > I upgraded our postgres database from V9.3 to V10.5. Used pg_dumpall then restore it to the new instance. > After the restore, we notice that 1 table had duplicate rows, such that it was not able to create the primary key. > I checked the old database, it does not have the dups. > Has anyone encountered having dups rows loaded? Any idea what caused this and how to prevent? > > Your help would be much appreciated. I don't believe that pg_dumpall miraculously duplicated the row. You probably *do* have a duplicate row, and hence table corruption, but I suspect that one of the rows is not in the index you used to look for the row. If you query: SELECT * FROM tab WHERE id = 42; the query will likely use the index on "id" and find only one of the rows. You should SET enable_indexscan = off; SET enable_indexonlyscan = off; and then repeat the query, so that a sequential scan is used. To fix, delete one of the rows and reindex. You can identify a row by its tuple id: SELECT ctid, * FROM tab WHERE id = 42; Yours, Laurenz Albe -- Cybertec | https://urldefense.proofpoint.com/v2/url?u=https-3A__www.cybertec-2Dpostgresql.com&d=DwIGaQ&c=eLkx2stMcShI0L6xvxICXHnFB9zmDvZmvnhsCd8Gf8M&r=EWbtHQpXIg2XbQduIylzyXrAFdbjWaMyy-p_LkyfjhQ&m=Oyq62lylPJc2GMn32y1LP8A7tG0QlTVCZtZ4jE4zwIc&s=wTd0yL8L_97S1EgPJeIdgkZwFsc_6lWGn7qd3XrBoNY&e= --=_b0c5bc60-abdc-4bff-a432-7f0ec9ed607c Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
W= hen this happened to me the only explanation I could find was that the orig= inal table/index had been corrupted.
The only way to "fix" things= was to create a copy of the table and insert select distinct * from orig_t= able.

Hope this helps.
<= div>
Stanton

From: "Rossi, Maria" <maria.r= ossi@jackson.com>
To: "Laurenz Albe" <laurenz.albe@cybertec= .at>, "pgsql-sql" <pgsql-sql@lists.postgresql.org>, "pgsql-novice@= lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Sent:= Tuesday, October 9, 2018 3:05:59 PM
Subject: RE: pg_dumpall = and restore

Th= e table  has only 2 columns, name and value.
Select count(*) from t= able1 at the old database returned 115, on  the  new database, it= returned 117.   This a simple select without any WHERE clause.
Tha= nks.

Maria



-----Original Message-----
From: Lauren= z Albe <laurenz.albe@cybertec.at>
Sent: Tuesday, October 9, 2018 = 2:28 PM
To: Rossi, Maria <maria.rossi@jackson.com>; 'pgsql-sql@lis= ts.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@li= sts.postgresql.org' <pgsql-novice@lists.postgresql.org>
Subject: R= e: pg_dumpall and restore

EXTERNAL EMAIL


Rossi, Maria wro= te:
> I upgraded our postgres database  from V9.3 to V10.5. &nbs= p;Used pg_dumpall then restore it to the new  instance.
> After = the restore, we notice that 1 table had duplicate rows, such that it was no= t able to create the primary key.
> I checked the old database, it do= es not have the dups.
> Has anyone encountered  having dups rows=  loaded?  Any idea  what caused this and how to prevent?>  
> Your help would be much appreciated.

I don't be= lieve that pg_dumpall miraculously duplicated the row.

You probably = *do* have a duplicate row, and hence table corruption, but I suspect that o= ne of the rows is not in the index you used to look for the row.

If = you query:

   SELECT * FROM tab WHERE id =3D 42;

th= e query will likely use the index on "id" and find only one of the rows.
You should

   SET enable_indexscan =3D off;
 =   SET enable_indexonlyscan =3D off;

and then repeat the query, = so that a sequential scan is used.

To fix, delete one of the rows an= d reindex.

You can identify a row by its tuple id:

 &nbs= p; SELECT ctid, * FROM tab WHERE id =3D 42;

Yours,
Laurenz Albe--
Cybertec | https://urldefense.proofpoint.com/v2/url?u=3Dhttps-3A__w= ww.cybertec-2Dpostgresql.com&d=3DDwIGaQ&c=3DeLkx2stMcShI0L6xvxICXHn= FB9zmDvZmvnhsCd8Gf8M&r=3DEWbtHQpXIg2XbQduIylzyXrAFdbjWaMyy-p_LkyfjhQ&am= p;m=3DOyq62lylPJc2GMn32y1LP8A7tG0QlTVCZtZ4jE4zwIc&s=3DwTd0yL8L_97S1EgPJ= eIdgkZwFsc_6lWGn7qd3XrBoNY&e=3D
--=_b0c5bc60-abdc-4bff-a432-7f0ec9ed607c--