Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyymc-0006wW-6l for pgsql-admin@arkaria.postgresql.org; Wed, 27 Feb 2019 12:57:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gyymZ-0000d6-Ov for pgsql-admin@arkaria.postgresql.org; Wed, 27 Feb 2019 12:57:55 +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_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyymZ-0000cy-Gl for pgsql-admin@lists.postgresql.org; Wed, 27 Feb 2019 12:57:55 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyymX-0000I4-28 for pgsql-admin@lists.postgresql.org; Wed, 27 Feb 2019 12:57:55 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.15.2/8.15.2/Debian-8) with ESMTPS id x1RCvkIc030668 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Wed, 27 Feb 2019 14:57:51 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id x1RCvkex070941 for ; Wed, 27 Feb 2019 14:57:46 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: Logical replication and wal segment retention To: pgsql-admin@lists.postgresql.org References: <695C083D-AFB5-431B-B022-ED442CE94794@gmail.com> <20190227125242.6ixy4jkzcuj55cdl@vault.lan> From: Achilleas Mantzios Message-ID: <59567e0a-78e4-ed1c-f5b1-033aafcfb68a@matrix.gatewaynet.com> Date: Wed, 27 Feb 2019 14:57:46 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.3.2 MIME-Version: 1.0 In-Reply-To: <20190227125242.6ixy4jkzcuj55cdl@vault.lan> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US X-Virus-Scanned: clamav-milter 0.100.2 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-28.9 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_NAUTICAL,TW_PG autolearn=unavailable autolearn_force=no version=3.4.2 X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 27/2/19 2:52 μ.μ., Johannes Truschnigg wrote: > Hi Jay, > > On Wed, Feb 27, 2019 at 07:40:26AM -0500, John Scalia wrote: >> Hello, folks, >> >> Yesterday, I had a small file system fill up, due to some logical >> replication testing we had been performing. We had been testing IBM’s IIDR >> system and apparently it had built a logical replication slot on my server. >> When the test was completed, nobody removed the slot, so WAL segments >> stopped being dropped. Now I can understand the difficulty separating what >> physical versus logical replication needs from the WAL segments, but as >> logical replication is database specific not cluster wide, this behavior was >> a little unexpected, since the WAL segments are cluster wide. Are WAL >> segments going to pile up whenever something drops a logical replication >> connection? I’ve seen it, but it seems like this could be a bad thing. > Since Logical Replication is piggybacked on Physical Replication, you cannot > use the first without having the latter. And yes, what you experienced is one > of the dangers of using replication slots when having a busy database (i.e. > producing lots of WAL) and a filesystem with little excess space. Under these > circumstances, it is imperative to monitor for (and alert on) anything going > awry with your replication slot consumers, and/or the size of your wal/xlog > directory. It's a feature of replication slots to work that way - but one that > may end up biting you. A logical approach for replication slots would be to accept a parameter regarding max WAL files to retain, after which newer WALs will be removed and the primary server saved.  Pretty much like : --archive-push-queue-max argument of pgbackrest . > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt