Thread: pg_log deleted - what to do?

pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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





Re: pg_log deleted - what to do?

From
Peter Eisentraut
Date:
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/


Re: pg_log deleted - what to do?

From
Bruce Momjian
Date:
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

Re: pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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
>


Re: pg_log deleted - what to do?

From
Peter Eisentraut
Date:
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/


Re: pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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/
>


Re: pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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/
>


Re: pg_log deleted - what to do?

From
Bruce Momjian
Date:
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

Re: pg_log deleted - what to do?

From
Bruce Momjian
Date:
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

Re: pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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
>


Re: pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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


Re: pg_log deleted - what to do?

From
Ralf Tschiersch
Date:
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