Hello,
I played around a bit with the logical replication in 10.0 beta 1.
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