Re: [HACKERS] make async slave to wait for lsn to be replayed - Mailing list pgsql-hackers

From Kartyshov Ivan
Subject Re: [HACKERS] make async slave to wait for lsn to be replayed
Date
Msg-id b460b4b6aeca68fbe2b0c705d82bb60e@postgrespro.ru
Whole thread Raw
In response to Re: [HACKERS] make async slave to wait for lsn to be replayed  (Alexander Korotkov <aekorotkov@gmail.com>)
Responses Re: [HACKERS] make async slave to wait for lsn to be replayed
List pgsql-hackers
Intro
==========
The main purpose of the feature is to achieve
read-your-writes-consistency, while using async replica for reads and
primary for writes. In that case lsn of last modification is stored
inside application. We cannot store this lsn inside database, since
reads are distributed across all replicas and primary.


Procedure style implementation
==========
https://www.postgresql.org/message-id/27171.1586439221%40sss.pgh.pa.us
https://www.postgresql.org/message-id/20210121.173009.235021120161403875.horikyota.ntt%40gmail.com

CALL pg_wait_lsn(‘LSN’, timeout);

Examples
==========

primary         standby
-------         --------
              postgresql.conf
              recovery_min_apply_delay = 10s


CREATE TABLE tbl AS SELECT generate_series(1,10) AS a;
INSERT INTO tbl VALUES (generate_series(11, 20));
SELECT pg_current_wal_lsn();


              CALL pg_wait_lsn('0/3002AE8', 10000);
              BEGIN;
              SELECT * FROM tbl; // read fresh insertions
              COMMIT;

Fixed and ready to review.

-- 
Ivan Kartyshov
Postgres Professional: www.postgrespro.com
Attachment

pgsql-hackers by date:

Previous
From: Greg Sabino Mullane
Date:
Subject: Re: Possibility to disable `ALTER SYSTEM`
Next
From: Daniel Gustafsson
Date:
Subject: Re: Possibility to disable `ALTER SYSTEM`