POSTGRESQL 的“只读”模式

开源软件联盟PostgreSQL分会 2022-01-29

6027

译者：魏波

中国PG分会培训认证执行总监、资深数据库工程师，十多年的数据库运维管理及培训经验，掌握PostgreSQL架构部署、性能优化等，致力于推动PostgreSQL在中国的发展。

作者：乔纳森·卡茨（JONATHAN KATZ）

2022 年 1 月 23 日，星期日 2分钟阅读

当讨论PostgreSQL数据库的“只读”连接时，通常是指连接到副本节点。

有多种方法可用于路由只读查询的连接（即带有SELECT语句的查询……不调用修改数据的VOLATILE函数）。包括像Pgpool-II这样的连接代理软件或像Django的数据库路由器这样的框架机制。

但是，在某些情况下，您可能需要强制只读连接到主实例（可读写）。包括一些示例将您的应用程序降级，以执行数据库移动或升级；或允许管理员检查可能正在累积跟踪所有更改WAL的系统。

PostgreSQL有一个配置参数：default_transaction_read_only。

default_transaction_read_only全局设置为on，强制所有连接禁止写入数据库。default_transaction_read_only是一个可重新加载的参数，因此无需重新启动实例即可使用它。

简单示例：

首先，确保系统没有设置default_transaction_read_only：

 postgres=# SHOWdefault_transaction_read_only ;
default_transaction_read_only
-------------------------------
Off
(1 row)
 
postgres=# CREATE TABLE IF NOT EXISTS abc(id int); INSERT INTO abc VALUES (1) RETURNING id;
 
CREATE TABLE
 id
---- 
1
(1 row)
INSERT 0 1

按预期工作：我们能够创建一个表并插入数据。现在让我们将系统置于default_transaction_read_only模式（注意：我在PostgreSQL 14进行测试）

ALTER SYSTEM SET default_transaction_read_only TO on;
SELECT pg_reload_conf();
SHOW default_transaction_read_only;

确保default_transaction_read_only已启用：

postgres=# SHOW default_transaction_read_only; 
default_transaction_read_only
------------------------------- 
On
(1 row)

现在验证不允许写入：

postgres=# INSERT INTO abc VALUES (2)RETURNING id;
ERROR: cannot execute INSERT in a read-only transaction

测试OK！

但default_transaction_read_only不是万能药，请注意以下事项：

首先，即使该值是在数据库范围内设置的，default_transaction_read_only仍可以在会话中覆盖。

例如：

postgres=# SHOWdefault_transaction_read_only ;
default_transaction_read_on
-------------------------------
 on
(1 row)
 
postgres=# SETdefault_transaction_read_only TO off;
SET
postgres=# INSERT INTO abc VALUES (2)RETURNING id;
Id
---- 
2
(1 row)
INSERT 0 1