Re: Tracking of page changes for backup purposes. PTRACK [POC]

On 12/20/2017 09:29 PM, Pavel Stehule wrote:
> 
> 
> 2017-12-20 21:18 GMT+01:00 Robert Haas <robertmhaas@gmail.com
> <mailto:robertmhaas@gmail.com>>:
> 
>     On Wed, Dec 20, 2017 at 3:15 PM, Pavel Stehule
>     <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:
>     >> > So I'm somewhat hesitant to proclaim option 5 as the clear winner, here.
>     >>
>     >> I agree.  I think (4) is better.
>     >
>     > Can depends on load? For smaller intensive updated databases the 5 can be
>     > optimal, for large less updated databases the 4 can be better.
> 
>     It seems to me that the difference is that (4) tracks which pages have
>     changed in the background, and (5) does it in the foreground.  Why
>     would we want the latter?
> 
> 
> 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.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services