Thread: recovering a control file from a shutdown database....

Somehow in upgrading from 7.1.2 to 7.1.3 (and with much operator error
along the way), my database became corrupted.

I believe it is only the pg_control file that was damaged.

The database was completely shutdown at the time.

Is there anyway to create a new pg_control file, based on the contents of
the database itself?

The symptoms are that the

data/global/1262 file has a file size of 8192 bytes and a time stamp of
last June.
data/global/pg_control has a file size of 16384 bytes and a time stamp of
today.

When the postmaster starts up, it tells me that:

     database was initialized with BLCKSZ 16384,
     but the backend was compiled with BLCKSZ 8192.
     looks like you need an initdb.

Bleh!  I would prefer to not waste my db, and I assume initdb will do just
that.

Can anything be done to recover the pg_control file?

Thanks,

Jerry
========================================================
Jerry Asher                      jerry@theashergroup.com
1678 Shattuck Avenue Suite 161   Tel: (510) 549-2980
Berkeley, CA 94709               Fax: (877) 311-8688

> When the postmaster starts up, it tells me that:
>
>      database was initialized with BLCKSZ 16384,
>      but the backend was compiled with BLCKSZ 8192.
>      looks like you need an initdb.
>

It sounds from that error message like your 7.1.2 version of PostgreSQL was
modified and compiled from source. The default BLCKSZ is 8192, which is what
your 7.1.3 version is using. I think you need to modify the BLCKSZ for 7.1.3
to match that of your database, and recompile, if you want to avoid losing
it.

See <postgres source tree>/src/include/config.h for the section that looks
like the following:
--------------------------------------------------------------
/*
 * Size of a disk block --- this also limits the size of a tuple.
 * You can set it bigger if you need bigger tuples (although TOAST
 * should reduce the need to have large tuples, since fields can now
 * be spread across multiple tuples).
 *
 * The maximum possible value of BLCKSZ is currently 2^15 (32768).
 * This is determined by the 15-bit widths of the lp_off and lp_len
 * fields in ItemIdData (see include/storage/itemid.h).
 *
 * CAUTION: changing BLCKSZ requires an initdb.
 */
#define BLCKSZ 8192
--------------------------------------------------------------

and change
#define BLCKSZ 8192
to
#define BLCKSZ 16384

Then recompile.

Hope this helps,

-- Joe

Jerry Asher <jerry@theashergroup.com> writes:
> Can anything be done to recover the pg_control file?

I've already been talking to Jerry off-list, and I trust he's managed
to recover his data --- but for the archives, the answer to this is to
hack up contrib/pg_resetxlog to force whatever data you need into the
pg_control file.

It is an *extremely* good idea to back up your whole $PGDATA directory
tree (with tar or some such) before attempting such desperate measures,
so that you can undo any self-inflicted damage if you get it wrong...

            regards, tom lane