postgres 退出_Postgresl选择正确的关闭模式

admin管理员组

文章数量:1582038

停止数据库的命令：

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 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.

启动数据库：

[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

查看数据：

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

本文标签： 正确模式PostgresPostgresl