Re: logical replication read-only slave

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