Re: Force WAL cleanup on running instance

Question everyone isn't this a problem with the order of operations?

switching the wal files then running checkpoint means the Checkpoint can
cross wal files, so the previous wal file can not be deleted???

To my understanding the order operations should be

Checkpoint
which flushes everything to disk, then
pg_switch_wal()

which creates an empty wal file and the previous wal can be deleted?

http://www.interdb.jp/pg/pgsql09.html#_9.7.
https://www.postgresql.org/docs/current/wal-configuration.html

Or am i missing something?

On Wed, Mar 11, 2020 at 11:45 AM Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Wed, 11 Mar 2020 at 08:59, Torsten Krah <krah(dot)tm(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> I am building a docker image with a postgresql 12.2 instance and while
>> doing so and importing a dump and running some update scripts wal size
>> is increasing.
>>
>> When finished I don't need all those wal files anymore and tried to
>> force the daemon to clean them up and tried this:
>>
>> select pg_switch_wal();
>> CHECKPOINT;
>>
>> and did wait for a minute.
>>
>> Sometimes it works and wal files are cleaned and moved away so my image
>> size is way smaller - but it does not happen always in that minute.
>>
>> So is there a way to tell postgres to force the housekeeping of the wal
>> stuff via a statement / command line tool?
>> In a "normal" running instance it just takes care of itself and it will
>> happen sooner or later and it doesn't really matter when that will
>> happen - but with my docker image which is automatically build it would
>> be nice to have a deterministic way of trigger that to reduce the final
>> size image.
>
>
> The size of the task varies, so sometimes takes longer than 60s, depending
> upon your hardware.
>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> <http://www.2ndquadrant.com/>
> PostgreSQL Solutions for the Enterprise
>