public inbox for [email protected]help / color / mirror / Atom feed
serializable master and non-serializable hot standby: feasible set up? 4+ messages / 3 participants [nested] [flat]
* serializable master and non-serializable hot standby: feasible set up? @ 2024-10-15 23:27 Jacob Biesinger <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Jacob Biesinger @ 2024-10-15 23:27 UTC (permalink / raw) To: [email protected] Howdy! I've been going back and forth with the GCP CloudSQL engineering team about the feasibility of a particular setup, and I'm pinging the list here hoping for a sanity check. They assure me that it's impossible and I think they must be mistaken, but I have limited experience administrating my own postgres instances. So I'm appealing to a higher authority :) The docs outline[1 <https://www.postgresql.org/docs/current/hot-standby.html;][2 <https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY;] that a hot standby / replica with the flag `default_transaction_isolation='serializable'` is going to cause issues -- while you can connect to such an instance, basically every query against it will fail. But! If you can somehow manage to get the replica's flags to instead use `repeatable read` or `read committed` isolation, everything seems to work well, even if the master uses `serializable` isolation. In GCP, we are having to temporarily swap the master to a lower isolation level, then stand up the replica and pin the lower isolation level flag, and then finally revert the flag change on the master. If the replica goes down, we have to repeat this process and it's a pain (not to mention data issues since our app relies on this isolation level instead of doing explicit locking in most cases). So I know this is an awkward question to post here, but as postgres admin professionals, *would you* expect to be able to stand up a `repeatable read` replica against a `serializable` master? My expectation is that you'd simply change the setting in a .conf file on the replica and be good to go; is there something that would make this process really difficult / impossible? Thanks so much! [1]: https://www.postgresql.org/docs/current/hot-standby.html [2]: https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY -- Jake Biesinger ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: serializable master and non-serializable hot standby: feasible set up? @ 2024-10-16 03:32 Clay Jackson (cjackson) <[email protected]> parent: Jacob Biesinger <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Clay Jackson (cjackson) @ 2024-10-16 03:32 UTC (permalink / raw) To: Jacob Biesinger <[email protected]>; [email protected] <[email protected]> Just out of curiosity, what's the use case for this? Clay Jackson Database Solutions Sales Engineer [cid:[email protected]]<https://www.quest.com/solutions/database-performance-monitoring/; [email protected]<mailto:[email protected]> office 949-754-1203 mobile 425-802-9603 From: Jacob Biesinger <[email protected]> Sent: Tuesday, October 15, 2024 4:28 PM To: [email protected] Subject: serializable master and non-serializable hot standby: feasible set up? CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe. Howdy! I've been going back and forth with the GCP CloudSQL engineering team about the feasibility of a particular setup, and I'm pinging the list here hoping for a sanity check. They assure me that it's impossible and I think they must be mistaken, but I have limited experience administrating my own postgres instances. So I'm appealing to a higher authority :) The docs outline[1<https://www.postgresql.org/docs/current/hot-standby.html;][2<https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY;] that a hot standby / replica with the flag `default_transaction_isolation='serializable'` is going to cause issues -- while you can connect to such an instance, basically every query against it will fail. But! If you can somehow manage to get the replica's flags to instead use `repeatable read` or `read committed` isolation, everything seems to work well, even if the master uses `serializable` isolation. In GCP, we are having to temporarily swap the master to a lower isolation level, then stand up the replica and pin the lower isolation level flag, and then finally revert the flag change on the master. If the replica goes down, we have to repeat this process and it's a pain (not to mention data issues since our app relies on this isolation level instead of doing explicit locking in most cases). So I know this is an awkward question to post here, but as postgres admin professionals, *would you* expect to be able to stand up a `repeatable read` replica against a `serializable` master? My expectation is that you'd simply change the setting in a .conf file on the replica and be good to go; is there something that would make this process really difficult / impossible? Thanks so much! [1]: https://www.postgresql.org/docs/current/hot-standby.html [2]: https://www.postgresql.org/docs/current/applevel-consistency.html#SERIALIZABLE-CONSISTENCY -- Jake Biesinger Attachments: [image/jpeg] image001.jpg (38.5K, 3-image001.jpg) download | view image ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: serializable master and non-serializable hot standby: feasible set up? @ 2024-10-16 04:23 Laurenz Albe <[email protected]> parent: Jacob Biesinger <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Laurenz Albe @ 2024-10-16 04:23 UTC (permalink / raw) To: Jacob Biesinger <[email protected]>; [email protected] On Tue, 2024-10-15 at 16:27 -0700, Jacob Biesinger wrote: > *would you* expect to be able to stand up a `repeatable read` replica against a > `serializable` master? My expectation is that you'd simply change the setting in > a .conf file on the replica and be good to go; is there something that would make > this process really difficult / impossible? I expect that to work fine, at least I cannot think of a problem with such a setup. But I have been wrong before, so test it. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: serializable master and non-serializable hot standby: feasible set up? @ 2024-10-16 19:28 Jacob Biesinger <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Jacob Biesinger @ 2024-10-16 19:28 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] On Tue, Oct 15, 2024 at 9:23 PM Laurenz Albe <[email protected]> wrote: > On Tue, 2024-10-15 at 16:27 -0700, Jacob Biesinger wrote: > > *would you* expect to be able to stand up a `repeatable read` replica > against a > > `serializable` master? My expectation is that you'd simply change the > setting in > > a .conf file on the replica and be good to go; is there something that > would make > > this process really difficult / impossible? > > I expect that to work fine, at least I cannot think of a problem with such > a setup. > But I have been wrong before, so test it. > The setup (serializable master, repeatable read replica) definitely works -- we've been running that way for over a year now. I guess I'm really asking "how would you go about getting the replica into the appropriate state?" Would you expect to have to downgrade the master's isolation level as I describe? Or would you expect to be able to stand up the replica using a modified conf file initially? Thanks as always for your help! ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-16 19:28 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-10-15 23:27 serializable master and non-serializable hot standby: feasible set up? Jacob Biesinger <[email protected]> 2024-10-16 03:32 ` Clay Jackson (cjackson) <[email protected]> 2024-10-16 04:23 ` Laurenz Albe <[email protected]> 2024-10-16 19:28 ` Jacob Biesinger <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox