Implementing incremental backup

Tatsuo,

* Tatsuo Ishii (ishii@postgresql.org) wrote:

I'm thinking of implementing an incremental backup tool for
PostgreSQL. The use case for the tool would be taking a backup of huge
database. For that size of database, pg_dump is too slow, even WAL
archive is too slow/ineffective as well. However even in a TB
database, sometimes actual modified blocks are not that big, may be
even several GB. So if we can backup those modified blocks only,
that would be an effective incremental backup method.

I'm trying to figure out how that's actually different from WAL..? It
sounds like you'd get what you're suggesting with simply increasing the
checkpoint timeout until the WAL stream is something which you can keep
up with. Of course, the downside there is that you'd have to replay
more WAL when recovering.

What about a tool which receives WALs but then "compresses" them across
a longer period of time than the normal checkpointing by simply keeping
in memory the current set of blocks modified and applying each WAL
record against that block in memory as it reads the WAL? It would then
purge that block out using a full-page WAL write at some pre-defined
point, perhaps at the end of the overall backup?

Consider this: connect the WAL-compressor to a PG backend, issue a
'start backup', which the WAL-compressor detects and then starts keeping
track of every block changed in memory, applying the WAL stream of full
page and non-full-page changes to the in memory set, until the 'stop
backup' is called, at which point the WAL-compressor simply dumps all
the records as full page writes into this new WAL stream.

Or perhaps some combination of an 'always running' WAL compressor which
simply reduces the overall size of the WAL stream with coordination
around full backups.

Thanks,

Stephen

On Wed, Jun 19, 2013 at 1:13 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

I'm thinking of implementing an incremental backup tool for
PostgreSQL. The use case for the tool would be taking a backup of huge
database. For that size of database, pg_dump is too slow, even WAL
archive is too slow/ineffective as well. However even in a TB
database, sometimes actual modified blocks are not that big, may be
even several GB. So if we can backup those modified blocks only,
that would be an effective incremental backup method.

PostgreSQL definitely needs better tools to cope with TB scale
databases. Especially when the ideas that get rid of anti-wraparound
vacuums materialize and make huge databases more practical.

For now, my idea is pretty vague.

- Record info about modified blocks. We don't need to remember the
whole history of a block if the block was modified multiple times.
We just remember that the block was modified since the last
incremental backup was taken.

- The info could be obtained by trapping calls to mdwrite() etc. We need
to be careful to avoid such blocks used in xlogs and temporary
tables to not waste resource.

Unless I'm missing something, the information about modified blocks
can also be obtained by reading WAL, not requiring any modifications
to core.

- If many blocks were modified in a file, we may be able to condense
the info as "the whole file was modified" to reduce the amount of
info.

You could keep a list of block ranges modified and when the list gets
too large, merge ranges that are close together.

- How to take a consistent incremental backup is an issue. I can't
think of a clean way other than "locking whole cluster", which is
obviously unacceptable. Maybe we should give up "hot backup"?

I don't see why regular pg_start_backup(), copy out modified blocks,
pg_stop_backup(), copy WAL needed to recover approach wouldn't work
here.

A good feature of the tool would be to apply the incremental backup to
the previous backup while copying out old blocks so you could have the
latest full backup available and incremental changes to rewind it to
the previous version.

Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

For now, my idea is pretty vague.

- Record info about modified blocks. We don't need to remember the
whole history of a block if the block was modified multiple times.
We just remember that the block was modified since the last
incremental backup was taken.

- The info could be obtained by trapping calls to mdwrite() etc. We need
to be careful to avoid such blocks used in xlogs and temporary
tables to not waste resource.

- If many blocks were modified in a file, we may be able to condense
the info as "the whole file was modified" to reduce the amount of
info.

- How to take a consistent incremental backup is an issue. I can't
think of a clean way other than "locking whole cluster", which is
obviously unacceptable. Maybe we should give up "hot backup"?

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 6/19/13 11:02 AM, Claudio Freire wrote:

On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

For now, my idea is pretty vague.

- Record info about modified blocks. We don't need to remember the
whole history of a block if the block was modified multiple times.
We just remember that the block was modified since the last
incremental backup was taken.

- The info could be obtained by trapping calls to mdwrite() etc. We need
to be careful to avoid such blocks used in xlogs and temporary
tables to not waste resource.

