Re: Missing PRIMARY KEYs and duplicated rows

On 13/04/17 08:46, Chris Pacejo wrote:

> Hi all, we're encountering a very strange issue lately on a 9.4.5 server.
>
> We recently performed a dump & restore of the entire cluster to
> migrate to a new server. All but one of the databases seem fine.
> That one is encountering a very strange issue.
>
> It seems that periodically, the primary keys (and underlying indexes)
> of all tables in the database disappear, and every row in each table
> gets duplicated. This appears to occur at the storage level, as the
> duplicated rows get their own ctids (so it's not just a query
> artifact). I've seen this happen twice in the past week.
>
> I can delete the duplicate rows (by their ctid). In the first
> instance, I was unable to re-add the primary key, as it had reappeared
> by the time I issued the statement. In the most recent instance, I
> did re-add the primary key to a table, and the other primary keys have
> not yet re-appeared.
>
> All indexes are b-tree indexes. They were not created using
> CONCURRENTLY. They are in the default tablespace. The installation
> is on an Intel CentOS machine using the PGDG RPMs. I've checked the
> release notes for all 9.4 bugfixes and do not see anything which could
> relate to this issue.
>
> Any insight into this issue? We will probably just drop and recreate
> the database from the recent dump, as it is on a production server and
> we'd rather not take chances tinkering around with it. But it would
> be nice to know why/how we've encountered this to ensure it won't
> happen again, because frankly it's quite a scary issue.
>
>

I'd be inclined to suspect the hardware with weird behaviour like that
(memory and disk errors in particular).

Cheers

Mark