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
selectwithout any WHERE clause.
Thanks.
Maria
-----Original Message-----
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Tuesday, October 9, 2018 2:28 PM
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>
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
indexyou 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