作者
digoal
日期
2019-01-30
标签
PostgreSQL , 只读 , 锁定 , readonly , recovery.conf , 恢复模式 , pg_is_in_revoery , default_transaction_read_only
背景
在一些场景中，可能要将数据库设置为只读模式。
例如，
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 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
