PostgreSQL — 数据库实例只读锁定

柔情只为你懂 2023-10-05 11:06 13阅读 0赞

## 目录 ##

#### 文章目录 ####

*  目录
 *  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.  重载配置。

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

psql -U <username> -d postgres
    begin;
    set transaction read write;
    alter database exercises set default_transaction_read_only = off;
    commit;
    \q