linux psql退出交互式界面,Postgresql 如何选择正确的关闭模式

停止数据库的命令：

pg_ctl stop -D $PGDATA [-m shutdown-mode]

shutdown-mode有如下几种模式：

1. smart: 等所有的连接中止后，关闭数据库。如果客户端连接不终止， 则无法关闭数据库。

开启一个空会话：

[root@localhost ~]# su - postgres

[postgres@localhost ~]$ psql

psql (9.4.4)

Type "help" for help.

postgres=#

用smart关闭数据库：

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA -m smart

waiting for server to shut down............................................................... failed

pg_ctl: server does not shut down

HINT: The "-m fast" option immediately disconnects sessions rather than

waiting for session-initiated disconnection

2. fast: 快速关闭数据库， 断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA -m fast

waiting for server to shut down.... done

server stopped

查看关闭日志：

LOG: received fast shutdown request

LOG: aborting any active transactions

FATAL: terminating connection due to administrator command

LOG: shutting down

LOG: database system is shut down

会话被强制中断，然后关闭数据库。

起一个事务，然后测试关闭：

postgres=# create table t(id int primary key, name varchar(9));

CREATE TABLE

postgres=# begin;

BEGIN

postgres=# insert into t values(1,'a')

postgres-# ;

INSERT 0 1

不提交， 然后用FAST MODE去关闭数据库：

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA -m fast

waiting for server to shut down.... done

server stopped

查看日志：

LOG: received fast shutdown request

LOG: aborting any active transactions

LOG: autovacuum launcher shutting down

FATAL: terminating connection due to administrator command

LOG: shutting down

LOG: database system is shut down

同样是直接中断会话， 而不去管事务有没有提交。

postgres=# select * from t;

id | name

----+------

(0 rows)

没有提交的数据， 在重启之后并不能查到。

3. immediate: 立即关闭数据库，立即停止数据库进程，直接退出，下次启动时会进行实例恢复。

postgres=# insert into t values(1,'a')

;

INSERT 0 1

postgres=# select * from t;

id | name

----+------

1 | a

(1 row)

关闭数据库：

[postgres@localhost ~]$ pg_ctl stop -D $PGDATA -m immediate

waiting for server to shut down.... done

server stopped

查看日志：

LOG: received immediate shutdown request

WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT: In a moment you should be able to rec