Обсуждение: 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