Thread: pg recovery
Hi folks I have a broken database and not much clue about postgres, sorry for the n00b questions :( I have my old raid drive which contains the databases but backup did not work, so we don't have one ... looking at the old database directory I get this: root@collab:/var/lib/postgresql/8.1/main# /usr/lib/postgresql/8.1/bin/pg_controldata . WARNING: Calculated CRC checksum does not match value stored in file. Either the file is corrupt, or it has a different layout than this program is expecting. The results below are untrustworthy. pg_control version number: 812 Catalog version number: 200510211 Database system identifier: 5005219046698704127 Database cluster state: shut down pg_control last modified: Thu 26 Dec 13946 17:27:28 GMT Current log file ID: 0 Next log file segment: 1474589828 Latest checkpoint location: 0/57E47440 Prior checkpoint location: 0/57E47484 Latest checkpoint's REDO location: 0/0 Latest checkpoint's UNDO location: 1/231175 Latest checkpoint's TimeLineID: 177739 Latest checkpoint's NextXID: 1 Latest checkpoint's NextOID: 0 Latest checkpoint's NextMultiXactId: 1197559863 Latest checkpoint's NextMultiOffset: 4 Time of latest checkpoint: Thu 11 Jan 1116953 02:46:31 GMT Maximum data alignment: 131072 Database block size: 1 Blocks per segment of large relation: 128 Bytes per WAL segment: 1230990949 Maximum length of identifiers: 1414868549 Maximum columns in an index: 3681606 Date/time type storage: floating-point numbers Maximum length of locale name: 0 LC_COLLATE: LC_CTYPE: if i run postgres@collab:~/8.1/main$ /usr/lib/postgresql/8.1/bin/pg_resetxlog -n . pg_resetxlog: pg_control exists but has invalid CRC; proceed with caution Guessed pg_control values: pg_control version number: 812 Catalog version number: 200510211 Database system identifier: 5005219046698704127 Current log file ID: 0 Next log file segment: 1474589828 Latest checkpoint's TimeLineID: 177739 Latest checkpoint's NextXID: 1 Latest checkpoint's NextOID: 0 Latest checkpoint's NextMultiXactId: 1197559863 Latest checkpoint's NextMultiOffset: 4 Maximum data alignment: 131072 Database block size: 1 Blocks per segment of large relation: 128 Maximum length of identifiers: 1414868549 Maximum columns in an index: 3681606 Date/time type storage: floating-point numbers Maximum length of locale name: 0 LC_COLLATE: LC_CTYPE: I end up with no locale and hence understandably a database server that won't start ... I have not yet found a way to set the locale and ctype. hex editor maybe? any help is appreciated have a good new year! thanks Bernhard -- Graylion's Fetish & Fashion Store Goth and Kinky Boots, Clothing and Jewellery http://www.graylion.net
Bernhard D Rohrer <graylion@sm-wg.net> writes:
> I have a broken database and not much clue about postgres, sorry for the
> n00b questions :(
I think you've got a cross-version problem, as in the database is really
PG 8.0 or earlier but you're trying to run 8.1 against it. What is in
the PG_VERSION file? Have you done "pg_resetxlog -f", and if so do you
have the original pg_control file to put back?
regards, tom lane
Tom Lane wrote: > Bernhard D Rohrer <graylion@sm-wg.net> writes: >> I have a broken database and not much clue about postgres, sorry for the >> n00b questions :( > > I think you've got a cross-version problem, as in the database is really > PG 8.0 or earlier but you're trying to run 8.1 against it. What is in > the PG_VERSION file? Have you done "pg_resetxlog -f", and if so do you > have the original pg_control file to put back? > > regards, tom lane Hi Tom I have not run "pg_resetxlog -f" on the pg_control file for which I posted the output of pg_resetxlog -n as for the versions see for yourself: root@collab:/home/adminlion# cat /var/lib/postgresql/8.1/main/PG_VERSION 8.1 root@collab:/home/adminlion# cat /olddrive/var/lib/postgresql/8.1/main/PG_VERSION 8.1 /olddrive is the former raid drive that holds the database I am trying to restore. thanks :) Bernhard -- Graylion's Fetish & Fashion Store Goth and Kinky Boots, Clothing and Jewellery http://www.graylion.net
Bernhard D Rohrer <graylion@sm-wg.net> writes:
> Tom Lane wrote:
>> I think you've got a cross-version problem, as in the database is really
>> PG 8.0 or earlier but you're trying to run 8.1 against it. What is in
>> the PG_VERSION file? Have you done "pg_resetxlog -f", and if so do you
>> have the original pg_control file to put back?
> as for the versions see for yourself:
> root@collab:/home/adminlion# cat /var/lib/postgresql/8.1/main/PG_VERSION
> 8.1
> root@collab:/home/adminlion# cat
> /olddrive/var/lib/postgresql/8.1/main/PG_VERSION
> 8.1
Hmmm ... but it sure looks like the values are offset a few fields from
where they belong ... [ meditates awhile... ] Ah, I've sussed it: the
pg_controldata output you showed can be explained exactly by the
assumption that this copy of pg_controldata thinks time_t is 64 bits
wide, where the pg_control file actually has 32-bit-wide time_t fields.
That explains both the ridiculously large dates (quite impossible for
32-bit time_t's) and the offsetting of the following fields.
So the short answer is probably that you're trying to use a 64-bit build
of Postgres against a 32-bit database. You need to get a matching build.
(We really need to stop using time_t in pg_control.h ...)
regards, tom lane
Tom Lane wrote: > Hmmm ... but it sure looks like the values are offset a few fields from > where they belong ... [ meditates awhile... ] Ah, I've sussed it: the > pg_controldata output you showed can be explained exactly by the > assumption that this copy of pg_controldata thinks time_t is 64 bits > wide, where the pg_control file actually has 32-bit-wide time_t fields. > That explains both the ridiculously large dates (quite impossible for > 32-bit time_t's) and the offsetting of the following fields. > > So the short answer is probably that you're trying to use a 64-bit build > of Postgres against a 32-bit database. You need to get a matching build. > > (We really need to stop using time_t in pg_control.h ...) > > regards, tom lane exactly - I am currently installing a 32bit dapper on a VM in order to do the migration thanks muchly :) Bernhard -- Graylion's Fetish & Fashion Store Goth and Kinky Boots, Clothing and Jewellery http://www.graylion.net
Bernhard D Rohrer wrote: > Tom Lane wrote: > > exactly - I am currently installing a 32bit dapper on a VM in order to > do the migration > > thanks muchly :) > > Bernhard > ok, done. worked like a charm. thanks! Bernhard -- Graylion's Fetish & Fashion Store Goth and Kinky Boots, Clothing and Jewellery http://www.graylion.net