Re: how could duplicate pkey exist in psql?

got it. thank you very much for you help. I found out this problem too
late, and there is no backup.

luckily there was not too much data for this, and my app keeps running
without error.

I am not sure if they are related but I could not use pg_restore to import
data dumped by "pg_dump -Fc";

pg_restore will print some error message about "duplicate primary key", and
the table is empty. no data has been imported.

pg_restore supposed to import the data and ignore the errors. does any
one have the similar problem?

On Thu, Nov 17, 2011 at 11:08 PM, Edson Richter <richter(at)simkorp(dot)com(dot)br>wrote:

> Em 17-11-2011 09:21, Yan Chunlu escreveu:
>
> I am using pgpool's replication feature, it does copy pg_xlog from one
> server to another, was that possible cause of the problem?
>
>
> I did not mean that this IS your problem, I just gave you a tip regarding
> a problem I had in the past, that eventually has same simptom.
>
> This scenario only happens when your script is copy data over own data...
> like in "rsync -ar root(at)127(dot)0(dot)0(dot)1:/var/lib/pgsql/9.0/data/*/var/lib/pgsql/9.0/data/"
>
> the command above is highly dangerous because it copies data over the
> network link over its own data... if you have transactions runing during
> the command above, you will get a crash (and, in my case, I had duplicate
> primary keys).
>
> Would be better to check if this could be happening to you... some script
> overwriting data using rsync, cp, etc... I had no other situation where
> Postgresql allowed duplicate keys.
>
> Hope this helps,
>
> Edson.
>
>
>
>
> thanks for the help!
>
> On Thu, Nov 17, 2011 at 5:38 PM, Edson Richter <richter(at)simkorp(dot)com(dot)br>wrote:
>
>>
>> Em 17-11-2011 03:19, Yan Chunlu escreveu:
>>
>> recently I have found several tables has exactly the same pkey, here is
>>> the definition:
>>> "diggcontent_data_account_pkey" PRIMARY KEY, btree (thing_id, key)
>>>
>>>
>>> the data is like this:
>>>
>>> 159292 | funnypics_link_point | 41
>>>
>>> | num
>>> 159292 | funnypics_link_point | 40
>>>
>>> | num
>>>
>>>
>>> I could not even update this record.
>>>
>>> really confused about how could this happen... thanks!
>>>
>>
>> I know one scenario this can happen on Linux. In my case, it was caused
>> by a "rsync"... instead copy to a different location, script was copying
>> pg_xlog over own pg_xlog.
>>
>> I did this stupidity once, and learned for a life time. Lost two hours of
>> work to recover everything (from backup, at least I had one).
>>
>> Be careful with rsync and cp, since Linux does not block files from being
>> overwriten even when they are in use.
>>
>>
>> Regards,
>>
>> Edson.
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>