Postgresl选择正确的关闭模式

http://www.cnblogs.com/o-to-s/articles/6881646.html

停止数据库的命令：

1
pg_ctl stop -D $PGDATA [-m  shutdown -mode]
shutdown-mode有如下几种模式：
1. smart: 等所有的连接中止后，关闭数据库。如果客户端连接不终止， 则无法关闭数据库。
开启一个空会话：
1
2
3
4
5
6
[root@localhost ~] # su - postgres
[postgres@localhost ~]$ psql
psql (9.4.4)
Type  "help"  for  help.
  
postgres= #

用smart关闭数据库：

1
2
3
4
5
[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: 快速关闭数据库， 断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。
1
2
3
[postgres@localhost ~]$ pg_ctl stop -D $PGDATA -m fast
waiting  for  server to shut down....  done
server stopped

查看关闭日志：

1
2
3
4
5
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

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

起一个事务，然后测试关闭：
1
2
3
4
5
6
7
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去关闭数据库：

1
2
3
4
5
6
7
8
9
10
[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

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

1
2
3
4
postgres= # select * from t;
id  | name
----+------
(0 rows)

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

 
3. immediate: 立即关闭数据库，立即停止数据库进程，直接退出，下次启动时会进行实例恢复。
1
2
3
4
5
6
7
8
postgres= # insert into t values(1,'a')
;
INSERT 0 1
postgres= # select * from t;
id  | name
----+------
1 | a
(1 row)

关闭数据库：

1
2
3
4
5
6
7
8
9
10
11
[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 reconnect to the database and repeat your  command .
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 reconnect to the database and repeat your  command .

启动数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
[postgres@localhost ~]$ pg_ctl -D  /apps/pgsql/pgdata  -l 1.log start
server starting
  
查看日志：
LOG: database system was interrupted; last known up at 2017-04-27 18:56:47 PDT
LOG: database system was not properly shut down; automatic recovery  in  progress  #提示非正常关机，自动开启恢复。
LOG: redo starts at 0 /181F910
LOG: record with zero length at 0 /181FA90
LOG: redo  done  at 0 /181FA60
LOG: last completed transaction was at log  time  2017-04-27 18:59:13.727213-07
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

查看数据：

1
2
3
4
5
6
7
8
9
[postgres@localhost ~]$ psql
psql (9.4.4)
Type  "help"  for  help.
  
postgres= # select * from t;
id  | name
----+------
1 | a
(1 row)
提交的数据已通过实例恢复。
 
小结：
对比以上三种关库模式：
smart最为安全，但最慢， 需要将所有连接都断开后，才会关库，默认关库模式。
fast强制中断会话，而不管有操作有没有提交，在做系统维护（系统维护时一般应用都正常关闭了，或者不再会有事务操作。）时，需要这种模式来关闭数据库。
immediate最暴力的方式，不管数据有没有落盘（POSGRE是遵循WAL机制），就直接关掉， 待启动时进行实例恢复， 如果在关库前有大量的事务没有写入磁盘， 那这个恢复过程可能会非常的漫长。