Feedback-ID: i76984098:Fastmail
Message-ID: <1628637f-419f-4f6a-9cb6-07af90cd0bc4@aklaver.com>
Date: Tue, 4 Jun 2024 16:05:08 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Questions on logical replication
To: Koen De Groote <kdg.dev@gmail.com>,
 PostgreSQL General <pgsql-general@lists.postgresql.org>
References: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAGbX52FxL9eR=jmS3ACgRC=tEm=xj_xMmFoOxO+wVA+oURW=kA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/1628637f-419f-4f6a-9cb6-07af90cd0bc4%40aklaver.com>
Precedence: bulk

On 6/4/24 15:55, Koen De Groote wrote:
> I recently read the entire documentation on logical replication, but am 
> left with a question on the buildup of WAL
> 
> On this page: 
> https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT <https://www.postgresql.org/docs/current/logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT>
> 
> It is written: " When dropping a subscription, the remote host is not 
> reachable. In that case, disassociate the slot from the subscription 
> using |ALTER SUBSCRIPTION| before attempting to drop the subscription. 
> If the remote database instance no longer exists, no further action is 
> then necessary. If, however, the remote database instance is just 
> unreachable, the replication slot (and any still remaining table 
> synchronization slots) should then be dropped manually; otherwise 
> it/they would continue to reserve WAL and might eventually cause the 
> disk to fill up. Such cases should be carefully investigated."
> 
> 
> Assuming a situation where I add tables 1 at a time to the publisher, 
> and refresh the subscription every time.
> 
> What happens if I shut down the subscriber database for a while? The 
> subscription isn't dropped, so am I reading it right that the disk on 
> the publisher will slowly be filling up with WAL? Isn't that always the 
> case if wall is enabled?

https://www.postgresql.org/docs/current/wal-configuration.html

"Checkpoints are points in the sequence of transactions at which it is 
guaranteed that the heap and index data files have been updated with all 
information written before that checkpoint. At checkpoint time, all 
dirty data pages are flushed to disk and a special checkpoint record is 
written to the WAL file. (The change records were previously flushed to 
the WAL files.) In the event of a crash, the crash recovery procedure 
looks at the latest checkpoint record to determine the point in the WAL 
(known as the redo record) from which it should start the REDO 
operation. Any changes made to data files before that point are 
guaranteed to be already on disk. Hence, after a checkpoint, WAL 
segments preceding the one containing the redo record are no longer 
needed and can be recycled or removed. (When WAL archiving is being 
done, the WAL segments must be archived before being recycled or removed.)"

> 
> This "cause disk to fill up" warning is quite concerning, and I'd like 
> to understand what could cause it and how likely it is? I thought 
> logical replication uses WAL by default, so doesn't that mean there has 
> to be a log of changes kept anyhow? Even if the WAL isn't written to 
> disk by an "archive_command"?

https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS

"Replication slots provide an automated way to ensure that the primary 
does not remove WAL segments until they have been received by all 
standbys, and that the primary does not remove rows which could cause a 
recovery conflict even when the standby is disconnected."

When you set up logical replication you are 'asking' via the replication 
slot that WAL records be kept on the publisher until the subscriber 
retrieves them.

> 
> Regards,
> Koen De Groote

-- 
Adrian Klaver
adrian.klaver@aklaver.com