Re: Hot backup

On Tue, Jun 01, 2004 at 10:55:26 -0400,
Krishna R Palati <krishna(at)hedgehoghosting(dot)com> wrote:
> Hello,
>
> Thanks for the reply. Pg_dump might provide consistent dump at that point of
> time. But my question is:
>
> We can setup pg_dump everyday at 3AM. Suppose a crash occurs at 10PM. Is
> there a way in Postgres for us to use the dump file from 3AM and the
> transaction logs since then so that we can do a point in time recovery (to
> recover everything till 10PM) ?

If you are talking about a system crash, you won't lose any committed
transactions and you don't need to reload from a dump at all. Just start
the server back up and it will recover.

If you are talking a head crash (or multiple ones taking out a raid set),
then you can only recover from the last dump, since the data that might
be used to roll forward from a dump will be lost.

If you are talking about going back to before an erroneous transaction ruined
the database, you can't do that now without going back to the last dump
or trying to fix the live version with new transactions. The next version
may have point in time recovery which would allow you to roll forward from
a dump to a particular time. We won't know for about a month if this
feature will make it in. The next version will be released sometime this fall.