Re: [patch] demote

On Thu, Jun 18, 2020 at 6:02 AM Jehan-Guillaume de Rorthais
<jgdr(at)dalibo(dot)com> wrote:
> At some expense, Admin can already set the system as readonly from the
> application point of view, using:
>
> alter system set default_transaction_read_only TO on;
> select pg_reload_conf();
>
> Current RW xact will finish, but no other will be allowed.

That doesn't block all WAL generation, though:

rhaas=# alter system set default_transaction_read_only TO on;
ALTER SYSTEM
rhaas=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
rhaas=# cluster pgbench_accounts_pkey on pgbench_accounts;
rhaas=#

There's a bunch of other things it also doesn't block, too. If you're
trying to switch to a new primary, you really want to stop WAL
generation completely on the old one. Otherwise, you can't guarantee
that the machine you're going to promote is completely caught up,
which means you might lose some changes, and you might have to
pg_rewind the old master.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company