PostgreSQL — 数据库实例只读锁定

目录

PostgreSQL 数据库实例只读锁定

在一些场景中,可能要将数据库设置为只读模式。例如:需要对数据库进行迁移,准备割接时,首先要将主库切换到只读(锁定),确保绝对不会有新的事务写入,导致数据不一致的情况。

但实际上,目前 PostgreSQL 没有严格意义上的只读模式。不过 PostgreSQL 提供了 2 种只读锁定的方法:

通过或可以

  1. 硬锁定(调整参数):直接将数据库切换到恢复模式(Recovery Mode),不允许写操作。
  2. 软锁定(设置事务模式):设置 system config default_transaction_read_only = on,将后续登录的会话或者当前事务设置为只读模式,允许被破解。

在只读模式下,PostgreSQL 不允许执行如下 SQL:

When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed. This is a high-level notion of read-only that does not prevent all writes to disk.

硬锁定

  1. 配置 recovery.conf。
recovery_target_timeline = 'latest'  
standby_mode = on  

注:PostgreSQL 12: Recovery.conf 文件参数合并到了 postgresql.conf,recovery.conf 不再使用。

  1. 重启数据库。
pg_ctl restart -m fast  
  1. 硬锁定是不可被破解的。
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. 重命名 recovery.conf 为 recovery.done。
cd $PGDATA  
mv recovery.conf recovery.done  

注:PostgreSQL 12: Recovery.conf 文件参数合并到了 postgresql.conf,recovery.conf 不再使用。

  1. 重启数据库。
pg_ctl restart -m fast  

软锁定

  1. 设置 default_transaction_read_only。
# 设置系统级别的只读模式,数据库不需要重启也永久生效。
postgres=# alter system set default_transaction_read_only=on;  

# 设置 Session 级别的只读模式,退出 SQL 交互窗口后失效。
set session default_transaction_read_only=off;

# 设置指定登陆数据库的用户为只读模式,数据库不需要重启也永久生效。
alter user user001 set default_transaction_read_only=on;
  1. 重载配置。
postgres=# select pg_reload_conf();  
 pg_reload_conf   
----------------  
 t  
(1 row)  

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 on  
(1 row)  
  1. 所有会话自动进入 read-only 的默认事务模式。
postgres=# insert into t1 values (1);  
ERROR:  cannot execute INSERT in a read-only transaction  

软解锁

  1. 设置 default_transaction_read_only。
postgres=# alter system set default_transaction_read_only=off;  
  1. 重载配置。
postgres=# select pg_reload_conf();  
 pg_reload_conf   
----------------  
 t  
(1 row)  

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

注:软锁定是可以被破解的,无需重新配置,执行指令:

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
\q
# struts2:2.3.20 # spring:4.1.5 # hibernate:4.3.8 ## 用户与订单一对多的关系,数据库中订单表有用户这个外键。在用户映射文件中已设置cascade="delete" ## ## 项目启动无错误,在后台管理用户页面删除用户时报如下错误: 16:35:30,957 WARN SqlExceptionHelper:144 - SQL Error: 1451, SQLState: 23000 16:35:30,959 ERROR SqlExceptionHelper:146 - Cannot delete or update a parent row: a foreign key constraint fails (`mall`.`orderitem`, CONSTRAINT `FK_hth7ctygtqa5ov7kutghwa1ni` FOREIGN KEY (`uid`) REFERENCES `orders` (`oid`)) 16:35:30,961 INFO AbstractBatchImpl:208 - HHH000010: On release of batch it still contained JDBC statements 16:35:31,165 ERROR DefaultDispatcherErrorHandler:42 - Exception occurred during processing request: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:163) at org.springframework.orm.hibernate4.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:730) at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:592) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:521) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:291) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653) at com.lonini.mall.user.service.UserService$$EnhancerBySpringCGLIB$$3596b2ab.deleteUser(<generated>) at com.lonini.mall.user.adminaction.UserAdminAction.delete(UserAdminAction.java:55) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ...... Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211) ...... Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`mall`.`orderitem`, CONSTRAINT `FK_hth7ctygtqa5ov7kutghwa1ni` FOREIGN KEY (`uid`) REFERENCES `orders` (`oid`)) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723) at com.mysql.jdbc.Connection.execSQL(Connection.java:3283) ...... order实体: ![order实体](https://img-ask.csdn.net/upload/201506/05/1433495761_123196.jpg) order关系映射: ![order关系映射](https://img-ask.csdn.net/upload/201506/05/1433494844_663178.jpg) user实体: ![user实体](https://img-ask.csdn.net/upload/201506/05/1433494875_943639.jpg) user关系映射: ![user关系映射](https://img-ask.csdn.net/upload/201506/05/1433494947_333542.jpg) 删除用户的JSP页面: ![删除用户的JSP页面](https://img-ask.csdn.net/upload/201506/05/1433494972_750482.jpg) 删除用户的action方法: ![删除用户的action方法](https://img-ask.csdn.net/upload/201506/05/1433495000_125389.jpg) 删除用户的Service层: ![删除用户的Service层](https://img-ask.csdn.net/upload/201506/05/1433494970_267564.jpg) 删除用户的Dao层: ![删除用户的Dao层](https://img-ask.csdn.net/upload/201506/05/1433495048_299389.jpg) 删除用户的struts2配置: ![删除用户的struts2配置](https://img-ask.csdn.net/upload/201506/05/1433495023_750724.jpg) 删除用户的spring配置(action): ![删除用户的spring配置(action)](https://img-ask.csdn.net/upload/201506/05/1433495106_936985.jpg)
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付 49.00元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值