pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Rossi, Maria <maria.rossi@jackson.com>
To: Stanton Schmidt <sschmidt@rgllogistics.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Cc: pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
Subject: RE: pg_dumpall and restore
Date: Wed, 10 Oct 2018 12:01:55 +0000
Message-ID: <1ded20e1c1f149d0810e2656452595b4@DC03PXMBP003.jacksonnational.com> (raw)
In-Reply-To: <26359686.2839648.1539172290439.JavaMail.zimbra@rglholdings.com>
References: <323742d3468543f590fc081c7fba0c7a@DC03PXMBP003.jacksonnational.com>
<47175ca8aea768568fb51e1a91c94d436ef79d97.camel@cybertec.at>
<99b52306364e41068b6851db669e3db8@DC03PXMBP003.jacksonnational.com>
<26359686.2839648.1539172290439.JavaMail.zimbra@rglholdings.com>
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@rgllogistics.com>
Sent: Wednesday, October 10, 2018 7:52 AM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-sql <pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.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.rossi@jackson.com<mailto:maria.rossi@jackson.com>>
To: "Laurenz Albe" <laurenz.albe@cybertec.at<mailto:laurenz.albe@cybertec.at>>, "pgsql-sql" <pgsql-sql@lists.postgresql.org<mailto:pgsql-sql@lists.postgresql.org>>, "pgsql-novice@lists.postgresql.org<mailto:pgsql-novice@lists.postgresql.org>" <pgsql-novice@lists.postgresql.org<mailto: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 <laurenz.albe@cybertec.at<mailto:laurenz.albe@cybertec.at>>
Sent: Tuesday, October 9, 2018 2:28 PM
To: Rossi, Maria <maria.rossi@jackson.com<mailto:maria.rossi@jackson.com>>; 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org<mailto:pgsql-sql@lists.postgresql.org>>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org<mailto: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&...
view thread (8+ messages)
Message-ID: <1ded20e1c1f149d0810e2656452595b4@DC03PXMBP003.jacksonnational.com>
Permalink: ../1ded20e1c1f149d0810e2656452595b4@DC03PXMBP003.jacksonnational.com/
Also on: postgresql.org/message-id/1ded20e1c1f149d0810e2656452595b4@DC03PXMBP003.jacksonnational.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maria.rossi@jackson.com, sschmidt@rgllogistics.com, laurenz.albe@cybertec.at, pgsql-sql@lists.postgresql.org, pgsql-novice@lists.postgresql.org
Subject: RE: pg_dumpall and restore
In-Reply-To: <1ded20e1c1f149d0810e2656452595b4@DC03PXMBP003.jacksonnational.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox