Re: hot standby, how to disable WAL archiving ?

I don't want disable WAL logging at all
but I want know how many files will be created by the postgres at runtime
and I want manage of this behavior
at this time i can see that standby server ignores all of WAL parameters in
config and creates as many WAL files as it wants

I've changed wal_keep_segments, checkpoint_segments, checkpoint_timeout
config parameters, but nothing changed
for testing current behavior I just made restore of database on master
server (7GB)
then I look at wal files on master and stanby servers
master server has only ~150 files (as expected by the config parameters),
but stanby has more than 400 and in archive_status folder contains many
xxxxxxx.done as I described in my first post

config files is the same on both sides

2015-09-30 17:25 GMT+03:00 Keith <keith(at)keithf4(dot)com>:

>
>
> On Wed, Sep 30, 2015 at 8:16 AM, John Scalia <jayknowsunix(at)gmail(dot)com>
> wrote:
>
>> Hi Roman,
>>
>> Maybe one of the developers can chime in on this, but to my knowledge, as
>> at least one WAL file should be available for the server to successfully
>> start, you can't turn these off entirely. My suggestion would be to set
>> wal_keep_segments to a much smaller number, even "1", Also, did you change
>> the default size of the WAL segments? I can't actually remember the exact
>> name for this parameter, but the default is 16MB. You can have this set to
>> a much larger value.
>> --
>> Jay
>>
>> On Tue, Sep 29, 2015 at 6:01 PM, Roman Shubovich <r(dot)shubovich(at)gmail(dot)com>
>> wrote:
>>
>>> hi
>>>
>>> I've up master-slave streaming replication.
>>> each host have RAM tmpfs disk for WAL files with 5BG space, and my
>>> config is:
>>>
>>> wal_keep_segments = 64
>>> checkpoint_segments = 32
>>> checkpoint_timeout = 1h
>>> checkpoint_completion_target = 0.8
>>> WAL archiving is off
>>>
>>> master host works as expected - he keeps about 140-160 files in the
>>> pg_xlog folder
>>> but standby host ignore the config, he won't remove old wal files and in
>>> archive_status folder I can see many xxxxxx.done files, but archiving is
>>> not enabled.
>>>
>>> how can I disable that behavior ?
>>>
>>> I already tried playing with archive_cleanup_command,
>>> recovery_end_command, pg_archivecleanup - nothing helped.
>>> when standby host operate as master server then no problems occurs
>>>
>>> postgres version 9.4.4, compiled from source
>>> OS ubuntu 14.04.3
>>>
>>
>>
> If you're talking about the WAL files in the pg_xlogs folder, you cannot
> disable this completely. Those are the WAL files that get written to
> directly before flushing out to the table files themselves and how postgres
> attempts to stay crash safe and not lose your data. Streaming replication
> also uses these files. The WAL archiving discussed here
> http://www.postgresql.org/docs/9.4/static/continuous-archiving.html that
> uses the archive_command is for secondary archiving of those files outside
> of the pg_xlog folder for use in backups and point-in-time-recovery. That
> is not turned on by default and you don't mention having those settings
> turned on.
>
> The archive cleanup commands are for cleaning up secondary WAL files sent
> over by the master's archive_command, not for cleaning up the files in
> pg_xlogs.
>
> With wal_keep_settings set to 64, you'll always, at a minimum, have
> 16MB*64 = 1GB of WAL files in the pg_xlogs folder. If you have heavy writes
> going to the database this amount could certainly be higher. Is there a
> reason you have the checkpoint_timeout set to 1 hr? That could also be the
> reason you have additional WAL files beyond the 64 minimum sticking around.
> Read up about checkpoints here
> http://www.postgresql.org/docs/9.4/static/wal-configuration.html to
> better understand what they do.
>
> The WAL segment size cannot be changed at any time. You have to set that
> during compile time and it's not recommended to change unless you really
> have a good reason.
>
> If you're using streaming replication, I would not set the
> wal_keep_segments value to 1 on the master. If your slave falls behind for
> some reason, it has to use the files in the pg_xlogs folder on your master
> to catch back up again. Since you're on 9.4, though, you can look into
> setting up a replication slot to avoid this issue as well and lower the
> wal_keep_segments value.
> http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS
>
> Keith
>