Обсуждение: Recovering data from an old disk image
Greetings, I need to recover some data from a disk image that was attached to a virtual machine that has since been deleted. I'm 99.9% sure that postgres was stopped normally before this image was saved. Everything was supposed to have been migrated as part of an upgrade, but it seems that at least one of the original tables didn't get moved. I've been asked to recover it. It was apparently decided that there was no reason to save the output of the pg_dump that was used for the upgrade, since they saved the original pgsql data directory. I've copied the entire pgsql directory to a new machine & installed the most recent 9.3 to try to read it. When I start the server it tells me that the database was initialized by version 9.4, so it can't handle it. I uninstalled 9.3 & installed the most recent 9.4. When I try to start it, it tells me: postgres[99770]: [1-1] FATAL: database files are incompatible with server postgres[99770]: [1-2] DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 937, but the server was compiled with PG_CONTROL_VERSION 942. Based on a search of the mailing list archives I'm guessing that the original postgresql server was a 9.4 beta. I'd greatly appreciate any help in recovering this database. If anyone can tell me how to find the postgresql source to the 9.4 version that used PG_CONTROL_VERSION 937 that would be wonderful. If there's a simpler way to get at the data that would be even better. Any and all help would be greatly appreciated. Thanks in advance. -- Richard Kuhns <rjk@wintek.com> Wintek Corporation 427 N 6th Street Lafayette, IN 47901-2211 Main: 765-742-8428 Direct: 765-269-8541
On 7/15/16 11:37 AM, Richard Kuhns wrote: > I've copied the entire pgsql directory to a new machine & installed the > most recent 9.3 to try to read it. When I start the server it tells me > that the database was initialized by version 9.4, so it can't handle it. > > I uninstalled 9.3 & installed the most recent 9.4. When I try to start > it, it tells me: > > postgres[99770]: [1-1] FATAL: database files are incompatible with server > postgres[99770]: [1-2] DETAIL: The database cluster was initialized > with PG_CONTROL_VERSION 937, but the server was compiled with > PG_CONTROL_VERSION 942. > > Based on a search of the mailing list archives I'm guessing that the > original postgresql server was a 9.4 beta. Yeah, based on that it looks like it might have been 9.4beta1. If you can't find a tarball for that anywhere anymore, you can check out the tag REL9_4_BETA1 from git and built it yourself. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 07/15/2016 08:37 AM, Richard Kuhns wrote: > Greetings, > > I need to recover some data from a disk image that was attached to a > virtual machine that has since been deleted. > > I'm 99.9% sure that postgres was stopped normally before this image was > saved. Everything was supposed to have been migrated as part of an > upgrade, but it seems that at least one of the original tables didn't > get moved. I've been asked to recover it. > > It was apparently decided that there was no reason to save the output of > the pg_dump that was used for the upgrade, since they saved the original > pgsql data directory. > > I've copied the entire pgsql directory to a new machine & installed the > most recent 9.3 to try to read it. When I start the server it tells me > that the database was initialized by version 9.4, so it can't handle it. > > I uninstalled 9.3 & installed the most recent 9.4. When I try to start > it, it tells me: > > postgres[99770]: [1-1] FATAL: database files are incompatible with server > postgres[99770]: [1-2] DETAIL: The database cluster was initialized > with PG_CONTROL_VERSION 937, but the server was compiled with > PG_CONTROL_VERSION 942. > > Based on a search of the mailing list archives I'm guessing that the > original postgresql server was a 9.4 beta. > > I'd greatly appreciate any help in recovering this database. If anyone > can tell me how to find the postgresql source to the 9.4 version that > used PG_CONTROL_VERSION 937 that would be wonderful. If there's a > simpler way to get at the data that would be even better. https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/include/catalog/pg_control.h;h=05c5b748cb9a780d2f938cc1ca1d8ae4d7df1bbb;hb=e6df2e1be6330660ba4d81daa726ae4a71535aa9 So: REL9_4_BETA1 https://git.postgresql.org/gitweb/?p=postgresql.git;a=tags > > Any and all help would be greatly appreciated. > > Thanks in advance. > > -- > Richard Kuhns <rjk@wintek.com> > Wintek Corporation > 427 N 6th Street > Lafayette, IN 47901-2211 > > Main: 765-742-8428 > Direct: 765-269-8541 > > -- Adrian Klaver adrian.klaver@aklaver.com
Richard Kuhns <rjk@wintek.com> writes: > I uninstalled 9.3 & installed the most recent 9.4. When I try to start > it, it tells me: > postgres[99770]: [1-1] FATAL: database files are incompatible with server > postgres[99770]: [1-2] DETAIL: The database cluster was initialized > with PG_CONTROL_VERSION 937, but the server was compiled with > PG_CONTROL_VERSION 942. > Based on a search of the mailing list archives I'm guessing that the > original postgresql server was a 9.4 beta. [ digs in commit logs... ] Assuming that this actually was a release of some sort, and not just a random git snapshot, it would have to have been 9.4beta1. Betas later than that one used the newer PG_CONTROL_VERSION value. The catalog_version_no would provide a finer-grain dating, but trying beta1 seems like a good bet. I'm not sure if there are still tarballs of 9.4beta1 on our webservers, but in any case you could easily check out that tag from our git server to recover the matching source code. regards, tom lane
On 07/15/2016 09:06 AM, Tom Lane wrote: > Richard Kuhns <rjk@wintek.com> writes: >> I uninstalled 9.3 & installed the most recent 9.4. When I try to start >> it, it tells me: > >> postgres[99770]: [1-1] FATAL: database files are incompatible with server >> postgres[99770]: [1-2] DETAIL: The database cluster was initialized >> with PG_CONTROL_VERSION 937, but the server was compiled with >> PG_CONTROL_VERSION 942. > >> Based on a search of the mailing list archives I'm guessing that the >> original postgresql server was a 9.4 beta. > > [ digs in commit logs... ] Assuming that this actually was a release of > some sort, and not just a random git snapshot, it would have to have been > 9.4beta1. Betas later than that one used the newer PG_CONTROL_VERSION > value. The catalog_version_no would provide a finer-grain dating, but > trying beta1 seems like a good bet. > > I'm not sure if there are still tarballs of 9.4beta1 on our webservers, > but in any case you could easily check out that tag from our git server > to recover the matching source code. If you go here: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e6df2e1be6330660ba4d81daa726ae4a71535aa9 would not fetching the snapshot also work? > > regards, tom lane > > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/15/16 12:13, Adrian Klaver wrote: > On 07/15/2016 09:06 AM, Tom Lane wrote: >> Richard Kuhns <rjk@wintek.com> writes: >>> I uninstalled 9.3 & installed the most recent 9.4. When I try to start >>> it, it tells me: >> >>> postgres[99770]: [1-1] FATAL: database files are incompatible with >>> server >>> postgres[99770]: [1-2] DETAIL: The database cluster was initialized >>> with PG_CONTROL_VERSION 937, but the server was compiled with >>> PG_CONTROL_VERSION 942. >> >>> Based on a search of the mailing list archives I'm guessing that the >>> original postgresql server was a 9.4 beta. >> >> [ digs in commit logs... ] Assuming that this actually was a release of >> some sort, and not just a random git snapshot, it would have to have been >> 9.4beta1. Betas later than that one used the newer PG_CONTROL_VERSION >> value. The catalog_version_no would provide a finer-grain dating, but >> trying beta1 seems like a good bet. >> >> I'm not sure if there are still tarballs of 9.4beta1 on our webservers, >> but in any case you could easily check out that tag from our git server >> to recover the matching source code. > > If you go here: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e6df2e1be6330660ba4d81daa726ae4a71535aa9 > > > would not fetching the snapshot also work? > >> >> regards, tom lane >> >> Thank you all! Fetching the snapshot worked just fine, and I have the missing table. I'm also dumping the entire database so I don't have to worry about it again. Thanks again, - Richard -- Richard Kuhns <rjk@wintek.com> Wintek Corporation 427 N 6th Street Lafayette, IN 47901-2211 Main: 765-742-8428 Direct: 765-269-8541