Re: [HACKERS] logical replication read-only slave

[Craig Ringer]

On 15 June 2017 at 23:12, Maeldron T.  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] logical replication read-only slave

[Peter Eisentraut]

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] logical replication read-only slave

[Maeldron T.]

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