Force WAL cleanup on running instance

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.

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

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/&gt;
PostgreSQL Solutions for the Enterprise

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:

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

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