Обсуждение: How to restore updated records
Hi all. Sorry for my problem. I am using pg on ~100 servers fro 5 y. without any problems, but this time I make a big mistake. A have a table tasks with about 20000 records. The table have ID as pk and some other fields. I typed : update tasks set c1='sss'; instead update tasks setc1='sss' where id=10; And the pg updated all my table tasks. I do not have any backup on this table. Exists any way to restore the data> After the mistake I stopped to execute any commands to this database and also I DO NOT HAVE running vacuum, vacuum analyze ot vacuum full analyze on this table. I know pg stores the old copy of data, but I do not have any idea how to restore this deleted records. Pls., point me to any solution for this problem. Many thanks and best regards, ivan. ----------------------------- SCENA - Единственото БЕЗПЛАТНО списание за мобилни комуникации и технологии. http://www.bgscena.com/
Ivan, if you have configured WAL archiving, you may want to look at http://www.postgresql.org/docs/8.1/static/backup-online.html,especially paragraph '23.3.4. Timelines'. List: Maybe someone already hacked the postmaster to extract deleted records?! HTH, Oliver. -----Ursprüngliche Nachricht----- Von: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] Im Auftrag von zz_11@mail.bg Gesendet: Montag, 11. Juni 2007 19:35 An: pgsql-admin@postgresql.org Betreff: [ADMIN] How to restore updated records Hi all. Sorry for my problem. I am using pg on ~100 servers fro 5 y. without any problems, but this time I make a big mistake. A have a table tasks with about 20000 records. The table have ID as pk and some other fields. I typed : update tasks set c1='sss'; instead update tasks setc1='sss' where id=10; And the pg updated all my table tasks. I do not have any backup on this table. Exists any way to restore the data> After the mistake I stopped to execute any commands to this database and also I DO NOT HAVE running vacuum, vacuum analyze ot vacuum full analyze on this table. I know pg stores the old copy of data, but I do not have any idea how to restore this deleted records. Pls., point me to any solution for this problem. Many thanks and best regards, ivan. ----------------------------- SCENA - Единственото БЕЗПЛАТНО списание за мобилни комуникации и технологии. http://www.bgscena.com/ ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
No, I do not have WAL archiving. I hope some one have done the hacking in postmaster. If no one have done this, my idea was to change the vacuum (or make e new copy). I am not familiar with pg code, but as I know by running vacuum, pg marks deleted records as free, and my idea was to mark this records as real record in the database (if it is possible). regards, ivan. > Ivan, > > if you have configured WAL archiving, you may want to > look at > http://www.postgresql.org/docs/8.1/static/backup-online.html, > especially paragraph '23.3..4. Timelines'. > > List: Maybe someone already hacked the postmaster to > extract deleted records?! > > HTH, Oliver. > > -----Ursprungliche Nachricht----- > Von: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] Im Auftrag von > zz_11@mail.bg > Gesendet: Montag, 11. Juni 2007 19:35 > An: pgsql-admin@postgresql.org > Betreff: [ADMIN] How to restore updated records > > > > Hi all. > Sorry for my problem. > I am using pg on ~100 servers fro 5 y. without any > problems, > but this time I make a big mistake. > > A have a table tasks with about 20000 records. > The table have ID as pk and some other fields. > > I typed : update tasks set c1='sss'; > instead update tasks setc1='sss' where id=10; > > And the pg updated all my table tasks. > > I do not have any backup on this table. > Exists any way to restore the data> > After the mistake I stopped to execute any commands to > this > database and also I DO NOT HAVE running vacuum, vacuum > analyze ot vacuum full analyze on this table. > > I know pg stores the old copy of data, but I do not have > any > idea how to restore this deleted records. > > Pls., point me to any solution for this problem. > > Many thanks and best regards, > ivan. > > ----------------------------- > > SCENA - Единственото БЕЗПЛАТНО списание за мобилни > комуникации и технологии. > http://www.bgscena.com/ > > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by > donating at > > http://www.postgresql.org/about/donate > > > ----------------------------- SCENA - Единственото БЕЗПЛАТНО списание за мобилни комуникации и технологии. http://www.bgscena.com/
Your best bet on something like this is probably going to be contacting a company that does PostgreSQL support and inquiring about data recovery. EnterpriseDB and Command Prompt both employ folks who could probably accomplish this; I think OmniTI might as well. There's probably a few others. On Jun 12, 2007, at 11:19 AM, zz_11@mail.bg wrote: > > No, I do not have WAL archiving. > I hope some one have done the hacking in postmaster. > If no one have done this, my idea was to change the vacuum > (or make e new copy). > I am not familiar with pg code, but as I know by running > vacuum, pg marks deleted records as free, and my idea was to > mark this records as real record in the database (if it is > possible). > > regards, > ivan. > >> Ivan, >> >> if you have configured WAL archiving, you may want to >> look at >> > http://www.postgresql.org/docs/8.1/static/backup-online.html, >> especially paragraph '23.3..4. Timelines'. >> >> List: Maybe someone already hacked the postmaster to >> extract deleted records?! >> >> HTH, Oliver. >> >> -----Ursprungliche Nachricht----- >> Von: pgsql-admin-owner@postgresql.org >> [mailto:pgsql-admin-owner@postgresql.org] Im Auftrag von >> zz_11@mail.bg >> Gesendet: Montag, 11. Juni 2007 19:35 >> An: pgsql-admin@postgresql.org >> Betreff: [ADMIN] How to restore updated records >> >> >> >> Hi all. >> Sorry for my problem. >> I am using pg on ~100 servers fro 5 y. without any >> problems, >> but this time I make a big mistake. >> >> A have a table tasks with about 20000 records. >> The table have ID as pk and some other fields. >> >> I typed : update tasks set c1='sss'; >> instead update tasks setc1='sss' where id=10; >> >> And the pg updated all my table tasks. >> >> I do not have any backup on this table. >> Exists any way to restore the data> >> After the mistake I stopped to execute any commands to >> this >> database and also I DO NOT HAVE running vacuum, vacuum >> analyze ot vacuum full analyze on this table. >> >> I know pg stores the old copy of data, but I do not have >> any >> idea how to restore this deleted records. >> >> Pls., point me to any solution for this problem. >> >> Many thanks and best regards, >> ivan. >> >> ----------------------------- >> >> SCENA - Единственото БЕЗПЛАТНО списание за мобилни >> комуникации и технологии. >> http://www.bgscena.com/ >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 7: You can help support the PostgreSQL project by >> donating at >> >> http://www.postgresql.org/about/donate >> >> >> > > > > > ----------------------------- > > SCENA - Единственото БЕЗПЛАТНО списание за мобилни комуникации и > технологии. > http://www.bgscena.com/ > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
About 2 years ago a new guy at our company deleted all contents of a table on a development server and I managed to restore them by changing the XID value with pg_resetxlog. You can get last chackpoint's XID by running pg_controldata; then use pg_resetxlog -x and try different values. You'll need to repeat the "copy from backup - change XID - start postmaster" cycle a few times; but it should be doable in less than an hour. Regards, Aleksander Jim Nasby wrote: > Your best bet on something like this is probably going to be contacting > a company that does PostgreSQL support and inquiring about data > recovery. EnterpriseDB and Command Prompt both employ folks who could > probably accomplish this; I think OmniTI might as well. There's probably > a few others. > > On Jun 12, 2007, at 11:19 AM, zz_11@mail.bg wrote: > >> >> No, I do not have WAL archiving. >> I hope some one have done the hacking in postmaster. >> If no one have done this, my idea was to change the vacuum >> (or make e new copy). >> I am not familiar with pg code, but as I know by running >> vacuum, pg marks deleted records as free, and my idea was to >> mark this records as real record in the database (if it is >> possible). >> >> regards, >> ivan. >>