PostgreSQL 只读从库上创建索引

PostgreSQL About 1,080 words

说明

以下操作没有特殊说明均在从库中执行。

会话级别复制角色

DEFAULT:标准行为,所有写操作记录到WAL日志中并复制到从库。
REPLICA:在从库中使用,禁止普通事务中的写操作,但DDL操作可以通过。
ORIGIN:使用于触发器,处理数据变更事件

show session_replication_role;

允许临时写操作

CREATE INDEX CONCURRENTLY允许索引在不锁定表的情况下创建,从而不会影响读取操作。

SET session_replication_role = 'replica';
CREATE INDEX CONCURRENTLY your_index_name ON your_table_name (your_column_name);
SET session_replication_role = 'origin';

原理

利用了Replication Role的机制在从库上创建了索引,不会在WALWrite-Ahead Logging)日志中记录这项变更。

查看主从状态

在主库上执行

SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn 
FROM pg_stat_replication;

在从库上执行

SELECT pg_last_wal_replay_lsn();

删除索引

可以包裹在事务中。

注意:CREATE INDEX CONCURRENTLY不能包裹在事务中。

BEGIN;

-- 临时禁用只读限制
SET session_replication_role = 'replica';

-- 删除索引
DROP INDEX your_index_name;

-- 恢复默认设置
SET session_replication_role = 'origin';

COMMIT;

更多文章

PostgreSQL 16 编译安装:https://www.zhangbj.com/p/1699.html

PostgreSQL 搭建主从同步实现读写分离:https://www.zhangbj.com/p/1795.html

PostgreSQL 搭建级联从库:https://www.zhangbj.com/p/1796.html

PostgreSQL 参数调整作为 OLAP 统计数据库:https://www.zhangbj.com/p/1797.html

Views: 90 · Posted: 2024-11-20

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓


Today On History
Browsing Refresh