Re: understanding max_wal_size,wal_keep_segments and checkpoints

Mariel Cherkassky wrote:
> Yeah, so basically if we open a transaction and we do some insert queries, until the transaction
> is commited the changes(the wal data and not the blocked that are chaned) are kept in the wal buffers ?
> . When the user commits the transaction, the wal buffer(only the transaction log of that specific
> transaction ?) is written to wal files. When the database completes saving the content of the
> transaction log into the wal files, the commit completes. Did I got it right ?

WAL can be written to file before the transaction commits.
Otherwise the size of a transaction would be limited.
Only at commit time, it has to be written out and flushed to disk.

> What I meant, when checkpoint occurs, it reads the wal files created since last checkpoint,
> and does those changing on the data blocks on the disk ? I was not talking about dirty blocks
> from shared_buffer.

No, PostgreSQL does not read the WAL files when it performs a checkpoint.
When data are modified, first WAL is written, then it is written to shared buffers.
The checkpoint flushes dirty pages in shared buffers to disk.

> > so I'f I want have replication slot and wal_keep_segment is 0 after the archiving of
> > the wal it should be recycled/deleted ?

Only if it is older than the position of the replication slot.

> > So basically having wal_keep_segments and replication slot configured together is a mistake right ?
> > In that case, if you have both configured, and you set wal_keep_segments to 0, the db should
> > delete all the unused wals ?

It is pointless to have both a replication slot and wal_keep_segments, yes.
Setting wal_keep_segments to 0 is the right move in that case and should
reduce pg_xlog size in time.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com