Re: how could duplicate pkey exist in psql? - Mailing list pgsql-general
| From | Yan Chunlu |
|---|---|
| Subject | Re: how could duplicate pkey exist in psql? |
| Date | |
| Msg-id | CAOA66tHc8SmgPMy0MVEAxV4n1E-1u7wWq9EkNOsOpQS2X+uvSw@mail.gmail.com Whole thread Raw |
| In response to | Re: how could duplicate pkey exist in psql? (Edson Richter <richter@simkorp.com.br>) |
| Responses |
Re: how could duplicate pkey exist in psql?
(Adrian Klaver <adrian.klaver@gmail.com>)
|
| List | pgsql-general |
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@simkorp.com.br> wrote:
Em 17-11-2011 09:21, Yan Chunlu escreveu: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.I am using pgpool's replication feature, it does copy pg_xlog from one server to another, was that possible cause of the problem?
This scenario only happens when your script is copy data over own data... like in "rsync -ar root@127.0.0.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@simkorp.com.br> wrote:
Em 17-11-2011 03:19, Yan Chunlu escreveu: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.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 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: