Re: [GENERAL] Recovering data via raw table and field separators
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We used to have the C defined MAKE_EXPIRED_TUPLES_VISIBLE that would make deleted rows visible, but it seems it was removed in this commit as part of a restructuring: It was removed because it was utterly useless. It worked in some cases so I don't think it was useless. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Recovering data via raw table and field separators
Bruce Momjian [EMAIL PROTECTED] writes: We used to have the C defined MAKE_EXPIRED_TUPLES_VISIBLE that would make deleted rows visible, but it seems it was removed in this commit as part of a restructuring: It was removed because it was utterly useless. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: On 12/4/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: Ah sorry, I though you meant de table was dropped or the database was deleted. If you actually ran a DELETE FROM on the table, then yes they'll all be marked deleted. So, given a database table file that still has records in it, and given the fact that these records could be parsed and displayed if the proper utilty knew how to read the various data structures used to denote field and record length, is there no utility to do this? I seems that it would be fairly straight forward to somehow read the records, yet to pay no mind to the deleted flag (or whatever mechanism postgresql uses to mark them as deleted). We used to have the C defined MAKE_EXPIRED_TUPLES_VISIBLE that would make deleted rows visible, but it seems it was removed in this commit as part of a restructuring: http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/time/tqual.c.diff?r1=1.95;r2=1.96;f=h Sun Sep 3 15:59:39 2006 UTC (15 months, 1 week ago) by tgl Branches: MAIN Diff to: previous 1.95: preferred, colored Changes since revision 1.95: +100 -66 lines Arrange for GetSnapshotData to copy live-subtransaction XIDs from the PGPROC array into snapshots, and use this information to avoid visits to pg_subtrans in HeapTupleSatisfiesSnapshot. This appears to solve the pg_subtrans-related context swap storm problem that's been reported by several people for 8.1. While at it, modify GetSnapshotData to not take an exclusive lock on ProcArrayLock, as closer analysis shows that shared lock is always sufficient. Itagaki Takahiro and Tom Lane Not sure if we should re-add it for later use. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Recovering data via raw table and field separators
On 12/5/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Not that I know of. I think the simplest thing to get your tuples back is: 1. mark the transaction that deleted them as aborted in pg_clog 2. reset the hint bits in the deleted tuples, or hack your postgres copy to ignore hint bits You can figure out the transaction that deleted the tuples by seeing that their Xmax value is with pg_filedump. The hint bits part makes it rather messy :-( A bit beyond me I'm afriad, at least at my current level with postgresql. Does anyone offer a commercial tool to do this? Or, would anyone be interested in doing it for a fee? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Recovering data via raw table and field separators
On Thu, Dec 06, 2007 at 02:35:42PM -0500, John Wells wrote: A bit beyond me I'm afriad, at least at my current level with postgresql. Does anyone offer a commercial tool to do this? Or, would anyone be interested in doing it for a fee? There was a tool pgfsck which could dump table data, but it's not been updated in quite a while so I don't know if it'll work for your version... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Recovering data via raw table and field separators
On 12/6/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Thu, Dec 06, 2007 at 02:35:42PM -0500, John Wells wrote: A bit beyond me I'm afriad, at least at my current level with postgresql. Does anyone offer a commercial tool to do this? Or, would anyone be interested in doing it for a fee? There was a tool pgfsck which could dump table data, but it's not been updated in quite a while so I don't know if it'll work for your version... I'm on 8.1. I'll check and see if I can make it work. Thanks! John ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: I have pg_filedump installed, but can't figure out how to dump the rows themselves. I get the equivalent of the output at the end of this post. Looking over the --help, there's nothing obvious that has gotten me further. -i is the option you need; but you have to keep in mind that it won't show you the textual representation of data. It will only get you internal representation (binary). -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J MySQL is a toy compared to PostgreSQL. (Randal L. Schwartz) (http://archives.postgresql.org/pgsql-general/2005-07/msg00517.php) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: On 12/5/07, Alvaro Herrera [EMAIL PROTECTED] wrote: John Wells wrote: I have pg_filedump installed, but can't figure out how to dump the rows themselves. I get the equivalent of the output at the end of this post. Looking over the --help, there's nothing obvious that has gotten me further. -i is the option you need; but you have to keep in mind that it won't show you the textual representation of data. It will only get you internal representation (binary). Well, then isn't that no better than having the raw table file in this case? What I really need is a way to just spit out all tuples in the file in a readable format, regardless of whether they've been marked deleted. Is there any way to do this? Not that I know of. I think the simplest thing to get your tuples back is: 1. mark the transaction that deleted them as aborted in pg_clog 2. reset the hint bits in the deleted tuples, or hack your postgres copy to ignore hint bits You can figure out the transaction that deleted the tuples by seeing that their Xmax value is with pg_filedump. The hint bits part makes it rather messy :-( Please keep the list on CC:. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Recovering data via raw table and field separators
On 12/4/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Tue, Dec 04, 2007 at 03:05:53PM -0500, John Wells wrote: So, given a database table file that still has records in it, and given the fact that these records could be parsed and displayed if the proper utilty knew how to read the various data structures used to denote field and record length, is there no utility to do this? I seems that it would be fairly straight forward to somehow read the records, yet to pay no mind to the deleted flag (or whatever mechanism postgresql uses to mark them as deleted). Ofcourse, see the pg_filedump mentioned at the beginning of this thread. Thanks Martijn, I have pg_filedump installed, but can't figure out how to dump the rows themselves. I get the equivalent of the output at the end of this post. Looking over the --help, there's nothing obvious that has gotten me further. Is there a trick I'm missing? Thanks! John *** * PostgreSQL File/Block Formatted Dump Utility - Version 8.1.1 * * File: 17741 * Options used: -i -f * * Dump created on: Wed Dec 5 11:21:07 2007 *** Block0 Header - Block Offset: 0x Offsets: Lower 196 (0x00c4) Block: Size 8192 Version3Upper8192 (0x2000) LSN: logid 0 recoff 0x0181e758 Special 8192 (0x2000) Items: 44 Free Space: 7996 Length (including item array): 200 : 58e78101 0100 c420 X.. 0010: 00200320 441f 781e b81d . . D...x... 0020: f41c 301c 641b 981a 0...d... 0030: c419 f418 2418 5417 $...T... 0040: 8016 ac15 e014 1014 0050: 4013 7412 a011 d010 @...t... 0060: 0410 380f 680e 980d 8...h... 0070: c40c f80b 280b 540a (...T... 0080: 8809 b408 0008 4807 H... 0090: 9006 d805 2005 6804 ...h... 00a0: b403 fc02 4802 9001 H... 00b0: d400 4803 9402 e001 H... 00c0: 3001 0... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: I've been looking through the records with a hex editor, but the unfortunate thing is that I either don't see consistency with field separators or I'm overlooking them. There are no field separators. Perhaps you could extract some useful info with pg_filedump, which you can grab at http://sources.redhat.com/rhdb -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC No hay hombre que no aspire a la plenitud, es decir, la suma de experiencias de que un hombre es capaz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recovering data via raw table and field separators
On 12/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote: There are no field separators. Perhaps you could extract some useful info with pg_filedump, which you can grab at http://sources.redhat.com/rhdb So is it simply field width? Can one count the number of bytes based on native datatype length and determine field start/end? Thanks! John ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: On 12/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote: There are no field separators. Perhaps you could extract some useful info with pg_filedump, which you can grab at http://sources.redhat.com/rhdb So is it simply field width? Can one count the number of bytes based on native datatype length and determine field start/end? Yes. For variable length types, there is a 4-byte length word at the start of the field (unless you are using 8.3 which introduces more compact representations in some cases). -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ If it wasn't for my companion, I believe I'd be having the time of my life (John Dunbar) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Recovering data via raw table and field separators
On Tue, Dec 04, 2007 at 03:38:16PM -0300, Alvaro Herrera wrote: So is it simply field width? Can one count the number of bytes based on native datatype length and determine field start/end? Yes. For variable length types, there is a 4-byte length word at the start of the field (unless you are using 8.3 which introduces more compact representations in some cases). And NULLs are skipped entirely. They are represented in the null-bitmap at the beginning of the tuple. What sometimes works is creating a new table with the exact same structure, shutting down the postmaster and copying the old table over the new one. If it's the same cluster and the clog/xlog are still there it might work. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Recovering data via raw table and field separators
John Wells wrote: On 12/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote: John Wells wrote: On 12/4/07, Alvaro Herrera [EMAIL PROTECTED] wrote: There are no field separators. Perhaps you could extract some useful info with pg_filedump, which you can grab at http://sources.redhat.com/rhdb So is it simply field width? Can one count the number of bytes based on native datatype length and determine field start/end? Yes. For variable length types, there is a 4-byte length word at the start of the field (unless you are using 8.3 which introduces more compact representations in some cases). Oh fun...what about record separators? I think I could live with doing it by name and id. Since name is the last field in the table, and id is the first, one could assume that it looks something like this: 1rst record name | record separator | 2nd record id | etc, etc If I could split on that record separator I might be able to get what I'm after. There are no record separators either. Records are stored following offsets which are stored in a fixed-size array at the start of each page, called line pointers. Have a look at what pg_filedump shows you for interpreted output. Maybe you can get away with your idea, keeping in mind that record separators are just not there. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ Aprender sin pensar es inútil; pensar sin aprender, peligroso (Confucio) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Recovering data via raw table and field separators
On 12/4/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Tue, Dec 04, 2007 at 03:38:16PM -0300, Alvaro Herrera wrote: So is it simply field width? Can one count the number of bytes based on native datatype length and determine field start/end? Yes. For variable length types, there is a 4-byte length word at the start of the field (unless you are using 8.3 which introduces more compact representations in some cases). And NULLs are skipped entirely. They are represented in the null-bitmap at the beginning of the tuple. What sometimes works is creating a new table with the exact same structure, shutting down the postmaster and copying the old table over the new one. If it's the same cluster and the clog/xlog are still there it might work. Have a nice day, Martijn, Wow...interesting idea...but to clarify, I copied the table file *after* the delete was run on the table. Although the data appears to still be there, wouldn't they be marked as deleted in some way and not appear in the new table even if the copy worked? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Recovering data via raw table and field separators
On Tue, Dec 04, 2007 at 02:26:21PM -0500, John Wells wrote: Wow...interesting idea...but to clarify, I copied the table file *after* the delete was run on the table. Although the data appears to still be there, wouldn't they be marked as deleted in some way and not appear in the new table even if the copy worked? Ah sorry, I though you meant de table was dropped or the database was deleted. If you actually ran a DELETE FROM on the table, then yes they'll all be marked deleted. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Recovering data via raw table and field separators
On 12/4/07, Martijn van Oosterhout [EMAIL PROTECTED] wrote: Ah sorry, I though you meant de table was dropped or the database was deleted. If you actually ran a DELETE FROM on the table, then yes they'll all be marked deleted. So, given a database table file that still has records in it, and given the fact that these records could be parsed and displayed if the proper utilty knew how to read the various data structures used to denote field and record length, is there no utility to do this? I seems that it would be fairly straight forward to somehow read the records, yet to pay no mind to the deleted flag (or whatever mechanism postgresql uses to mark them as deleted). ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Recovering data via raw table and field separators
On Tue, Dec 04, 2007 at 03:05:53PM -0500, John Wells wrote: So, given a database table file that still has records in it, and given the fact that these records could be parsed and displayed if the proper utilty knew how to read the various data structures used to denote field and record length, is there no utility to do this? I seems that it would be fairly straight forward to somehow read the records, yet to pay no mind to the deleted flag (or whatever mechanism postgresql uses to mark them as deleted). Ofcourse, see the pg_filedump mentioned at the beginning of this thread. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
