Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-12 Thread Michael Paquier 
On Tue, Sep 12, 2017 at 11:43 PM, Ron Johnson  wrote:
> On 09/07/2017 09:32 AM, Tom Lane wrote:
>>
>> Ron Johnson  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

Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-12 Thread Ron Johnson 

On 09/07/2017 09:32 AM, Tom Lane wrote:

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

Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-07 Thread Ron Johnson 

On 09/07/2017 05:07 PM, Michael Paquier wrote:

On Thu, Sep 7, 2017 at 11:08 PM, 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!).
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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-07 Thread Michael Paquier 
On Thu, Sep 7, 2017 at 11:08 PM, 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!).
> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-07 Thread Tom Lane 
Ron Johnson  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-07 Thread Ron Johnson 

On 09/07/2017 09:08 AM, Tom Lane wrote:

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WAL & ready files retained after turning off log shipping

2017-09-07 Thread Tom Lane 
Ron Johnson  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] WAL & ready files retained after turning off log shipping

2017-09-07 Thread Ron Johnson 

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general