Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1cenIs-0004Ze-5k for pgsql-admin@arkaria.postgresql.org; Fri, 17 Feb 2017 18:30:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1cenIr-0000mC-Ox for pgsql-admin@arkaria.postgresql.org; Fri, 17 Feb 2017 18:30:45 +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_2) (envelope-from ) id 1cenIq-0000ia-Ow for pgsql-admin@postgresql.org; Fri, 17 Feb 2017 18:30:44 +0000 Received: from omr1.cc.ipv6.vt.edu ([2607:b400:92:8300:0:c6:2117:b0e] helo=omr1.cc.vt.edu) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1cenIo-0001uD-8g for pgsql-admin@postgresql.org; Fri, 17 Feb 2017 18:30:44 +0000 Received: from mr2.cc.vt.edu (mr2.cc.ipv6.vt.edu [IPv6:2607:b400:92:8400:0:90:e077:bf22]) by omr1.cc.vt.edu (8.14.4/8.14.4) with ESMTP id v1HIUdnH011551 for ; Fri, 17 Feb 2017 13:30:39 -0500 Received: from mail-qk0-f198.google.com (mail-qk0-f198.google.com [209.85.220.198]) by mr2.cc.vt.edu (8.14.7/8.14.7) with ESMTP id v1HIUXu6024394 for ; Fri, 17 Feb 2017 13:30:39 -0500 Received: by mail-qk0-f198.google.com with SMTP id u25so41925554qki.3 for ; Fri, 17 Feb 2017 10:30:39 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-transfer-encoding; bh=nBkmYJOBNNSUk7HsRPnUmboCqC0PYoWvsmv3gXsxfQ0=; b=F+PklsDcNDDL/7/Tzd2j5yutMzZdIjMnSmE2ElQlQWWtCQKM7NHT3htYYOP0mxlqNy h4CMijsqikPyvMD/scnB23sBEHFLfqU30vn4oaEeFPCzEYfr77Kdipy4szmr0Ag7PFXN DLeaVjkR20jUs+HP7toL3xv8FunDF/NU/YzfMK0AhuvECCDOvA7f3e+JJKZsQMraXKCK lryJGhYt98E6f/S2BQQxqfzjqaueUPRjUhlqtPMvbIJoozZ5TJR02039wzWhINRHraE+ uaHlfSyUbsUzgdfG1QN2duYKKvXe6TdlBss35HFU8d1zL9lvK2pIeaqw94fWaKR12VbV B1EA== X-Gm-Message-State: AMke39kdd5yonCet7CZ7v93ZcPCmDS05Xiv7RAWSxL41oXNeFzQ/UFa1QMzIS7CSW4FU+6v8qZ3gKliF8p6o08VIEFnyqxqvJ3m/KIa8CDc5E8fFY4AokKOC2/p/ysILqkbsZMt/1vtfJDioJSzdZtrbsU9RofHnVNVt6Kst5sCaAkW4/g66HY+6U6kRVP77UGsaeso= X-Received: by 10.55.135.1 with SMTP id j1mr9340750qkd.107.1487356233678; Fri, 17 Feb 2017 10:30:33 -0800 (PST) X-Received: by 10.55.135.1 with SMTP id j1mr9340737qkd.107.1487356233483; Fri, 17 Feb 2017 10:30:33 -0800 (PST) Received: from stell.cns.vt.edu ([2001:468:c80:2102:6969:3029:df01:af92]) by smtp.gmail.com with ESMTPSA id w41sm6997740qtw.34.2017.02.17.10.30.33 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 17 Feb 2017 10:30:33 -0800 (PST) To: pgsql-admin@postgresql.org From: Ray Stell Subject: WAL archive space planning? Message-ID: Date: Fri, 17 Feb 2017 13:30:32 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:45.0) Gecko/20100101 Thunderbird/45.7.1 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, score=1.0 required=5.0 tests=RCVD_IN_DNSWL_NONE, RCVD_IN_MSPIKE_H3,RCVD_IN_MSPIKE_WL,SPF_SOFTFAIL autolearn=disabled version=3.4.0 X-Spam-Checker-Version: SpamAssassin 3.4.0 (2014-02-07) on mr2.cc.vt.edu X-Pg-Spam-Score: -4.2 (----) 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 I was "planning" to turn on WAL archiving on a postgresql 9.4.11 server that currently is running with "wal_level=hot_standby" and streaming to a standby. I thought there would be a relationship between the rate of pg_xlog files and archive generation. When I turned up the archive_command/mode I found the scale of the archive target was wrong as I had based it on the pg_xlog file creation rate. When I turned on the archive command for a few minutes, pg_xlog dir contained these files for the time period: -rw------- 1 postgres postgres 16777216 Feb 17 06:47 00000001000023AC0000007F -rw------- 1 postgres postgres 16777216 Feb 17 06:45 00000001000023AC0000007E The archive command wrote 126, 16MB files: -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000056 -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000055 -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000054 -rw------- 1 postgres postgres 16777216 Feb 17 06:47 000000010000237700000053 ... -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DC -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DB -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000DA -rw------- 1 postgres postgres 16777216 Feb 17 06:45 0000000100002376000000D9 On servers that are not nearly as busy, I observe a one-to-one relationship between these files/rates. Is there a good WAL archive space planning guide? Is the way to collect planning data for this to turn on wal_debug? TIA! -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin