On Thu, Dec 21, 2017 at 7:35 AM, Robert Haas <robertmh...@gmail.com> wrote: > On Wed, Dec 20, 2017 at 3:45 PM, Tomas Vondra > <tomas.von...@2ndquadrant.com> wrote: >>> Isn't more effective hold this info in Postgres than in backup sw? >>> Then any backup sw can use this implementation. >> >> I don't think it means it can't be implemented in Postgres, but does it >> need to be done in backend? >> >> For example, it might be a command-line tool similar to pg_waldump, >> which processes WAL segments and outputs list of modified blocks, >> possibly with the matching LSN. Or perhaps something like pg_receivewal, >> doing that in streaming mode. >> >> This part of the solution can still be part of PostgreSQL codebase, and >> the rest has to be part of backup solution anyway. > > I agree with all of that.
+1. This summarizes a bunch of concerns about all kinds of backend implementations proposed. Scanning for a list of blocks modified via streaming gives more availability, but knowing that you will need to switch to a new segment anyway when finishing a backup, does it really matter? Doing it once a segment has finished would be cheap enough, and you can even do it in parallel with a range of segments. Also, since 9.4 and the introduction of the new WAL API to track modified blocks, you don't need to know about the record types to know which blocks are being changed. Here is an example of tool I hacked up in a couple of hours that does actually what you are looking for, aka a scanner of the blocks modified per record for a given WAL segment using xlogreader.c: https://github.com/michaelpq/pg_plugins/tree/master/pg_wal_blocks You could just use that and shape the data in the way you want and you would be good to go. -- Michael
