Thread: WAL reducing size
Is Postgres removes the committed transaction after archive. LIke in SQL Server , which removes the committed transactions after taking Log backup. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Mon, Dec 4, 2017 at 8:50 AM, sql2pg wrote:
> Is Postgres removes the committed transaction after archive. LIke in SQL
> Server , which removes the committed transactions after taking Log backup.
>
If I understand correctly, yes - eventually. The docs explain the behavior
and the configuration variables that control it.
https://www.postgresql.org/docs/10/static/wal-configuration.html
David J.
how about uncommitted(open transactions) . if a segment has 1 committed and 2 uncommitted transactions then will it keep the segment instead deleting it after checkpoint , since it has 2 uncommitted transactions -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
sql2pg wrote: > how about uncommitted(open transactions) . if a segment has 1 committed and > 2 uncommitted transactions then will it keep the segment instead deleting it > after checkpoint , since it has 2 uncommitted transactions There is no connection between transaction commit and checkpoint removing WAL files. WAL files are removed after two checkpoints have occured, regardless of transactions that might be open. The on-disk effects of those transactions that occured before the earlier checkpoint are already on disk anyway. The commit record might appear at whatever point in the future, and the rows will not become visible to other transactions until then, regardless of what happened to the WAL segments. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Tue, Dec 5, 2017 at 6:12 AM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote: > sql2pg wrote: >> how about uncommitted(open transactions) . if a segment has 1 committed and >> 2 uncommitted transactions then will it keep the segment instead deleting it >> after checkpoint , since it has 2 uncommitted transactions > > There is no connection between transaction commit and checkpoint > removing WAL files. WAL files are removed after two checkpoints have > occured, regardless of transactions that might be open. The on-disk > effects of those transactions that occured before the earlier checkpoint > are already on disk anyway. The commit record might appear at whatever > point in the future, and the rows will not become visible to other > transactions until then, regardless of what happened to the WAL > segments. Something that perhaps does not concern you directly, but in Postgres 11 WAL segments get recycled after one completed checkpoint thanks to commit 4b0d28d. -- Michael