What I understood from the Administrator's guide is:

  - Yes, PostgreSQL provides hot backup: it's the pg_dump utility. It'h hot because users can still be connected and work whil pg_dump is running ( though they will be slowed down). ( See Administrator's guide ch9)

 -  No, PostgreSQL does NOT provide a way to restore a database up to the last commited transaction, with a reapply of the WAL, as Oracle or SQL Server ( and others, I guess) do. That would be a VERY good feature. See Administrator's guide ch11

So, with Pg, if you backup your db every night with pg_dump, and your server crashes during the day, you will loose up to one day of work.

Am I true?

Erwan


-------------------------------------------------------------------------------
Erwan DUROSELLE    //    SEAFRANCE DSI 
Responsable Bases de Données  //  Databases Manager
Tel: +33 (0)1 55 31 59 70    //    Fax: +33 (0)1 55 31 85 28
email: eduroselle@seafrance.fr
-------------------------------------------------------------------------------


>>> Neil Conway <neilc@samurai.com> 07/10/2002 19:48 >>>
"Sandeep Chadha" <sandeep@newnetco.com> writes:
> Postgresql has been lacking this all along. I've installed postgres
> 7.3b2 and still don't see any archive's flushed to any other
> place. Please let me know how is hot backup procedure implemented in
> current 7.3 beta(2) release.

AFAIK no such hot backup feature has been implemented for 7.3 -- you
appear to have been misinformed.

That said, I agree that would be a good feature to have.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


Tue, 08 Oct 2002 12:35:22 +0200
"Erwan DUROSELLE" <EDuroselle@seafrance.fr>
=?ISO-8859-1?Q?R=E9p.=20:=20Re:=20=20[HACKERS]=20Hot=20?=
	=?ISO-8859-1?Q?Backup?=
On Tue, Oct 08, 2002 at 12:35:22PM +0200, Erwan DUROSELLE wrote:
> What I understood from the Administrator's guide is:
> 
>   - Yes, PostgreSQL provides hot backup: it's the pg_dump utility. It'h
> hot because users can still be connected and work whil pg_dump is running
> ( though they will be slowed down). ( See Administrator's guide ch9)

Correct.

>  -  No, PostgreSQL does NOT provide a way to restore a database up to the
> last commited transaction, with a reapply of the WAL, as Oracle or SQL
> Server ( and others, I guess) do. That would be a VERY good feature. See
> Administrator's guide ch11

Umm, I thought the whole point of WAL was that if the database crashed, the
WAL would provide the info to replay to the last committed transaction.

http://www.postgresql.org/idocs/index.php?wal.html

... because we know that in the event of a crash we will be able to recover
the database using the log: ...

These docs seem to corrobrate this.

> So, with Pg, if you backup your db every night with pg_dump, and your
> server crashes during the day, you will loose up to one day of work.

I've never lost any data with postgres, even if it's crashed, even without
WAL.

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Tue, 8 Oct 2002 20:45:13 +1000
Martijn van Oosterhout <kleptog@svana.org>
Re: =?iso-8859-1?B?Uulw?=
Martijn!

Tuesday, October 08, 2002, 3:45:13 PM, you wrote:

>>  -  No, PostgreSQL does NOT provide a way to restore a database up to the
>> last commited transaction, with a reapply of the WAL, as Oracle or SQL
>> Server ( and others, I guess) do. That would be a VERY good feature. See
>> Administrator's guide ch11

MvO> Umm, I thought the whole point of WAL was that if the database crashed, the
MvO> WAL would provide the info to replay to the last committed transaction.

MvO> http://www.postgresql.org/idocs/index.php?wal.html

MvO> ... because we know that in the event of a crash we will be able to recover
MvO> the database using the log: ...

MvO> These docs seem to corrobrate this.

>> So, with Pg, if you backup your db every night with pg_dump, and your
>> server crashes during the day, you will loose up to one day of work.

MvO> I've never lost any data with postgres, even if it's crashed, even without
MvO> WAL.

Suppose you made your nightly backup, and then after a day of work
the building where your server is located disappears in flames..

That's it - you lost one day of work (of course, if your dumps where
stored outside that building otherwise you lost everything)..

There is a need in "incremental" backup, which backs up only those
transactions which has been fulfilled after last "full dump" or last
"incremental dump".  These backups should be done quite painlessly -
just copy some part of WAL, and should be small enough (compared to
full dump), so they can be done each hour or even more frequently..

I hope sometime PostgreSQL will support that. :-)

Sincerely Yours,
Timur
mailto:itvthor@sdf.lonestar.org


Tue, 8 Oct 2002 16:00:12 +0500
"Timur V. Irmatov" <itvthor@sdf.lonestar.org>
=?Windows-1251?B?UmVbMl06IFtHRU5FUkFMXSBS6XA=?=