pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedhot standby, how to disable WAL archiving ?
5+ messages / 3 participants
[nested] [flat]
* hot standby, how to disable WAL archiving ?
@ 2015-09-29 23:01 Roman Shubovich <r.shubovich@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Roman Shubovich @ 2015-09-29 23:01 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: hot standby, how to disable WAL archiving ?
@ 2015-09-30 12:16 John Scalia <jayknowsunix@gmail.com>
parent: Roman Shubovich <r.shubovich@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: John Scalia @ 2015-09-30 12:16 UTC (permalink / raw)
To: Roman Shubovich <r.shubovich@gmail.com>; +Cc: pgsql-admin
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.shubovich@gmail.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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: hot standby, how to disable WAL archiving ?
@ 2015-09-30 14:25 Keith <keith@keithf4.com>
parent: John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Keith @ 2015-09-30 14:25 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Roman Shubovich <r.shubovich@gmail.com>; pgsql-admin
On Wed, Sep 30, 2015 at 8:16 AM, John Scalia <jayknowsunix@gmail.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.shubovich@gmail.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: hot standby, how to disable WAL archiving ?
@ 2015-09-30 22:29 Roman Shubovich <r.shubovich@gmail.com>
parent: Keith <keith@keithf4.com>
0 siblings, 1 reply; 5+ messages in thread
From: Roman Shubovich @ 2015-09-30 22:29 UTC (permalink / raw)
To: Keith <keith@keithf4.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
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@keithf4.com>:
>
>
> On Wed, Sep 30, 2015 at 8:16 AM, John Scalia <jayknowsunix@gmail.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.shubovich@gmail.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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: hot standby, how to disable WAL archiving ?
@ 2015-09-30 23:34 John Scalia <jayknowsunix@gmail.com>
parent: Roman Shubovich <r.shubovich@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: John Scalia @ 2015-09-30 23:34 UTC (permalink / raw)
To: Roman Shubovich <r.shubovich@gmail.com>; Keith <keith@keithf4.com>; +Cc: pgsql-admin
Postgresql only writes a single WAL file at any point and it will be the most recent file in the pg_xlog directory. Check the contents of this directory and see if any gaps in
filenames appear. You should see an orderly progression in names that follow a hex pattern. If you do see some old files that have gaps in the naming sequence, those should be able
to be safely removed. Also, if you've restarted your database, the new wal_keep_segments setting should be active, you can confirm in your database by entering "show
wal_keep_segments;" You can then safely remove any files provided you still keep the last /n /files where /n/ = the value reported by the show command.
FYI, checkpoint_timeout only instructs postgresql to write a checkpoint into the WAL file after this amount of time has elapsed, otherwise based on transaction volume, a checkpoint
will be recorded every /n /WAL files provided the elapsed time has not eclipsed this setting.
On 9/30/2015 5:29 PM, Roman Shubovich wrote:
> 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@keithf4.com <mailto:keith@keithf4.com>>:
>
>
>
> On Wed, Sep 30, 2015 at 8:16 AM, John Scalia <jayknowsunix@gmail.com <mailto:jayknowsunix@gmail.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.shubovich@gmail.com <mailto:r.shubovich@gmail.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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-09-30 23:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-09-29 23:01 hot standby, how to disable WAL archiving ? Roman Shubovich <r.shubovich@gmail.com>
2015-09-30 12:16 ` John Scalia <jayknowsunix@gmail.com>
2015-09-30 14:25 ` Keith <keith@keithf4.com>
2015-09-30 22:29 ` Roman Shubovich <r.shubovich@gmail.com>
2015-09-30 23:34 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox