Re: hot standby, how to disable WAL archiving ?

[John Scalia <jayknowsunix@xxxxxxxxx>]

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@xxxxxxxxxxx>:

On Wed, Sep 30, 2015 at 8:16 AM, John Scalia <jayknowsunix@xxxxxxxxx> 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@xxxxxxxxx> 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