PostgreSQLreadonlystandby只读库如何写数据?DDLDML操作透明传输到主库

作者

digoal

日期

2020-03-26

标签

PostgreSQL , 主库 , 只读库 , 透明传输 , ddl , dml

背景

https://github.com/citusdata/pgmasq

只读库当分析库使用时, 分析通常比较复杂, 会有中间结果, 中间结果如何保持?是个问题, 因为只读库是只读的, 无法保存数据, pgmasq通过在只读库实现透明ddl dml传输到主库, 实现了只读库的写, 同时只读库会及时收到写结果.

Pgmasq is a PostgreSQL extension that transparently forwards transactions from a hot standby to a primary to enable DDL and DML from any node, while providing a means of offloading some SELECTs to a standby.

Pqmasq is currently a prototype, with some important limitations.

Compile the extension from source:

git clone https://github.com/citusdata/pgmasq.git
cd pgmasq
make && sudo PATH=$PATH make install

Add the following to postgresql.conf:

shared_preload_libraries = 'pgmasq'
pgmasq.execute_locally = immutable # one of: none, immutable, selects
lock_timeout = '2s'

The pgmasq.execute_locally setting controls which queries are executed locally on the standby. The following settings are supported:

• none forwards all commands to the primary.


• immutable executes selects outside of a transactino block that contain no functions that could modify the database on the standby.


• selects executes all selects outside of a transaction block on the standby.

Additionally, we recommend setting synchronous_commit = remote_apply on the primary to provide read-your-writes consistency.

To enable pgmasq in a particular database, run CREATE EXTENSION pgmasq in that database on the primary.

For pgmasq to work your replication user also needs to be superuser. You can make that the case by running:

ALTER USER replicator SUPERUSER;

In addition, you may need to add a line to pg_hba.conf to ensure access to the database, in addition to replication access:

host replication replicator 10.0.0.0/8 md5
host all replicator 10.0.0.0/8 md5

CREATE TEMPORARY TABLE is not supported as the parser on the hot standby cannot see the temporary table.

Multi-statement transactions of the form BEGIN; [DDL on table X]; [SELECT/DML on table X]; COMMIT; may create an undetectable deadlock, since the DDL will take an exclusive lock when it gets replicated, which will cause the parser to block when processing the SELECT/DML.

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.