Message-ID: <47175ca8aea768568fb51e1a91c94d436ef79d97.camel@cybertec.at>
Subject: Re: pg_dumpall and restore
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Rossi, Maria" <maria.rossi@jackson.com>,
	"'pgsql-sql@lists.postgresql.org'"
	 <pgsql-sql@lists.postgresql.org>, "'pgsql-novice@lists.postgresql.org'"
	 <pgsql-novice@lists.postgresql.org>
Date: Tue, 09 Oct 2018 20:28:25 +0200
In-Reply-To: 
 <323742d3468543f590fc081c7fba0c7a@DC03PXMBP003.jacksonnational.com>
References: 
	<323742d3468543f590fc081c7fba0c7a@DC03PXMBP003.jacksonnational.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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://www.cybertec-postgresql.com