Re: hot standby, how to disable WAL archiving ? - Mailing list pgsql-admin
| From | Keith |
|---|---|
| Subject | Re: hot standby, how to disable WAL archiving ? |
| Date | |
| Msg-id | CAHw75vvwTnW7SU8=6UEHcbOOb8dyy9Uex_h-6tzOsXxytG=bSA@mail.gmail.com Whole thread Raw |
| In response to | Re: hot standby, how to disable WAL archiving ? (John Scalia <jayknowsunix@gmail.com>) |
| Responses |
Re: hot standby, how to disable WAL archiving ?
|
| List | 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.--JayOn Tue, Sep 29, 2015 at 6:01 PM, Roman Shubovich <r.shubovich@gmail.com> wrote:hiI'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 = 64checkpoint_segments = 32checkpoint_timeout = 1hcheckpoint_completion_target = 0.8WAL archiving is offmaster host works as expected - he keeps about 140-160 files in the pg_xlog folderbut 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 occurspostgres version 9.4.4, compiled from sourceOS 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
pgsql-admin by date: