Обсуждение: [GENERAL] WAL & ready files retained after turning off log shipping
Hi, v8.4 (and there's nothing I can do about it). After disabling log shipping via setting "archive_mode = off", and then running, "pg_ctl reload", old WAL files and their associated .ready files aren't being deleted. Is there any document you can point me to as to why this is happening, and what I can do to stop it? Thanks -- World Peace Through Nuclear Pacification
Ron Johnson <ron.l.johnson@cox.net> writes:
> After disabling log shipping via setting "archive_mode = off", and then
> running, "pg_ctl reload", old WAL files and their associated .ready files
> aren't being deleted.
Hmm. I might be misremembering, but I think that it's the archiver
process that is in charge of deleting those files, so that this behavior
doesn't seem surprising.
I don't think anybody's thought very hard about how to clean up if
archiving had been running and then you turn it off with not everything
having been archived.
Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata
to see where the last checkpoint is, and delete WAL files whose names
indicate they are before that (but not the one including the checkpoint!).
If you don't intend to do archiving any more, you can just flush all the
.ready files (and .done if any) without much thought.
regards, tom lane
On 09/07/2017 09:08 AM, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> After disabling log shipping via setting "archive_mode = off", and then >> running, "pg_ctl reload", old WAL files and their associated .ready files >> aren't being deleted. > Hmm. I might be misremembering, but I think that it's the archiver > process that is in charge of deleting those files, so that this behavior > doesn't seem surprising. > > I don't think anybody's thought very hard about how to clean up if > archiving had been running and then you turn it off with not everything > having been archived. Presumably, restarting postgres will fix that? > Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata > to see where the last checkpoint is, and delete WAL files whose names > indicate they are before that (but not the one including the checkpoint!). All WAL files after log shipping was stopped will keep accumulating "forever"? > If you don't intend to do archiving any more, you can just flush all the > .ready files (and .done if any) without much thought. Many thanks. -- World Peace Through Nuclear Pacification
Ron Johnson <ron.l.johnson@cox.net> writes:
> On 09/07/2017 09:08 AM, Tom Lane wrote:
>> Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata
>> to see where the last checkpoint is, and delete WAL files whose names
>> indicate they are before that (but not the one including the checkpoint!).
> All WAL files after log shipping was stopped will keep accumulating "forever"?
Hmm ... on second thought, I think if you just remove the .ready/.done
files, the next checkpoint should clean up the old WAL files. That'd
certainly be safer than doing it manually.
regards, tom lane
On Thu, Sep 7, 2017 at 11:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata > to see where the last checkpoint is, and delete WAL files whose names > indicate they are before that (but not the one including the checkpoint!). > If you don't intend to do archiving any more, you can just flush all the > .ready files (and .done if any) without much thought. It would be less risky to do that as a two-time move: - First change archive_command to /sbin/true and let all archives be switched to .done. - And then disable archive_mode. -- Michael
On 09/07/2017 05:07 PM, Michael Paquier wrote: > On Thu, Sep 7, 2017 at 11:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata >> to see where the last checkpoint is, and delete WAL files whose names >> indicate they are before that (but not the one including the checkpoint!). >> If you don't intend to do archiving any more, you can just flush all the >> .ready files (and .done if any) without much thought. > It would be less risky to do that as a two-time move: > - First change archive_command to /sbin/true and let all archives be > switched to .done. > - And then disable archive_mode. Interesting. Thanks. -- World Peace Through Nuclear Pacification
On 09/07/2017 09:32 AM, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: >> On 09/07/2017 09:08 AM, Tom Lane wrote: >>> Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata >>> to see where the last checkpoint is, and delete WAL files whose names >>> indicate they are before that (but not the one including the checkpoint!). >> All WAL files after log shipping was stopped will keep accumulating "forever"? > Hmm ... on second thought, I think if you just remove the .ready/.done > files, the next checkpoint should clean up the old WAL files. That'd > certainly be safer than doing it manually. This weekend, (early Sunday morning) WAL files on the master started accumulating again. Now, .ready files are regenerated every time I delete them, even though according to pg_controldate the last checkpoint was 28 minutes ago. -- World Peace Through Nuclear Pacification -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Tue, Sep 12, 2017 at 11:43 PM, Ron Johnson <ron.l.johnson@cox.net> wrote: > On 09/07/2017 09:32 AM, Tom Lane wrote: >> >> Ron Johnson <ron.l.johnson@cox.net> writes: >>> >>> On 09/07/2017 09:08 AM, Tom Lane wrote: >>>> >>>> Manual cleanup shouldn't be very hard, fortunately. Run pg_controldata >>>> to see where the last checkpoint is, and delete WAL files whose names >>>> indicate they are before that (but not the one including the >>>> checkpoint!). Just noticed. This is actually forgetting that you can only remove safely WAL segment past the last *prior* checkpoint, not the last checkpoint. So you need to keep WAL segments worth of two completed checkpoints. >>> All WAL files after log shipping was stopped will keep accumulating >>> "forever"? >> >> Hmm ... on second thought, I think if you just remove the .ready/.done >> files, the next checkpoint should clean up the old WAL files. That'd >> certainly be safer than doing it manually. > > This weekend, (early Sunday morning) WAL files on the master started > accumulating again. Now, .ready files are regenerated every time I delete > them, even though according to pg_controldate the last checkpoint was 28 > minutes ago. My general advice here would be: do not mess up with the contents of the data folder while Postgres is running, you will never do that right. What is your archiving command telling then? If those .ready files are here, it means that you are not able to archive correctly segments. It seems to me that at the end you should try to just set archive_command = '/bin/true', this would solve all your problems, and trick the server correctly... -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general