Обсуждение: incremental backups?

My boss was asking about incremental backups.

I was scratching my head, thinking that the transaction log and a backup
policy (script) for each record set (sorry about the archaic terminology)
was the usual solution. But there is a some resistance against writing
more code, so I'm wondering what the current state of affairs with
postgresql in regards to incremental backup would be.

A quick search of the lists produced the following:

Bruce talks in November 2002 about plans for point-in-time recovery in v.
7.4, but last December says it isn't there yet.

Jan mentions Slony-I replication back last January.

Somebody threw in some chatter about XLog.

Scott metioned briefly last August the possibility of combining a live
data server with an archive server, or of using a daily schema.

What's the typical user doing for incrementals, besides going to a
commercial server?

--
Joel Matthew <rees@ddcom.co.jp>

Slony version 1 is supposed to be live very soon.  You can test beta3 if
you like.

Perhaps pgpool could help you.  Version 2 was just released.


On Tue, 2004-06-22 at 22:28, Joel Matthew wrote:
> My boss was asking about incremental backups.
>
> I was scratching my head, thinking that the transaction log and a backup
> policy (script) for each record set (sorry about the archaic terminology)
> was the usual solution. But there is a some resistance against writing
> more code, so I'm wondering what the current state of affairs with
> postgresql in regards to incremental backup would be.
>
> A quick search of the lists produced the following:
>
> Bruce talks in November 2002 about plans for point-in-time recovery in v.
> 7.4, but last December says it isn't there yet.
>
> Jan mentions Slony-I replication back last January.
>
> Somebody threw in some chatter about XLog.
>
> Scott metioned briefly last August the possibility of combining a live
> data server with an archive server, or of using a daily schema.
>
> What's the typical user doing for incrementals, besides going to a
> commercial server?

On 6/22/2004 11:51 PM, mike g wrote:

> Slony version 1 is supposed to be live very soon.  You can test beta3 if
> you like.

Slony-I version 1.0 is out now. It does not contain incremental backup.
This feature is on the TODO list for 1.1.


Jan

>
> Perhaps pgpool could help you.  Version 2 was just released.
>
>
> On Tue, 2004-06-22 at 22:28, Joel Matthew wrote:
>> My boss was asking about incremental backups.
>>
>> I was scratching my head, thinking that the transaction log and a backup
>> policy (script) for each record set (sorry about the archaic terminology)
>> was the usual solution. But there is a some resistance against writing
>> more code, so I'm wondering what the current state of affairs with
>> postgresql in regards to incremental backup would be.
>>
>> A quick search of the lists produced the following:
>>
>> Bruce talks in November 2002 about plans for point-in-time recovery in v.
>> 7.4, but last December says it isn't there yet.
>>
>> Jan mentions Slony-I replication back last January.
>>
>> Somebody threw in some chatter about XLog.
>>
>> Scott metioned briefly last August the possibility of combining a live
>> data server with an archive server, or of using a daily schema.
>>
>> What's the typical user doing for incrementals, besides going to a
>> commercial server?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

El Vie 02 Jul 2004 18:39, Jan Wieck escribió:
> On 6/22/2004 11:51 PM, mike g wrote:
> > Slony version 1 is supposed to be live very soon.  You can test beta3 if
> > you like.
>
> Slony-I version 1.0 is out now. It does not contain incremental backup.
> This feature is on the TODO list for 1.1.

I'm very interested in this.
How is it that a replication system would give the ability of doing
incremental backups?
How would these incremental backups compare to Informix's level backups (level
0 is a full backup, 1 are the differences from the last 0, and 2 are the
differences from the last level 1 BU)?

--
 10:05:02 up 1 day,  1:37,  1 user,  load average: 1.17, 0.93, 0.71
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador
             Universidad Nacional
                  del Litoral
-----------------------------------------------------------------

After takin a swig o' Arrakan spice grog, martin@bugs.unl.edu.ar (Martin Marques) belched out:
> El Vie 02 Jul 2004 18:39, Jan Wieck escribió:
>> On 6/22/2004 11:51 PM, mike g wrote:
>> > Slony version 1 is supposed to be live very soon.  You can test beta3 if
>> > you like.
>>
>> Slony-I version 1.0 is out now. It does not contain incremental backup.
>> This feature is on the TODO list for 1.1.
>
> I'm very interested in this.
> How is it that a replication system would give the ability of doing
> incremental backups?

The idea would be that you take a pg_dump at a point in time, which
provides, if you will, a "baseline."

You then take the series of logs containing Slony-I updates, which,
themselves, are a set of SQL Insert/Update/Delete statements.  They
represent incremental updates.

The clever part was noticing that it would be useful to record those
updates in text form _as incremental SQL logs_.

> How would these incremental backups compare to Informix's level
> backups (level 0 is a full backup, 1 are the differences from the
> last 0, and 2 are the differences from the last level 1 BU)?

Well, supposing you take a pg_dump from a particular node starting at
time T; that's a "level 0" backup.

Slony-I then has a series of (say) 42 log files each dated after time
T, and going to time T + n.  Together, they represent the "level 1"
differences between the "level 0" backup at time T and the present.

I suppose that the 42nd one might be considered to represent a "level
42" backup, as it depends on the preceding 41 logs as well as that
"level 0" backup.

It would be unsurprising for there to be hundreds, if not thousands of
such files per day...
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://www.ntlug.org/~cbbrowne/internet.html
Who's afraid of the garbage collector?

On 7/3/2004 9:11 AM, Martin Marques wrote:

> El Vie 02 Jul 2004 18:39, Jan Wieck escribió:
>> On 6/22/2004 11:51 PM, mike g wrote:
>> > Slony version 1 is supposed to be live very soon.  You can test beta3 if
>> > you like.
>>
>> Slony-I version 1.0 is out now. It does not contain incremental backup.
>> This feature is on the TODO list for 1.1.
>
> I'm very interested in this.
> How is it that a replication system would give the ability of doing
> incremental backups?

The information Slony collects with triggers basically consists of
INSERT, UPDATE and DELETE statements that cause exactly the same changes
on a slave, that have been performed on the master. If it is possible to
extract a consistent data snapshot and to know exactly what actions are
included in that, and which need to be redone after ...

There isn't much of a difference between applying the changes to a
database and writing SQL statements into files.

> How would these incremental backups compare to Informix's level backups (level
> 0 is a full backup, 1 are the differences from the last 0, and 2 are the
> differences from the last level 1 BU)?

I wonder how informix does that sort of level backup. It sounds very
much like a filesystem dump, but on a database level I can't really
imagine this. Especially with an MVCC database like PostgreSQL.

There will be no levels. Slony will provide a dump, and then
incrementals. One advantage will be that the incrementals are just plain
SQL scripts containing INSERT, UPDATE and DELETE operations. One usually
does PITR because a misbehaving application corrupted the data from a
logical point of view. Finding the offending statement with grep in
those files will ultimately lead to the exact point in time to wich the
DB can be rolled forward. Plus, it will be easy to analyze what had been
done after that point, based on the wrong data.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #