Обсуждение: Force WAL cleanup on running instance

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.

Is there some way to trigger this?

kind regards

Torsten
 
-- 

Am Mittwoch, den 11.03.2020, 08:42 -0600 schrieb Michael Lewis:
> I don't know the answer to your stated question. I am curious if you
> have
> set wal_level = minimal and if not, if that would be appropriate for
> your
> use case and might render your concern a non-issue.

Hi Micheal,

I am already running with "wal_level = minimal" set.

kind regards

Torsten

Am Mittwoch, den 11.03.2020, 15:45 +0000 schrieb Simon Riggs:
> The size of the task varies, so sometimes takes longer than 60s,
> depending
> upon your hardware.

Yes that's what I am observing and why I am asking if there is some
select statement or command which triggers that task and returns after
it has finished - as I can't predict how long that task may run.

kind regards

Torsten

-- 

Torsten Krah <krah.tm@gmail.com> writes:

> Am Mittwoch, den 11.03.2020, 15:45 +0000 schrieb Simon Riggs:
>
>> The size of the task varies, so sometimes takes longer than 60s,
>> depending
>> upon your hardware.
>
> Yes that's what I am observing and why I am asking if there is some
> select statement or command which triggers that task and returns after
> it has finished - as I can't predict how long that task may run.

If your site can afford a restart after the bulk load,

1. Clean shutdown.
2. pg_resetwal
3. Start 

That should leave you with a very small N WAL files, perhaps just 1,
though I've not run it lately to reverify.

HTH

>
> kind regards
>
> Torsten

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net

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
withno other transactions going on. Maybe I should have done that. Anyway, I also always do the checkpoint first and
thenthe WAL switch, which in my case is also an old habit from Oracle. ;-) 

Cheers,
Paul


> On 11. Mar, 2020, at 16:51, Justin <zzzzz.graf@gmail.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
cannot 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@2ndquadrant.com> wrote:
> On Wed, 11 Mar 2020 at 08:59, Torsten Krah <krah.tm@gmail.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

Am Mittwoch, den 11.03.2020, 11:46 -0500 schrieb Jerry Sievers:
> If your site can afford a restart after the bulk load,
> 
> 1. Clean shutdown.
> 2. pg_resetwal
> 3. Start 
> 
> That should leave you with a very small N WAL files, perhaps just 1,
> though I've not run it lately to reverify.

Thanks - that worked like a charm and is exactly what I was looking
for, of cause I can afford that, it's shutdown after the init anyway -
so after running pg_resetwal this was left:

root@53e0d45ce5d7:/var/lib/postgresql/data/pg_wal# ls -lh
total 17M
-rw------- 1 postgres postgres  16M Mär 11 17:23 000000010000000000000012
drwx------ 2 postgres postgres 4,0K Mär 11 17:13 archive_status

Nice one :) thanks.

kind regards

Torsten