Thread: log shipping with pg_receivewal
Hi,
postgres 13 question,
if I setup pg_receivewal from a primary instance to a given directory, and then setup a standby with a restore_command pointing to that directory,, the changes on the primary are applied on the standby at each wal change. And this, even if I run pg_receivewal with the --synchronous option.
My question: as the synchronous option is supposed to make pg_receivewal write transaction immediately in the wal files, is there a way to ask the standby to apply them on the fly ie. without waiting a wal file change ?
thanks,
On Tue, Dec 14, 2021 at 05:25:04AM +0100, Marc Millas wrote: > My question: as the synchronous option is supposed to make pg_receivewal > write transaction immediately in the wal files, is there a way to ask the > standby to apply them on the fly ie. without waiting a wal file change ? Nope, there is no such option. First, pg_receivewal strongly relies on the rename of WAL segment to its correct name once completed. If there is any failure, we would start back at the beginning of the last partial segment found. Saying that, you could do things by using a restore_command that checks after a .partial file directly in the path used by pg_receivewal to store the archives, and Postgres would repeatedly ask for a segment once it is out. But really, this is going to be really expensive as a restore_command would copy a full file, so that's a lot of bandwidth wasted away for nothing. Streaming replication would be likely your best, and cheapest, option here. -- Michael
Attachment
On Tue, Dec 14, 2021 at 9:55 AM Marc Millas <marc.millas@mokadb.com> wrote: > > Hi, > postgres 13 question, > > if I setup pg_receivewal from a primary instance to a given directory, and then setup a standby with a restore_commandpointing to that directory,, the changes on the primary are applied on the standby at each wal change. Andthis, even if I run pg_receivewal with the --synchronous option. > > My question: as the synchronous option is supposed to make pg_receivewal write transaction immediately in the wal files,is there a way to ask the standby to apply them on the fly ie. without waiting a wal file change ? I don't think that can be done, actually, the wal_receiver process is running in the same cluster so we can interact with that process and know exactly up to what LSN it has flush and what wal data is valid to be applied and wal receiver also continues to append more WAL to the same file. But pg_receivewal is a separate utility so this can serve as an archive location that means we can restore the complete WAL file only. -- Regards, Dilip Kumar EnterpriseDB: http://www.enterprisedb.com