Horton

Thread

  1. Force WAL cleanup on running instance

    Torsten Krah <krah.tm@gmail.com> — 2020-03-11T08:59:41Z

    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
 
--

  2. Re: Force WAL cleanup on running instance

    Michael Lewis <mlewis@entrata.com> — 2020-03-11T14:42:42Z

    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.

  3. Re: Force WAL cleanup on running instance

    Torsten Krah <krah.tm@gmail.com> — 2020-03-11T15:13:27Z

    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

  4. Re: Force WAL cleanup on running instance

    Simon Riggs <simon@2ndquadrant.com> — 2020-03-11T15:45:06Z

    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/
<http://www.2ndquadrant.com/>
PostgreSQL Solutions for the Enterprise

  5. Re: Force WAL cleanup on running instance

    Justin <zzzzz.graf@gmail.com> — 2020-03-11T15:51:45Z

    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@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/
> <http://www.2ndquadrant.com/>
> PostgreSQL Solutions for the Enterprise
>

  6. Re: Force WAL cleanup on running instance

    Torsten Krah <krah.tm@gmail.com> — 2020-03-11T16:01:54Z

    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

--

  7. Re: Force WAL cleanup on running instance

    Jerry Sievers <gsievers19@comcast.net> — 2020-03-11T16:46:33Z

    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

  8. Re: Force WAL cleanup on running instance

    Paul Foerster <paul.foerster@gmail.com> — 2020-03-11T17:29:23Z

    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.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 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@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

  9. Re: Force WAL cleanup on running instance

    Torsten Krah <krah.tm@gmail.com> — 2020-03-11T17:39:52Z

    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