Postgresl选择正确的关闭模式

来源：互联网发布：威露士极是洗发水知乎编辑：程序博客网时间：2024/06/06 09:13

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机制），就直接关掉，待启动时进行实例恢复，如果在关库前有大量的事务没有写入磁盘，那这个恢复过程可能会非常的漫长。

阅读全文

0 0