Обсуждение: pg_log deleted - what to do?
Hello - don't ask why, but what can I do if I have pg_log accidently deleted? How to recover? the postmaster starts, I can connect do databases, but neiter tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. I would prefer urgent help :-/ Thanks Ralf
Ralf Tschiersch writes: > don't ask why, but what can I do if I have pg_log accidently deleted? How > to recover? the postmaster starts, I can connect do databases, but neiter > tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. You have pretty much lost. Deleting pg_log is almost like deleting the data itself. You make backups, don't you? -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Can the user rename the /data directory, do initdb, save the pg_log file, move the old /data back into place, add the new pg_log, and do a backup of his data? I thought this would work, but am not sure. > Ralf Tschiersch writes: > > > don't ask why, but what can I do if I have pg_log accidently deleted? How > > to recover? the postmaster starts, I can connect do databases, but neiter > > tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. > > You have pretty much lost. Deleting pg_log is almost like deleting the > data itself. You make backups, don't you? > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi - I've tried this already, with no success. More details: the "postgres"-database shows: postgres=> \d Couldn't find any tables, sequences or indices! postgres=> select * from pg_database; datname |datdba|encoding|datpath ----------+------+--------+---------- template1 | 26| 0|template1 (1 row) Whew - where are the databases? Connecting a (former) existing database shows: dbx1=> \d Couldn't find any tables, sequences or indices! dbx1=> select * from gp; kid|typ|var|data ---+---+---+---- (0 rows) But the File is there! -rw------- 1 postgres daemon 16384 Sep 30 03:46 data/base/dbx1/gp It's horrible. I know that I had to do a backup, but things change fast! Its seems that Postgres did a "reset" and does not know that there is pretty much it could deliver. Setting up a fres installation with the exact table and properties ist okay, but copying the data/base/dbname-Files into this new place does not the trick. Thanks for your care... Ralf On Sun, 1 Oct 2000, Bruce Momjian wrote: > Can the user rename the /data directory, do initdb, save the pg_log > file, move the old /data back into place, add the new pg_log, and do a > backup of his data? > > I thought this would work, but am not sure. > > > Ralf Tschiersch writes: > > > > > don't ask why, but what can I do if I have pg_log accidently deleted? How > > > to recover? the postmaster starts, I can connect do databases, but neiter > > > tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. > > > > You have pretty much lost. Deleting pg_log is almost like deleting the > > data itself. You make backups, don't you? > > > > -- > > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
Bruce Momjian writes: > Can the user rename the /data directory, do initdb, save the pg_log > file, move the old /data back into place, add the new pg_log, and do a > backup of his data? My understanding is that pg_log contains flags about which transactions have committed, from which is inferred what tuples are valid. So theoretically you could set "all transactions have committed" in pg_log and pick out the tuples you like from the tables (after having gotten past the horribly corrupted indexes). But that seems like a pretty complicated undertaking in any case. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Hi - that's my understanding of pg_log, too. But what does the trick to re-generate the indexes to make the tuples valid? If I do a select on one of the larger tables (>100.000 tuples), all data is being loaded since it take the "usual" moment of accessing the corresponding file. But then, the tuples are not valid in some way, you see. I only remember like a "flash", that the pg_log-file was large. We have no blobs inside the tables. Would'nt it be possible to "scan" the raw table-files an to re-engineer the data? This seems easier to me than fiddling with transaction-logs. The pity is, that the transaction-log also affects the system's tables.... Whatever is possible, time is no problem. Getting the data back is what has to be done.... Ralf PS: "COMMIT *" or what? Bitter joke... or "ROLLBACK 'rm'". On Sun, 1 Oct 2000, Peter Eisentraut wrote: > Bruce Momjian writes: > > > Can the user rename the /data directory, do initdb, save the pg_log > > file, move the old /data back into place, add the new pg_log, and do a > > backup of his data? > > My understanding is that pg_log contains flags about which transactions > have committed, from which is inferred what tuples are valid. So > theoretically you could set "all transactions have committed" in pg_log > and pick out the tuples you like from the tables (after having gotten past > the horribly corrupted indexes). But that seems like a pretty complicated > undertaking in any case. > > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ >
Another idea: I will see if I can modify the source-code in that way, that the Transaction-Check will alway show green light. All I want to have is a clean pg_dump, I don't need updates or so. Once pg_dumped, the ugly patch can be deactivated, and afer an initdb and inserting this latest dump, everything goes fine. Does anybody have an idea which function has to be modified to "fake" the transaction-status only for the aboce action? I'm not one of the C-gurus and just starting to browse through the code, but I think it might work. If anybody laughs, please let me know to save ma time! Answers welcome! Ralf PS: If it works, I promise to set the pg_dump-cronjob to hourly and to spent money on an adequate machine which can handle that load. And to put the postgresql-Logo on one of the corresponding web-pages ;-) On Sun, 1 Oct 2000, Peter Eisentraut wrote: > Bruce Momjian writes: > > > Can the user rename the /data directory, do initdb, save the pg_log > > file, move the old /data back into place, add the new pg_log, and do a > > backup of his data? > > My understanding is that pg_log contains flags about which transactions > have committed, from which is inferred what tuples are valid. So > theoretically you could set "all transactions have committed" in pg_log > and pick out the tuples you like from the tables (after having gotten past > the horribly corrupted indexes). But that seems like a pretty complicated > undertaking in any case. > > > -- > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ >
OK, please try pg_dump or pg_dumpall. The database will not usable, but may be backup-able. > Hi - > > I've tried this already, with no success. More details: > > the "postgres"-database shows: > > postgres=> \d > Couldn't find any tables, sequences or indices! > > postgres=> select * from pg_database; > datname |datdba|encoding|datpath > ----------+------+--------+---------- > template1 | 26| 0|template1 > (1 row) > > Whew - where are the databases? > Connecting a (former) existing database shows: > > dbx1=> \d > Couldn't find any tables, sequences or indices! > dbx1=> select * from gp; > kid|typ|var|data > ---+---+---+---- > (0 rows) > > But the File is there! > > -rw------- 1 postgres daemon 16384 Sep 30 03:46 data/base/dbx1/gp > > It's horrible. I know that I had to do a backup, but things change fast! > Its seems that Postgres did a "reset" and does not know that there is > pretty much it could deliver. Setting up a fres installation with the > exact table and properties ist okay, but copying the > data/base/dbname-Files into this new place does not the trick. > > Thanks for your care... > > Ralf > > > > On Sun, 1 Oct 2000, Bruce Momjian wrote: > > > Can the user rename the /data directory, do initdb, save the pg_log > > file, move the old /data back into place, add the new pg_log, and do a > > backup of his data? > > > > I thought this would work, but am not sure. > > > > > Ralf Tschiersch writes: > > > > > > > don't ask why, but what can I do if I have pg_log accidently deleted? How > > > > to recover? the postmaster starts, I can connect do databases, but neiter > > > > tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. > > > > > > You have pretty much lost. Deleting pg_log is almost like deleting the > > > data itself. You make backups, don't you? > > > > > > -- > > > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > > > > > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Also, I never liked the name pg_log. Though descriptive, it was too tempting a name for administrators to delete. Write-ahead log will not use that file, so it should go away in 7.1. > OK, please try pg_dump or pg_dumpall. The database will not usable, but > may be backup-able. > > > > Hi - > > > > I've tried this already, with no success. More details: > > > > the "postgres"-database shows: > > > > postgres=> \d > > Couldn't find any tables, sequences or indices! > > > > postgres=> select * from pg_database; > > datname |datdba|encoding|datpath > > ----------+------+--------+---------- > > template1 | 26| 0|template1 > > (1 row) > > > > Whew - where are the databases? > > Connecting a (former) existing database shows: > > > > dbx1=> \d > > Couldn't find any tables, sequences or indices! > > dbx1=> select * from gp; > > kid|typ|var|data > > ---+---+---+---- > > (0 rows) > > > > But the File is there! > > > > -rw------- 1 postgres daemon 16384 Sep 30 03:46 data/base/dbx1/gp > > > > It's horrible. I know that I had to do a backup, but things change fast! > > Its seems that Postgres did a "reset" and does not know that there is > > pretty much it could deliver. Setting up a fres installation with the > > exact table and properties ist okay, but copying the > > data/base/dbname-Files into this new place does not the trick. > > > > Thanks for your care... > > > > Ralf > > > > > > > > On Sun, 1 Oct 2000, Bruce Momjian wrote: > > > > > Can the user rename the /data directory, do initdb, save the pg_log > > > file, move the old /data back into place, add the new pg_log, and do a > > > backup of his data? > > > > > > I thought this would work, but am not sure. > > > > > > > Ralf Tschiersch writes: > > > > > > > > > don't ask why, but what can I do if I have pg_log accidently deleted? How > > > > > to recover? the postmaster starts, I can connect do databases, but neiter > > > > > tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. > > > > > > > > You have pretty much lost. Deleting pg_log is almost like deleting the > > > > data itself. You make backups, don't you? > > > > > > > > -- > > > > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > > > > > > > > > > > > > > > > -- > > > Bruce Momjian | http://candle.pha.pa.us > > > pgman@candle.pha.pa.us | (610) 853-3000 > > > + If your life is a hard drive, | 830 Blythe Avenue > > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi - thanks for you answer. pg_dump* brings: nothing. Neither schemes, nor data. Like a direct select, it is not working proper. The backend is the problem. Or is it possible to dump without connecting a server? No, I don't see that... What do you (Bruce) think about modifying the source for the result of "every transaction is okay" for doing one (and only one) pg_dump? I think the effect that with a deleted pg_log no transactin ever seems to be okay, the silly results are "logical". The backend-server thinks that no tuple is "freed" at all... so let's free them altogether "manually". This affects the system-tables, too, and that is the reason why the effect is so silly... I'll let you know and would appreciate some advise on that point Greetings from rainy sunday night in Germany... Ralf On Sun, 1 Oct 2000, Bruce Momjian wrote: > OK, please try pg_dump or pg_dumpall. The database will not usable, but > may be backup-able. > > > > Hi - > > > > I've tried this already, with no success. More details: > > > > the "postgres"-database shows: > > > > postgres=> \d > > Couldn't find any tables, sequences or indices! > > > > postgres=> select * from pg_database; > > datname |datdba|encoding|datpath > > ----------+------+--------+---------- > > template1 | 26| 0|template1 > > (1 row) > > > > Whew - where are the databases? > > Connecting a (former) existing database shows: > > > > dbx1=> \d > > Couldn't find any tables, sequences or indices! > > dbx1=> select * from gp; > > kid|typ|var|data > > ---+---+---+---- > > (0 rows) > > > > But the File is there! > > > > -rw------- 1 postgres daemon 16384 Sep 30 03:46 data/base/dbx1/gp > > > > It's horrible. I know that I had to do a backup, but things change fast! > > Its seems that Postgres did a "reset" and does not know that there is > > pretty much it could deliver. Setting up a fres installation with the > > exact table and properties ist okay, but copying the > > data/base/dbname-Files into this new place does not the trick. > > > > Thanks for your care... > > > > Ralf > > > > > > > > On Sun, 1 Oct 2000, Bruce Momjian wrote: > > > > > Can the user rename the /data directory, do initdb, save the pg_log > > > file, move the old /data back into place, add the new pg_log, and do a > > > backup of his data? > > > > > > I thought this would work, but am not sure. > > > > > > > Ralf Tschiersch writes: > > > > > > > > > don't ask why, but what can I do if I have pg_log accidently deleted? How > > > > > to recover? the postmaster starts, I can connect do databases, but neiter > > > > > tables nor sequences are found! Postgres is 6.5.3 on linux 2.2.10. > > > > > > > > You have pretty much lost. Deleting pg_log is almost like deleting the > > > > data itself. You make backups, don't you? > > > > > > > > -- > > > > Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ > > > > > > > > > > > > > > > > > -- > > > Bruce Momjian | http://candle.pha.pa.us > > > pgman@candle.pha.pa.us | (610) 853-3000 > > > + If your life is a hard drive, | 830 Blythe Avenue > > > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > > > > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >
So far, so bad. I found out that the "pg_class" is only populated with system-tables, but all user-tables have gone, in every db! So another idea is to re-generate the table as "tablename_foo", rename this table's "relname" in pg_class to the original name and... Do I have another chance? Ralf
Okay, I tried the several ways, but there seems to be no way!? Who can help to reengineer a raw table in data/base/dbname/tablename from scratch? Even the descriptions in data/base/dbname/pg_class etc are readable, but postgres server does not recognize them since I deleted the pg_log by accident!! Wo can help? Ralf