PostgreSQL's SET default_transaction_read_only = off;只读事物模式

让数据库变成只读模式，目前PostgreSQL没有严格意义上的只读模式（如临时表在只读事务中还是可以使用的）。通过调整参数或设置事务模式可以将后续登录的SESSION或者当前事务设置为只读模式。
在只读模式下,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.

在SQL模式下进入只读事务的方法：
digoal=> begin;
BEGIN
digoal=> set transaction read only;
SET

参数配置 :
default_transaction_read_only = on
配置完后pg_ctl reload -D $PGDATA
配置完参数后，不影响已经连接的SESSION，仅仅对后续连接上来的SESSION生效。新建的SESSION进来后事务就是read only模式。
digoal=> show default_transaction_read_only
digoal-> ;
 default_transaction_read_only
-------------------------------
 on
digoal=> delete from tbl_test;
ERROR:  cannot execute DELETE in a read-only transaction

# 可以设置事务级WRITE覆盖这个默认值
digoal=> begin;
BEGIN
digoal=> set transaction read write;
SET
digoal=> delete from tbl_test;
DELETE 1008

# 或者设置SESSION级参数，覆盖之
digoal=> set session default_transaction_read_only=off;　　　#在恢复数据时报错“psql.bin:jyall_pgdump_all.sql:35: \connect: FATAL:  password authentication failed for user "postgres"

SET
digoal=> delete from tbl_test;
DELETE 1008


Oracle进入只读模式可以在启动数据库时通过startup mount ; alter database open read only ; ORACLE进入只读模式后要回到读写模式需要重启数据库，原因是数据库智能OPEN一次。
Oracle还有两个不需要重启数据库也进入到只读模式的命令如：
alter system SUSPEND | RESUME
alter system QUIESCE RESTRICTED and UNQUIESCE
具体的细节就不说了，可以参考ORACLE官方文档。