- If many blocks were modified in a file, we may be able to condense
the info as "the whole file was modified" to reduce the amount of
info.

- How to take a consistent incremental backup is an issue. I can't
think of a clean way other than "locking whole cluster", which is
obviously unacceptable. Maybe we should give up "hot backup"?

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

A snapshot is not the same as an incremental backup; it presents itself as a full copy of the filesystem. Actually, since it's on the same underlying storage a snapshot isn't really a good backup at all.

The proposal (at least as I read it) is to provide a means to easily deal with *only* the data that has actually *changed* since the last backup was taken.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 19, 2013 at 3:54 PM, Jim Nasby <jim@nasby.net> wrote:

On 6/19/13 11:02 AM, Claudio Freire wrote:

On Wed, Jun 19, 2013 at 7:13 AM, Tatsuo Ishii <ishii@postgresql.org>
wrote:

For now, my idea is pretty vague.

- Record info about modified blocks. We don't need to remember the
whole history of a block if the block was modified multiple times.
We just remember that the block was modified since the last
incremental backup was taken.

- The info could be obtained by trapping calls to mdwrite() etc. We need
to be careful to avoid such blocks used in xlogs and temporary
tables to not waste resource.

- If many blocks were modified in a file, we may be able to condense
the info as "the whole file was modified" to reduce the amount of
info.

- How to take a consistent incremental backup is an issue. I can't
think of a clean way other than "locking whole cluster", which is
obviously unacceptable. Maybe we should give up "hot backup"?

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

A snapshot is not the same as an incremental backup; it presents itself as a
full copy of the filesystem. Actually, since it's on the same underlying
storage a snapshot isn't really a good backup at all.

Read on bacula[0]http://www.bacula.org/en/, which is huge and thus this info may be hard to
find, you can take that snapshot, which will be on the same filesystem
of course, and *then* back it up. So you get a consistent snapshot on
your backup, which means a correct backup, and the backup certainly
doesn't have to be on the same filesystem. It even works for ext3 if
you install the right kernel modules.

Yes, it's a snapshot of the entire filesystem. So it's not the same as
a database-only backup. But it does have a huge overlap don't you
think?

When WAL archiving can get you PITR, and bacula-like tools can get you
incremental and consistent full-FS-snapshot backups, what does the
proposed feature add? I don't think you can get PITR with the proposed
feature, as it takes a snapshot only when told to, and it can't take
multiple snapshots. The only way to get PITR AFAIK is with WAL
archiving, so whether it's viable or not for TB-sized databases is
moot, if it's the only option.

And it will add an overhead. A considerable overhead. Even if you only
have to flip a bit on some page map, it amplifies writes twofold
(unless writes can be coalesced, of which there is no guarantee).

In the end, it may be preferrable to just alter PG's behavior slightly
to make bacula, rsync or whichever tool's job easier. Like trying hard
not to write to cold segments, so entire segments can be skipped by
quick mtime checks.

[0]: http://www.bacula.org/en/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Claudio Freire (klaussfreire@gmail.com) wrote:

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

Large databases tend to have multiple filesystems and getting a single,
consistent, snapshot across all of them while under load is..
'challenging'. It's fine if you use pg_start/stop_backup() and you're
saving the XLOGs off, but if you can't do that..

Thanks,

Stephen

On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Claudio Freire (klaussfreire@gmail.com) wrote:

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

Large databases tend to have multiple filesystems and getting a single,
consistent, snapshot across all of them while under load is..
'challenging'. It's fine if you use pg_start/stop_backup() and you're
saving the XLOGs off, but if you can't do that..

Good point there.

I still don't like the idea of having to mark each modified page. The
WAL compressor idea sounds a lot more workable. As in scalable.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Claudio Freire escribi�:

On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Claudio Freire (klaussfreire@gmail.com) wrote:

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

Large databases tend to have multiple filesystems and getting a single,
consistent, snapshot across all of them while under load is..
'challenging'. It's fine if you use pg_start/stop_backup() and you're
saving the XLOGs off, but if you can't do that..

Good point there.

I still don't like the idea of having to mark each modified page. The
WAL compressor idea sounds a lot more workable. As in scalable.

