PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁

背景

在一些场景中,可能要将数据库设置为只读模式。

例如,

1、云数据库,当使用的容量超过了购买的限制时。切换到只读(锁定)模式,确保用户不会用超。

2、业务上需要对数据库进行迁移,准备割接时,可将主库切换到只读(锁定),确保绝对不会有事务写入。

锁定的实现方法有若干种。

1、硬锁定,直接将数据库切换到恢复模式,绝对不会有写操作出现。

2、软锁定,设置default_transaction_read_only为on,默认开启的事务为只读事务。用户如果使用begion transaction read write可破解。

3、内核层面改进的锁定,对于云上产品,锁定后实际上是期望用户升级容量,或者用户可以上去删数据使得使用空间降下来的。那么以上两种锁定都不适用,需要禁止除truncate, drop操作以外的所有操作的这种锁定方式。而且最好是不需要重启数据库就可以实现。

实现

1 锁定实例

硬锁定

1、配置 recovery.conf

recovery_target_timeline = 'latest'  
standby_mode = on  

2、重启数据库

pg_ctl restart -m fast  

3、硬锁定,不可破解

postgres=# select pg_is_in_recovery();  
 pg_is_in_recovery   
-------------------  
 t  
(1 row)  
  
postgres=# insert into t1 values (1);  
ERROR:  cannot execute INSERT in a read-only transaction  
  
postgres=# begin transaction read write;  
ERROR:  cannot set transaction read-write mode during recovery  

软锁定

1、设置default_transaction_read_only

postgres=# alter system set default_transaction_read_only=on;  
ALTER SYSTEM  

2、重载配置

postgres=# select pg_reload_conf();  
 pg_reload_conf   
----------------  
 t  
(1 row)  

3、所有会话自动进入read only的默认事务模式。

reload前

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 off  
(1 row)  

reload后

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 on  
(1 row)  
  
postgres=# insert into t1 values (1);  
ERROR:  cannot execute INSERT in a read-only transaction  

4、软锁定可破解

postgres=# begin transaction read write;  
BEGIN  
postgres=# insert into t1 values (1);  
INSERT 0 1  
postgres=# end;  
COMMIT  

2 解锁实例

硬解锁

1、重命名recovery.conf到recovery.done

cd $PGDATA  
  
mv recovery.conf recovery.done  

2、重启数据库

pg_ctl restart -m fast  

软解锁

1、设置default_transaction_read_only

postgres=# alter system set default_transaction_read_only=off;  
ALTER SYSTEM  

2、重载配置

postgres=# select pg_reload_conf();  
 pg_reload_conf   
----------------  
 t  
(1 row)  

3、所有会话自动进入read only的默认事务模式。

reload前

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 on  
(1 row)  

reload后

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 off  
(1 row)  
  
写恢复  
postgres=# insert into t1 values (1);  
INSERT 0 1  

内核层锁定

通过修改内核实现锁定,锁定后只允许:

1、truncate

2、drop

这样,用户可以在锁定的情况下进行数据清理,可以跑任务的形式,检查数据是否清理干净,进行解锁设置。

阿里云RDS PG已支持。

参考

https://www.postgresql.org/docs/11/recovery-config.html

https://www.postgresql.org/docs/11/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-STATEMENT

https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

PostgreSQL 许愿链接

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

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

PostgreSQL 解决方案集合

上一篇:PolarDB 为什么要解决FPW的性能问题?


下一篇:JavaScript 时间与日期