Re: (Updated) Table File Format

Michael Richards wrote:
> 
>  I need a little help on the format of the postgres tables.
> 
>  I've got this wonderfully corrupted database where just about everything is
>  fubar. I've tried a number of things to get it back using postgres and
>  related tools with no success. It looks like most of the data is there, but
>  there may be a small amount of corruption that's causing all kinds of
>  problems.

Find attached a python script that I used to get deleted (actually all
;) 
records from a database table.

It was not corrupted, just a simple programming error in client software 
had deleted more than needed. 

Fortunately it was not vacuumed so the data itself (info for web-based
paper 
postcard sending system) was there 

It works as-is only for my table as the field extraction code is
hard-coded, but 
it should be quite easy to modify for your needs

It worked 1 year ago probably on 6.4.x . I hope that the structure had
not 
changed since.

sendcard.py is the actual script used, pgtabdump.py is a somewhat
cleaned-up version


---------------
Hannu#!/usr/bin/python

import sys,os,struct,string

site_base = "/var/lib/pgsql/base"
db_name = "betest"
table_name = "test"

db_path = os.path.join(site_base,db_name)
table_path = os.path.join(db_path,table_name)

page_size = 8*1024

def strbits(s,len):   bits = []   while s:       c = s[0]       s = s[1:]       b = struct.unpack('B',c)[0]       for i
inrange(8):           if b & (1<<i): bits.append(1)           else: bits.append(0)   return
string.join(map(str,bits),'')[:len]


class table_page:   "class to represent a database table page"   def __init__(self,fd,page_nr):
fd.seek(page_nr*page_size)      self.rawdata = fd.read(page_size)       self.lower,\       self.upper,\
self.special,\      self.opaque = struct.unpack('HHHH',self.rawdata[:8])       self.item_pointers=[]
self.items=[]      for i in range(8,self.lower,4):           rawItemIdData = self.rawdata[i:i+4]
ItemIdData_I32= struct.unpack('L',rawItemIdData)[0]           if not ItemIdData_I32: break           lp_len =
int(ItemIdData_I32>> 17)           lp_flags = int((ItemIdData_I32 >> 15) & 3)           lp_off = int(ItemIdData_I32 &
0x7fff)
           self.item_pointers.append(hex(ItemIdData_I32),lp_off,lp_flags,lp_len)           rawItemData =
self.rawdata[lp_off:lp_off+lp_len]          t_oid  = struct.unpack('L', rawItemData[ 0: 4])[0]           t_001  =
struct.unpack('L',rawItemData[ 4: 8])[0]           t_002  = struct.unpack('L', rawItemData[ 8:12])[0]           t_xmin
=struct.unpack('L', rawItemData[12:16])[0]           t_xmax = struct.unpack('L', rawItemData[16:20])[0]
t_ctid= struct.unpack('LH', rawItemData[20:26])             t_fcnt = struct.unpack('H', rawItemData[26:28])[0]
t_xxx  = struct.unpack('2B', rawItemData[28:30])             t_doff = struct.unpack('B', rawItemData[30:31])[0]
 t_mask = strbits(rawItemData[31:t_doff],t_fcnt)   
 
#            t_mask = rawItemData[31:t_doff]           t_data = rawItemData[t_doff:]
self.items.append(t_oid,t_001,t_002,t_xmin,t_xmax,t_ctid,t_fcnt,t_xxx,t_doff,t_mask,t_data)  def __str__(self):
strval= [str((self.lower, self.upper, self.special, self.opaque))]
strval.append(string.join(map(str,self.item_pointers),'\n'))      strval.append(string.join(map(repr,self.items),'\n'))
     return string.join(strval,'\n------------------\n')  
 


if __name__=="__main__":   print '# dumping %s' % table_path   fd = open(table_name)   page = table_page(fd,0)   print
page
#!/usr/bin/python

import sys,os,struct,string

table_name = "sendcard"

page_size = 8*1024

def strbits(s,len):   bits = []   while s:       c = s[0]       s = s[1:]       b = struct.unpack('B',c)[0]       for i
inrange(8):           if b & (1<<i): bits.append(1)           else: bits.append(0)   bits =
string.join(map(str,bits[:len]),'')   return bits
 

"""
rapos=> \d sendcard
Table    = sendcard
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| card_id                          | int4 default nextval ( 'card_id_ |     4 |
| card_name                        | text                             |   var |
| recipient_name                   | text                             |   var |
| recipient_street_name            | text                             |   var |
| recipient_house_nr               | text                             |   var |
| recipient_apartment_nr           | text                             |   var |
| recipient_city                   | text                             |   var |
| recipient_village                | text                             |   var |
| recipient_state                  | text                             |   var |
| recipient_zip_code               | text                             |   var |
| sender_name                      | text                             |   var |
| sender_email                     | text                             |   var |
| message                          | text                             |   var |
| bank_ref_id                      | text                             |   var |
| delivery_nr                      | text                             |   var |
| delivery_route                   | text                             |   var |
| sender_remote_addr               | text                             |   var |
| card_cost                        | float8                           |     8 |
| cookie_id                        | int4 default nextval ( 'cookie_i |     4 |
| is_payed                         | text                             |   var |
| printing_date                    | datetime                         |     8 |
| delivery_date                    | date                             |     4 |
| payment_date                     | date                             |     4 |
| entered_at                       | timestamp default now ( )        |     4 |
| send_bill_to                     | text                             |   var |
+----------------------------------+----------------------------------+-------+
"""
create_statement = """\
create table savecard(    u_oid                  int,   u_xmin                 int,   u_xmax                 int,
card_id               int4,   card_name              text,   recipient_name         text,   recipient_street_name
text,  recipient_house_nr     text,   recipient_apartment_nr text,   recipient_city         text,   recipient_village
  text,   recipient_state        text,   recipient_zip_code     text,   sender_name            text,   sender_email
     text,   message                text,   bank_ref_id            text,   delivery_nr            text,
delivery_route        text,   sender_remote_addr     text 
 
);
copy savecard from stdin;
"""  
def bin2text(buf,offset):   offset = ((offset + 3)/4)*4   len = int(struct.unpack('L',buf[offset:offset+4])[0])   str =
buf[offset+4:offset+len]  newoff = offset+len   return str,newoff  
 
class table_page:   "class to represent a database table page"   def __init__(self,fd,page_nr):
fd.seek(page_nr*page_size)      self.rawdata = fd.read(page_size)       self.lower,\       self.upper,\
self.special,\      self.opaque = struct.unpack('HHHH',self.rawdata[:8])       self.item_pointers=[]
self.items=[]      for i in range(8,self.lower,4):           rawItemIdData = self.rawdata[i:i+4]
ItemIdData_I32= struct.unpack('L',rawItemIdData)[0]           if not ItemIdData_I32: break           lp_len =
int(ItemIdData_I32>> 17)           lp_flags = int((ItemIdData_I32 >> 15) & 3)           lp_off = int(ItemIdData_I32 &
0x7fff)
           self.item_pointers.append(hex(ItemIdData_I32),lp_off,lp_flags,lp_len)           rawItemData =
self.rawdata[lp_off:lp_off+lp_len]          t_oid  = struct.unpack('L', rawItemData[ 0: 4])[0]           t_001  =
struct.unpack('L',rawItemData[ 4: 8])[0]           t_002  = struct.unpack('L', rawItemData[ 8:12])[0]           t_xmin
=struct.unpack('L', rawItemData[12:16])[0]           t_xmax = struct.unpack('L', rawItemData[16:20])[0]
t_ctid= struct.unpack('LH', rawItemData[20:26])             t_fcnt = struct.unpack('H', rawItemData[26:28])[0]
t_xxx  = struct.unpack('2B', rawItemData[28:30])             t_doff = struct.unpack('B', rawItemData[30:31])[0]
 t_mask = strbits(rawItemData[31:t_doff],t_fcnt)              t_data = rawItemData[t_doff:]           id =
int(struct.unpack('L',t_data[:4])[0])          values = [int(t_oid),int(t_xmin),int(t_xmax),id]           texts = []
      offset = 4           for c in t_mask[1:17]:               if not int(c):                   values.append('')
            continue               text,offset = bin2text(t_data,offset)               values.append(repr(text))
  values=values+texts           self.items.append(values)
 
#            self.items.append(t_oid,t_001,t_002,t_xmin,t_xmax,t_ctid,t_fcnt,t_xxx,t_doff,t_mask,t_data)   def
__str__(self):
#        strval = [str((self.lower, self.upper, self.special, self.opaque))]
#        strval.append(string.join(map(str,self.item_pointers),'\n'))
#        strval.append(string.join(map(repr,self.items),'\n'))
#        return string.join(strval,'\n------------------\n')         rows = []       for record in self.items:
rows.append(string.join(map(str,record),'\t'))       return string.join(rows,'\n')
 

if __name__=="__main__":
#    print '# dumping %s' % table_name   print create_statement   fd = open(table_name)    page_nr = 0   while 1:
  try:              page = table_page(fd,page_nr)           print page       except:           break       page_nr =
page_nr+ 1   print '\\.\n'