Re: finding changed blocks using WAL scanning

On Thu, Apr 18, 2019 at 5:47 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> How would the modblock file record all the modified blocks across
> restarts and crashes? I assume that 1G of WAL would not be available
> for scanning. I suppose that writing a modblock file to some PGDATA
> location when WAL is removed would work since during a crash the
> modblock file could be updated with the contents of the existing pg_wal
> files.

I think you've got to prevent the WAL from being removed until a
.modblock file has been written. In more detail, you should (a) scan
all the WAL segments that will be summarized in the .modblock file,
(b) write the file under a temporary name, (c) fsync it, (d) rename it
into place, (e) fsync it again, and (f) then allow those WAL segments
to be removed, if they are otherwise eligible to be removed.

If 1GB of WAL is too much to keep around (which I doubt, except on
systems that are so small and low-activity that they don't need
incremental backups anyway), then you'd have to scan less WAL at once
and write smaller .modblock files.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company