Re: Force WAL cleanup on running instance

Hi Justin,

that came to my mind also. Then I tried and found that not always a new WAL is created. I admit I tried on a test DC with no other transactions going on. Maybe I should have done that. Anyway, I also always do the checkpoint first and then the WAL switch, which in my case is also an old habit from Oracle. ;-)

Cheers,
Paul

> On 11. Mar, 2020, at 16:51, Justin <zzzzz(dot)graf(at)gmail(dot)com> wrote:
>
> 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/
> PostgreSQL Solutions for the Enterprise