Thread: Clues about tables fileformat
First, the embarrasing thing, in summary, deleted table, no backup at all, neither vacuum so ended with a pretty file with all my data but no clever way to access them. I've search for this question in the archives and only find a reference to "The Tao of Backup" which enlightened me, and will be very useful for future reference but doesn't solve my present problem. Also find some references for a "not so hard to make" recover utility so... I'm not a bad C and PERL hacker so I'm trying to contribute this utility but havent't found any doc about this file format (well, yes, http://developer.postgresql.org/docs/postgres/page.html, Chapter 7. Page Files, but it's somehow cryptical), I've scanned also some of the source code but haven't been able to find any information that helps me (I've only studied some parts of the fti contrib module before), also tried to do some rev. eng. but this is no the way to go so I would be grateful is someone can: - Point me to some more detailed developer doc about this file format. - Point me to some part of the source code that could be of help, please be precise because I'm not very familiar with the tree. - Or even explain, in summary, something about this file format, or some of the bytes needed for me to write this tool. - Of course, if someone has made or started this tool I will be very happy to receive an URL to a .tar.gz ;-) . On another point, I've been thinking about this tool, and will accept hints about this proccess: - The tool will receive the schema of the table and a copy of the file which stores this table, as far I know the schema is not stored in the table data file. - After scanning the file will print a (sorted or filtered by transaction id or number of altered rows) list of transactions. - Ask for a trasaction id and store all the data on a new .sql file much like the one generated from a pg_dump. I think that this is fairly feasible and practical, am I wrong? So, thanks in advance for your help, and thank you all for this killer application. PS: Oops, forgot something, I'm talkin about PG 7.1 after that I will port it to 7.2 if someone finds it usefull. Miguel A. Arévalo marevalo@marevalo.net ____________________________________________ Biblios.Org: All your Book Are Belong to Us.
The short answer is that you will need to modify tqual.c and have some of those routines return 'true' all the time. I think HeapTupleSatisfiesSnapshot() is the one you want. Recompile and install and a query on the table will show you the data, or at least it should. Make sure you back up your /data file before trying this and restore them once you have COPY'ed out the table you want. --------------------------------------------------------------------------- Miguel A. =?ISO-8859-1?Q?Ar=E9valo?= wrote: > First, the embarrasing thing, in summary, deleted table, no backup at > all, neither vacuum so ended with a pretty file with all my data but no > clever way to access them. > > I've search for this question in the archives and only find a reference > to "The Tao of Backup" which enlightened me, and will be very useful for > future reference but doesn't solve my present problem. Also find some > references for a "not so hard to make" recover utility so... > > I'm not a bad C and PERL hacker so I'm trying to contribute this utility > but havent't found any doc about this file format (well, yes, > http://developer.postgresql.org/docs/postgres/page.html, Chapter 7. Page > Files, but it's somehow cryptical), I've scanned also > some of the source code but haven't been able to find any information > that helps me (I've only studied some parts of the fti contrib module > before), also tried to do some rev. eng. but this is no the way to go so > I would be grateful is someone can: > > - Point me to some more detailed developer doc about this file format. > > - Point me to some part of the source code that could be of help, please > be precise because I'm not very familiar with the tree. > > - Or even explain, in summary, something about this file format, or some > of the bytes needed for me to write this tool. > > - Of course, if someone has made or started this tool I will be very > happy to receive an URL to a .tar.gz ;-) . > > On another point, I've been thinking about this tool, and will accept > hints about this proccess: > > - The tool will receive the schema of the table and a copy of the file > which stores this table, as far I know the schema is not stored in the > table data file. > > - After scanning the file will print a (sorted or filtered by > transaction id or number of altered rows) list of transactions. > > - Ask for a trasaction id and store all the data on a new .sql file much > like the one generated from a pg_dump. > > I think that this is fairly feasible and practical, am I wrong? > > > So, thanks in advance for your help, and thank you all for this killer > application. > > PS: Oops, forgot something, I'm talkin about PG 7.1 after that I will > port it to 7.2 if someone finds it usefull. > > Miguel A. Ar?valo > marevalo@marevalo.net > ____________________________________________ > Biblios.Org: All your Book Are Belong to Us. > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Amazing,
although my computer runs only free software for many months (and partly
for many years) I still get shocked when I get a real useful answer from
a mailing list, from one of the real men, that really solves real
problems with a piece of software in less time that Microsoft Support
used to last to pick up the phone in my (now happily far behind) days of
NT, Exchange and M$ SQL Server administrator.
Of course thanks a lot for the quick and precise answer. Of course, it
worked.
But I'm still interested in working in that recovery tool (of course
with less pressure), as it would be very useful, not only for this kind
of "problems" but with problems with corruption (with some love) and
recovering data without stopping the database (or with minimum downtime)
nor installing PostgreSQL on another machine, so I'm still interested in
any doc or information of help in order to implement it.
And thanks again for this excellent (o)RDBMS, I'm dying to see that
wonderful eager replication system that is being integrated into 7.3 and
later
Miguel A. Arévalo
marevalo@marevalo.net
____________________________
Biblios.Org: Save the trees!
El sáb, 02-03-2002 a las 19:18, Bruce Momjian escribió:
>
> The short answer is that you will need to modify tqual.c and have some
> of those routines return 'true' all the time. I think
> HeapTupleSatisfiesSnapshot() is the one you want. Recompile and install
> and a query on the table will show you the data, or at least it should.
>
> Make sure you back up your /data file before trying this and restore
> them once you have COPY'ed out the table you want.
>
> ---------------------------------------------------------------------------
>
> Miguel A. =?ISO-8859-1?Q?Ar=E9valo?= wrote:
Blah, blah, blah ...
> > On another point, I've been thinking about this tool, and will accept
> > hints about this proccess:
> >
> > - The tool will receive the schema of the table and a copy of the file
> > which stores this table, as far I know the schema is not stored in the
> > table data file.
> >
> > - After scanning the file will print a (sorted or filtered by
> > transaction id or number of altered rows) list of transactions.
> >
> > - Ask for a trasaction id and store all the data on a new .sql file much
> > like the one generated from a pg_dump.
> >
> > I think that this is fairly feasible and practical, am I wrong?
> >
> >
> > So, thanks in advance for your help, and thank you all for this killer
> > application.
> >
> > PS: Oops, forgot something, I'm talkin about PG 7.1 after that I will
> > port it to 7.2 if someone finds it usefull.
> >
> > Miguel A. Ar?valo
> > marevalo@marevalo.net
> > ____________________________________________
> > Biblios.Org: All your Book Are Belong to Us.
> >
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 853-3000
> + If your life is a hard drive, | 830 Blythe Avenue
> + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Miguel A. Ar�valo wrote: > Amazing, > > although my computer runs only free software for many months (and partly > for many years) I still get shocked when I get a real useful answer from > a mailing list, from one of the real men, that really solves real > problems with a piece of software in less time that Microsoft Support > used to last to pick up the phone in my (now happily far behind) days of > NT, Exchange and M$ SQL Server administrator. > > Of course thanks a lot for the quick and precise answer. Of course, it > worked. > > But I'm still interested in working in that recovery tool (of course > with less pressure), as it would be very useful, not only for this kind > of "problems" but with problems with corruption (with some love) and > recovering data without stopping the database (or with minimum downtime) > nor installing PostgreSQL on another machine, so I'm still interested in > any doc or information of help in order to implement it. > > And thanks again for this excellent (o)RDBMS, I'm dying to see that > wonderful eager replication system that is being integrated into 7.3 and > later I have started work on a data recovery article. You will hear on general when it is done. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026