agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedpg_dumpall and restore
8+ messages / 5 participants
[nested] [flat]
* pg_dumpall and restore
@ 2018-10-09 17:18 Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 2 replies; 8+ messages in thread
From: Rossi, Maria @ 2018-10-09 17:18 UTC (permalink / raw)
To: 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org>; +Cc: Rossi, Maria <maria.rossi@jackson.com>
Hi,
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.
Thanks.
Maria
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dumpall and restore
@ 2018-10-09 17:58 Keith <keith@keithf4.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
1 sibling, 1 reply; 8+ messages in thread
From: Keith @ 2018-10-09 17:58 UTC (permalink / raw)
To: maria.rossi@jackson.com; +Cc: pgsql-sql@lists.postgresql.org; pgsql-novice@lists.postgresql.org
On Tue, Oct 9, 2018 at 1:19 PM Rossi, Maria <maria.rossi@jackson.com> wrote:
> Hi,
>
>
>
> 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.
>
>
>
> Thanks.
>
> Maria
>
>
>
>
>
>
>
Did you use the pg_dumpall binary from 10.5 to create the dump?
Keith
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: pg_dumpall and restore
@ 2018-10-09 18:05 Rossi, Maria <maria.rossi@jackson.com>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rossi, Maria @ 2018-10-09 18:05 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
Yes, I did use the V10.5 pg_dumpall
Maria A Rossi
From: Keith <keith@keithf4.com>
Sent: Tuesday, October 9, 2018 1:58 PM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: pgsql-sql@lists.postgresql.org; pgsql-novice@lists.postgresql.org
Subject: Re: pg_dumpall and restore
EXTERNAL EMAIL
On Tue, Oct 9, 2018 at 1:19 PM Rossi, Maria <maria.rossi@jackson.com<mailto:maria.rossi@jackson.com>> wrote:
Hi,
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.
Thanks.
Maria
Did you use the pg_dumpall binary from 10.5 to create the dump?
Keith
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dumpall and restore
@ 2018-10-09 18:28 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Rossi, Maria <maria.rossi@jackson.com>
1 sibling, 2 replies; 8+ messages in thread
From: Laurenz Albe @ 2018-10-09 18:28 UTC (permalink / raw)
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>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dumpall and restore
@ 2018-10-09 18:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2018-10-09 18:32 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: 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>
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Rossi, Maria wrote:
>> Has anyone encountered having dups rows loaded? Any idea what caused this and how to prevent?
> I don't believe that pg_dumpall miraculously duplicated the row.
What seems much more likely is that the dump script was loaded twice.
The script expects to be fed into an empty database ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: pg_dumpall and restore
@ 2018-10-09 20:05 Rossi, Maria <maria.rossi@jackson.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 8+ messages in thread
From: Rossi, Maria @ 2018-10-09 20:05 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org>
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>
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 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_dumpall and restore
@ 2018-10-10 11:51 Stanton Schmidt <sschmidt@rgllogistics.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 1 reply; 8+ messages in thread
From: Stanton Schmidt @ 2018-10-10 11:51 UTC (permalink / raw)
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 <pgsql-novice@lists.postgresql.org>
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>
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
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>
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 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&...
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: pg_dumpall and restore
@ 2018-10-10 12:01 Rossi, Maria <maria.rossi@jackson.com>
parent: Stanton Schmidt <sschmidt@rgllogistics.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rossi, Maria @ 2018-10-10 12:01 UTC (permalink / raw)
To: Stanton Schmidt <sschmidt@rgllogistics.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-sql <pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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&...
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-10-10 12:01 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-10-09 17:18 pg_dumpall and restore Rossi, Maria <maria.rossi@jackson.com>
2018-10-09 17:58 ` Keith <keith@keithf4.com>
2018-10-09 18:05 ` Rossi, Maria <maria.rossi@jackson.com>
2018-10-09 18:28 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-10-09 18:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-10-09 20:05 ` Rossi, Maria <maria.rossi@jackson.com>
2018-10-10 11:51 ` Stanton Schmidt <sschmidt@rgllogistics.com>
2018-10-10 12:01 ` Rossi, Maria <maria.rossi@jackson.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox