Thread: Please help recover data - lost pg_control

Hey all - my pg_control file got hosed somehow on a 7.1b3 installation.
All my data files other than pg_control are intact.  A search of the net
reveals that the pg_resetxlog utility can be helpful in this situation.
However, my installation does not have the pg_resetxlog utility in the
contrib directory.

So, I grabbed the 7.1.2 sources, built the utility in the contrib
directory, and ran it.  It seemed to run fine, but when I start
postmaster now, I get:

  [vici:/usr/local/pgsql] postmaster
  FATAL 2:  Invalid CRC in control file

Next, I built and installed 7.1.2 over my 7.1b3 (I still have the 7.1b3
sources just in case).  Now postmaster seems to start fine.  But when I
try to do a pg_dumpall, I get:

  [root@vici:/usr/local/pgsql] pg_dumpall
  --
  -- pg_dumpall (7.1.2)
  --
  \connect template1
  DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM
pg_database WHERE datname = 'template0');

  ERROR:  readDatum: ']' expected, length = 12

  DELETE FROM pg_group;


  --
  -- Database template1
  --
  \connect template1 postgres
  \connect template1 postgres
  getDatabase(): SELECT failed.  Explanation from backend: 'ERROR:
readDatum: ']' expected, length = 12
  '.
  pg_dump failed on template1, exiting

When I run 'psql demo' at this point, I can issue commands like 'select
* from person' and it gives me the header with the list of columns, but
no data (0 rows).  Attempting to describe a table results in an error
much like the above.

Seems to me the pg_resetxlog utility does not generate a pg_control file
compatible with the 7.1b3 data file format, thus the CRC errors.  It
further appears that 7.1.2 is expecting a different format as well.

I know all the data is there - it must be possible to extract it
/somehow/.

I would be _extremely_ grateful for any assistance.

Thanks in advance.

Alok

"Alok K. Dhir" <alok@dhir.net> writes:
>   ERROR:  readDatum: ']' expected, length = 12

Hm, are you sure that was 7.1b3, and not something even older?  Looking
at the CVS logs, the last change in stored rule format was between beta1
and beta2, so I'd expect this sort of failure if you tried to run 7.1
release against a 7.1b1 database (noting that you clobbered the normal
DB version crosscheck by running pg_resetxlog).

You might be able to build a hacked version of 7.1 release that will
read your 7.1b1 database by undoing the CONST node dump format change of
2001-01-07 --- see rev 1.103 at
http://www.ca.postgresql.org/cgi/cvsweb.cgi/pgsql/src/backend/nodes/readfuncs.c
but note that most of the diffs don't need to be undone, you just need
to change back the order of the fields that _readConst is expecting.

I'm not sure that's the only thing that'll bite you, but it's worth a
try.  If you get a clean dump, initdb with a non-hacked copy of 7.1.2
before restoring.

            regards, tom lane