wal archive peak during pg_dump

On Thu, Jan 9, 2014 at 7:42 PM, Willy-Bas Loos <willybas@gmail.com> wrote:

Hi,

I've set up hot standby slaves for a couple of clusters.
The wal is cleaned up after use, i don't use it as a backup (yet).
It seems that the amount of wal peaks shortly after midnight, when pg_dump
is running.

It doesn't seem logical to me that pg_dump should generate wal, but i
haven't been able to find a different explanation so far.
So to make sure, i want to ask you people: can it be that running pg_dump
creates a lot of wal?

pg_dump does modify any data so it basically does not create any WAL
files. But HOT pruning could, as well as data checksums in this case a
plain SELECT can change some tuple hint bits, which indeed generates
WAL. This reminds me of this thread:
/messages/by-id/E1T9G6S-0007C4-OV@wrigleys.postgresql.org
It would be interesting to see with xlogdump what is the WAL generated.

Regards,
--
Michael

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

On Thu, Jan 9, 2014 at 11:42 AM, Willy-Bas Loos <willybas@gmail.com> wrote:

It doesn't seem logical to me that pg_dump should generate wal, but i
haven't been able to find a different explanation so far.
So to make sure, i want to ask you people: can it be that running pg_dump
creates a lot of wal?

Interesting. pg_dump works in a transactional model, but it should be
on read only. Is it possible you have dirty pages that are made
persistent due to pg_dump? Or could it be a vacuum process or
something related to freezing the tuples?

Luca

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