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 <[email protected]>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 > [email protected]:/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 <[email protected]>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 ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > >
