RE: pg_dumpall and restore

Thanks to all the responses. To fix:

1. At the source, pg_dump the 1 table only
2. Drop table at the target
3. Restore at the target

Also saw this: https://www.postgresql-archive.org/Duplicate-rows-during-pg-dump-td5871316.html
But I did not have to drop/recreate index at the source.
Thanks.

Maria A Rossi

From: Stanton Schmidt <sschmidt(at)rgllogistics(dot)com>
Sent: Wednesday, October 10, 2018 7:52 AM
To: Rossi, Maria <maria(dot)rossi(at)jackson(dot)com>
Cc: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>; pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>; pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Re: pg_dumpall and restore

EXTERNAL EMAIL

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" <maria(dot)rossi(at)jackson(dot)com<mailto:maria(dot)rossi(at)jackson(dot)com>>
To: "Laurenz Albe" <laurenz(dot)albe(at)cybertec(dot)at<mailto:laurenz(dot)albe(at)cybertec(dot)at>>, "pgsql-sql" <pgsql-sql(at)lists(dot)postgresql(dot)org<mailto:pgsql-sql(at)lists(dot)postgresql(dot)org>>, "pgsql-novice(at)lists(dot)postgresql(dot)org<mailto:pgsql-novice(at)lists(dot)postgresql(dot)org>" <pgsql-novice(at)lists(dot)postgresql(dot)org<mailto:pgsql-novice(at)lists(dot)postgresql(dot)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 <laurenz(dot)albe(at)cybertec(dot)at<mailto:laurenz(dot)albe(at)cybertec(dot)at>>
Sent: Tuesday, October 9, 2018 2:28 PM
To: Rossi, Maria <maria(dot)rossi(at)jackson(dot)com<mailto:maria(dot)rossi(at)jackson(dot)com>>; 'pgsql-sql(at)lists(dot)postgresql(dot)org' <pgsql-sql(at)lists(dot)postgresql(dot)org<mailto:pgsql-sql(at)lists(dot)postgresql(dot)org>>; 'pgsql-novice(at)lists(dot)postgresql(dot)org' <pgsql-novice(at)lists(dot)postgresql(dot)org<mailto:pgsql-novice(at)lists(dot)postgresql(dot)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=