Thread: [HACKERS] logical replication read-only slave
Hello,
My first question was: is it possible to set the "slave" server to run in (almost) read-only mode?
The current setup is the following:
There is a Rails application running on multiple servers
Two PostgreSQL servers, stream replication
Writes are executed on the master
Some of the reads are executed on the slave
(Nothing new here)
However, it provides me a safety net that I could not execute writes on the slave by accident. Not only I couldn’t do it, I would also receive a notification from the software about the attempt as it would throw an exception.
Let’s say I would switch to logical replication of all tables
Safety net is gone
I could send an explicit command for each session to make it read-only
I could use a read-only role (let’s ignore now I don’t use rules)
But the main attribute of a safety net is the safety. As soon as there would be a bug, and a session would not send the "set session ..." command, or the wrong role would be used, the application could write to the "slave", and that’s not great.
As far as I see, the only solution which provides the same safety level as the stream replication does would be starting up the "slave" in read-only mode. In this case, writes would be needed for:
* The replication
* DDL
The DDL could be applied in a specific session as whitelisting is safer than blacklisting. I think the only missing part is if the subscription could turn on the writes for itself.
If you think this would make sense, please consider it.
M
On 6/15/17 11:12, Maeldron T. wrote: > However, it provides me a safety net that I could not execute writes on > the slave by accident. Not only I couldn’t do it, I would also receive a > notification from the software about the attempt as it would throw an > exception. > > Let’s say I would switch to logical replication of all tables > Safety net is gone You can change the permissions on your slave so that you cannot write to the tables. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 15 June 2017 at 23:12, Maeldron T. <maeldron@gmail.com> wrote: > I could send an explicit command for each session to make it read-only > I could use a read-only role (let’s ignore now I don’t use rules) You can also set the GUC default_transaction_read_only = on. But apps can easily clobber that with explicit read/write begin. Setting it in combination with a role that doesn't have any write permissions would be sufficient for most practical situations IMO. > The DDL could be applied in a specific session as whitelisting is safer than > blacklisting. I think the only missing part is if the subscription could > turn on the writes for itself. > > If you think this would make sense, please consider it. BDR has the option of marking a node as read-only, which is implemented using an ExecutorStart_hook. It probably wouldn't be overly hard to do the same thing as a standalone extension. You'd want to detect when you were running within a logical replication apply worker and permit changes then, but I don't expect that'd be unduly hard. It'd be nice to have a built-in way to do this, so maybe you could pursue that for postgresql 11, raising a firm design idea here and following up with a patch if you get a reasonable approximation of consensus. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services