Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZhQtN-00015m-3t for pgsql-admin@arkaria.postgresql.org; Wed, 30 Sep 2015 23:34:33 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZhQtM-0002Ox-Lo for pgsql-admin@arkaria.postgresql.org; Wed, 30 Sep 2015 23:34:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZhQtF-0002I5-2b for pgsql-admin@postgresql.org; Wed, 30 Sep 2015 23:34:25 +0000 Received: from mail-wi0-x22d.google.com ([2a00:1450:400c:c05::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZhQt8-0002cm-E5 for pgsql-admin@postgresql.org; Wed, 30 Sep 2015 23:34:24 +0000 Received: by wicgb1 with SMTP id gb1so5021220wic.1 for ; Wed, 30 Sep 2015 16:34:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to:content-type; bh=ldhQrqO9akmGtYgquU6NQYjccII8wL/gdD2kNofq7HI=; b=1AnwZrldJ4xJMt+YnIL4CGYRWl0qPRbPCq5ebnP+GcrrEi4kmTJ2/R3fqX2ng2pA0Z zvOOh0p9p6a0px5krAJv6hyRgADrOxqwp+T9kxB4FJIo81YVmH4nzPnEOHoS8GK1VYxh keKiyYxMmT2Udj8cTfU4pVChTccbeDbfGgMxpU5eLo6fqRmlwTxWlrwGBJJEgVte8i+r ABz2V2bczRRhMaksHZvJispxS5TB5jLoxWQS/PdP0Ty3akKHEn6Ezf1iPci124WWNiBi ZU/fyD4ul82HgCmobhbp2/QtZ3zT828CvAZjl15BySthq6PE4krv70OhSzE0KxAVUR3J KOWA== X-Received: by 10.194.48.102 with SMTP id k6mr8089290wjn.124.1443656056909; Wed, 30 Sep 2015 16:34:16 -0700 (PDT) Received: from ?IPv6:2605:6000:f686:8500:b42d:8b4:2d9f:67c3? ([2605:6000:f686:8500:b42d:8b4:2d9f:67c3]) by smtp.gmail.com with ESMTPSA id gt4sm27856wib.21.2015.09.30.16.34.15 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 30 Sep 2015 16:34:16 -0700 (PDT) Subject: Re: hot standby, how to disable WAL archiving ? To: Roman Shubovich , Keith References: Cc: "pgsql-admin@postgresql.org" From: John Scalia X-Enigmail-Draft-Status: N1110 Message-ID: <560C7170.9040409@gmail.com> Date: Wed, 30 Sep 2015 18:34:08 -0500 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Thunderbird/38.2.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------050703010103060706010005" X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------050703010103060706010005 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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 dir= ectory and see if any gaps in filenames appear. You should see an orderly progression in names that fol= low a hex pattern. If you do see some old files that have gaps in the nam= ing sequence, those should be able to be safely removed. Also, if you've restarted your database, the new wa= l_keep_segments setting should be active, you can confirm in your databas= e by entering "show wal_keep_segments;" You can then safely remove any files provided you sti= ll keep the last /n /files where /n/ =3D the value reported by the show c= ommand. FYI, checkpoint_timeout only instructs postgresql to write a checkpoint i= nto the WAL file after this amount of time has elapsed, otherwise based o= n transaction volume, a checkpoint will be recorded every /n /WAL files provided the elapsed time has not ec= lipsed 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 runti= me and I want manage of this behavior > at this time i can see that standby server ignores all of WAL parameter= s 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 man= y 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 >: > > > > On Wed, Sep 30, 2015 at 8:16 AM, John Scalia > wrote: > > Hi Roman, > > Maybe one of the developers can chime in on this, but to my kno= wledge, as at least one WAL file should be available for the server to su= ccessfully start, you can't turn > these off entirely. My suggestion would be to set wal_keep_segm= ents 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 de= fault is 16MB. You can have this set to a much larger value. > -- > Jay > > On Tue, Sep 29, 2015 at 6:01 PM, Roman Shubovich > 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 =3D 64 > checkpoint_segments =3D 32 > checkpoint_timeout =3D 1h > checkpoint_completion_target =3D 0.8 > WAL archiving is off > > master host works as expected - he keeps about 140-160 file= s 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, recov= ery_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 c= annot disable this completely. Those are the WAL files that get written t= o 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= =2Ehtml 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 o= n by default and you don't mention having those settings turned on. > > The archive cleanup commands are for cleaning up secondary WAL file= s sent over by the master's archive_command, not for cleaning up the file= s in pg_xlogs. > > With wal_keep_settings set to 64, you'll always, at a minimum, have= 16MB*64 =3D 1GB of WAL files in the pg_xlogs folder. If you have heavy w= rites going to the database this > amount could certainly be higher. Is there a reason you have the ch= eckpoint_timeout set to 1 hr? That could also be the reason you have addi= tional 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 understan= d 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 r= eally have a good reason.=20 > > 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 r= eason, it has to use the files in > the pg_xlogs folder on your master to catch back up again. Since yo= u're on 9.4, though, you can look into setting up a replication slot to a= void this issue as well and lower > the wal_keep_segments value. http://www.postgresql.org/docs/9.4/st= atic/warm-standby.html#STREAMING-REPLICATION-SLOTS > > Keith > > --------------050703010103060706010005 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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>:


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


--------------050703010103060706010005--