There was a project that removed "useless" WAL records from the stream,
to make it smaller and useful for long-term archiving. It only removed
FPIs as far as I recall. It's dead now, and didn't compile on recent
(9.1?) Postgres because of changes in the WAL structs, IIRC.

This doesn't help if you have a large lot of UPDATEs that touch the same
set of rows over and over, though. Tatsuo-san's proposal would allow
this use-case to work nicely because you only keep one copy of such
data, not one for each modification.

If you have the two technologies, you could teach them to work in
conjunction: you set up WAL replication, and tell the WAL compressor to
prune updates for high-update tables (avoid useless traffic), then use
incremental backup to back these up. This seems like it would have a
lot of moving parts and be rather bug-prone, though.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 19, 2013 at 7:18 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

If you have the two technologies, you could teach them to work in
conjunction: you set up WAL replication, and tell the WAL compressor to
prune updates for high-update tables (avoid useless traffic), then use
incremental backup to back these up. This seems like it would have a
lot of moving parts and be rather bug-prone, though.

I don't think it would be worse than storage-manager-level stuff. And
though more complex, don't underestimate the pros: lower footprint,
better scalability, and you get consistent online backups.

That mechanism can also be used to distill a list of modified pages,
mind you, instead of hooking into storage-manager stuff. The pro
there, is that it wouldn't amplify writes. The con there is that you
don't get consistent online backups.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I'm thinking of implementing an incremental backup tool for
PostgreSQL. The use case for the tool would be taking a backup of huge
database. For that size of database, pg_dump is too slow, even WAL
archive is too slow/ineffective as well. However even in a TB
database, sometimes actual modified blocks are not that big, may be
even several GB. So if we can backup those modified blocks only,
that would be an effective incremental backup method.

I'm trying to figure out how that's actually different from WAL..? It
sounds like you'd get what you're suggesting with simply increasing the
checkpoint timeout until the WAL stream is something which you can keep
up with. Of course, the downside there is that you'd have to replay
more WAL when recovering.

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 19, 2013 at 7:39 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

I'm thinking of implementing an incremental backup tool for
PostgreSQL. The use case for the tool would be taking a backup of huge
database. For that size of database, pg_dump is too slow, even WAL
archive is too slow/ineffective as well. However even in a TB
database, sometimes actual modified blocks are not that big, may be
even several GB. So if we can backup those modified blocks only,
that would be an effective incremental backup method.

I'm trying to figure out how that's actually different from WAL..? It
sounds like you'd get what you're suggesting with simply increasing the
checkpoint timeout until the WAL stream is something which you can keep
up with. Of course, the downside there is that you'd have to replay
more WAL when recovering.

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.

How does replication handle that?

Because I doubt that's an issue only with backups.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

I'm trying to figure out how that's actually different from WAL..? It
sounds like you'd get what you're suggesting with simply increasing the
checkpoint timeout until the WAL stream is something which you can keep
up with. Of course, the downside there is that you'd have to replay
more WAL when recovering.

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.

How does replication handle that?

Because I doubt that's an issue only with backups.

Unlogged tables are not replicated to streaming replication
standbys. It is clearly stated in the doc.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tatsuo Ishii (ishii@postgresql.org) wrote:

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.

Unlogged tables are also nuked on recovery, so I'm not sure why you
think an incremental backup would help.. If you're recovering (even
from a simple crash), unlogged tables are going to go away.

Put simply, unlogged tables should not be used for any data you care
about, period.

Thanks,

Stephen

* Tatsuo Ishii (ishii@postgresql.org) wrote:

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.

Unlogged tables are also nuked on recovery, so I'm not sure why you
think an incremental backup would help.. If you're recovering (even
from a simple crash), unlogged tables are going to go away.

If my memory serves, unlogged tables are not nuked when PostgeSQL is
stopped by a planned shutdown (not by crash or by "pg_ctl -m i
stop"). If PostgreSQL works so, incremental backup should be able to
recover unlogged tables as well, at least people would expect so IMO.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tatsuo Ishii (ishii@postgresql.org) wrote:

* Tatsuo Ishii (ishii@postgresql.org) wrote:

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.

Unlogged tables are also nuked on recovery, so I'm not sure why you
think an incremental backup would help.. If you're recovering (even
from a simple crash), unlogged tables are going to go away.

If my memory serves, unlogged tables are not nuked when PostgeSQL is
stopped by a planned shutdown (not by crash or by "pg_ctl -m i
stop"). If PostgreSQL works so, incremental backup should be able to
recover unlogged tables as well, at least people would expect so IMO.

Sure, if you shut down PG, rsync the entire thing and then bring it back
up then unlogged tables should work when "backed up".

They're not WAL'd, so expecting them to work when restoring a backup of
a PG that had been running at the time of the backup is folly.

Thanks,

Stephen

* Tatsuo Ishii (ishii@postgresql.org) wrote:

* Tatsuo Ishii (ishii@postgresql.org) wrote:

Yeah, at first I thought using WAL was a good idea. However I realized
that the problem using WAL is we cannot backup unlogged tables because
they are not written to WAL.

Unlogged tables are also nuked on recovery, so I'm not sure why you
think an incremental backup would help.. If you're recovering (even
from a simple crash), unlogged tables are going to go away.

If my memory serves, unlogged tables are not nuked when PostgeSQL is
stopped by a planned shutdown (not by crash or by "pg_ctl -m i
stop"). If PostgreSQL works so, incremental backup should be able to
recover unlogged tables as well, at least people would expect so IMO.

Sure, if you shut down PG, rsync the entire thing and then bring it back
up then unlogged tables should work when "backed up".

I don't think using rsync (or tar or whatever general file utils)
against TB database for incremental backup is practical. If it's
practical, I would never propose my idea.

They're not WAL'd, so expecting them to work when restoring a backup of
a PG that had been running at the time of the backup is folly.

Probably you forget about our nice pg_dump tool:-)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 19, 2013 at 6:20 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Claudio Freire (klaussfreire@gmail.com) wrote:

I don't see how this is better than snapshotting at the filesystem
level. I have no experience with TB scale databases (I've been limited
to only hundreds of GB), but from my limited mid-size db experience,
filesystem snapshotting is pretty much the same thing you propose
there (xfs_freeze), and it works pretty well. There's even automated
tools to do that, like bacula, and they can handle incremental
snapshots.

Large databases tend to have multiple filesystems and getting a single,
consistent, snapshot across all of them while under load is..
'challenging'. It's fine if you use pg_start/stop_backup() and you're
saving the XLOGs off, but if you can't do that..

Good point there.

I still don't like the idea of having to mark each modified page. The
WAL compressor idea sounds a lot more workable. As in scalable.

Why do you think WAL compressor idea is more scalable? I really want
to know why. Besides the unlogged tables issue, I can accept the idea
if WAL based solution is much more efficient. If there's no perfect,
ideal solution, we need to prioritize things. My #1 priority is
allowing to create incremental backup against TB database, and the
backup file should be small enough and the time to create it is
acceptable. I just don't know why scanning WAL stream is much cheaper
than recording modified page information.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Tatsuo Ishii (ishii@postgresql.org) wrote:

I don't think using rsync (or tar or whatever general file utils)
against TB database for incremental backup is practical. If it's
practical, I would never propose my idea.

You could use rsync for incremental updates if you wanted, it'd
certainly be faster in some cases and it's entirely possible to use such
against TB databases in some cases.

They're not WAL'd, so expecting them to work when restoring a backup of
a PG that had been running at the time of the backup is folly.

Probably you forget about our nice pg_dump tool:-)

I don't consider pg_dump a mechanism for backing up TB databases.
You're certainly welcome to use it for dumping unlogged tables, but I
can't support the notion that unlogged tables should be supported
through WAL-supported file-based backups. If we're going down this
road, I'd much rather see support for exporting whole files from and
importing them back into PG in some way which completely avoids the need
to re-parse or re-validate data and supports pulling in indexes as part
of the import.

Thanks,

Stephen

* Tatsuo Ishii (ishii@postgresql.org) wrote:

Why do you think WAL compressor idea is more scalable? I really want
to know why. Besides the unlogged tables issue, I can accept the idea
if WAL based solution is much more efficient. If there's no perfect,
ideal solution, we need to prioritize things. My #1 priority is
allowing to create incremental backup against TB database, and the
backup file should be small enough and the time to create it is
acceptable. I just don't know why scanning WAL stream is much cheaper
than recording modified page information.

Because that's what the WAL *is*..?

Why would you track what's changed twice?

Thanks,

Stephen