Hot backup

[Bruno Wolff III]

On Wed, May 26, 2004 at 10:50:21 -0400,

I read online that Postgres 7.4.1 has the capability to do hotbackups
(equivalent to Hotbackups on Oracle). But am not able to find any
documentation which will help me set up one. Can you please point me to a
resource that I can use to set up hotbackups for Postgres 7.4.1 ?

pg_dump and pg_dumpall provide consistant dumps of the database.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Krishna R Palati]

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) ?

With pg_dump as I understand, we can recover only from the dump made at 3AM.
So we will be losing every transaction on the db which is done between
pg_dumps.

Thanks again for your time.
-Krishna.




-----Original Message-----
From: Bruno Wolff III [mailto:***@wolff.to]
Sent: Monday, May 31, 2004 5:15 AM
To: Krishna R Palati
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Hot backup

On Wed, May 26, 2004 at 10:50:21 -0400,

I read online that Postgres 7.4.1 has the capability to do hotbackups
(equivalent to Hotbackups on Oracle). But am not able to find any
documentation which will help me set up one. Can you please point me to a
resource that I can use to set up hotbackups for Postgres 7.4.1 ?

pg_dump and pg_dumpall provide consistant dumps of the database.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Tue, Jun 01, 2004 at 10:55:26 -0400,

Post by Krishna R Palati
Hello,
Thanks for the reply. Pg_dump might provide consistent dump at that point of
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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Krishna R Palati]

I was talking about a scenario where we lose the datafiles due to various
issues. Your reply answers my question. Thank you very much for your time.
I really appreciate that.
-Krishna.

-----Original Message-----
From: Bruno Wolff III [mailto:***@wolff.to]
Sent: Wednesday, June 02, 2004 2:14 PM
To: Krishna R Palati
Cc: pgsql-***@postgresql.org
Subject: Re: [ADMIN] Hot backup

On Tue, Jun 01, 2004 at 10:55:26 -0400,

Post by Krishna R Palati
Hello,
Thanks for the reply. Pg_dump might provide consistent dump at that point of
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.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings