Re: backups

On Wed, 30 Jun 2004 22:32:26 -0500, bruno(at)wolff(dot)to (Bruno Wolff III)
wrote:

>On Wed, Jun 30, 2004 at 18:23:08 -0500,
> wespvp(at)syntegra(dot)com wrote:
>>
>> What do other sites with mondo databases do?
>
>There have been comments from people using storage systems that they
>can freeze the storage system and get a consistant snap shot of the
>file system. This can be used to do a restore. It will look just like
>postgres crashed when coming back up.
>If you find one of the posts about this in the archives the poster may
>have more details on their storage systems.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend

I've been playing around with something like that.
On my test server I have put the postgresql directory (including the
config files) onto a software raid-1 array. This array starts off as
just one disk, but when the time comes to create a backup, you can add
a secondary disk to the array, on-the-fly, so the database does not
have to stop for this. The recovery-synchronosing of the disk consumes
a few % of the CPU, but nothing too bad (it's disk-to-disk copying)

When syncing is complete I shutdown the database, remove the secondary
disk from the array and start the database up again. Ofcourse this is
in a test environment so this operation takes a few seconds, I have
yet to test what this will do with a normal production load.

Now the secondary disk is an exact copy of the datafiles as they were
when the database was offline, and because it is software-raid, the
secondary disk can now be mounted and backed-up. And because the files
were in an offline state at backup, they can be restored without the
database server having to recover at startup.

It seems to work ok in the test, but ofcourse this has to be tested on
a much much larger scale.