Обсуждение: pg_dumpall and restore

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

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

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

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

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

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