Re: how could duplicate pkey exist in psql?

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
> <mailto: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
> <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>