Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Postgresql 12 启动停止 Study 002 Tips

2020-09-01

Postgresql 12 启动停止 Study 002 Tips

启动数据库

常用pg_ctl来启动停止数据库,使用语法可以参考命令的帮助提示。

启动数据库:pg_ctl start -D DATADIR

[postgres@centos7_pg ~]$ pg_ctl start -D $PGDATA
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start..... done
server started
[postgres@centos7_pg ~]$ 
[postgres@centos7_pg pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 1715)
/data/pgsql10/bin/postgres "-D" "/data/pgsql10/pgdata"
[postgres@centos7_pg pgdata]$

记录日志启动方式

启动数据库:pg_ctl start -D DATADIR -l LOGFILE

[postgres@centos7_pg pgdata]$ pg_ctl start -D $PGDATA -l $PGDATA/pg10.`date +%Y%m%d_%H%M`.log
waiting for server to start.... done
server started
[postgres@centos7_pg pgdata]$ pwd
/data/pgsql10/pgdata
[postgres@centos7_pg pgdata]$ ls -tlr pg10*
-rw------- 1 postgres postgres 367 Apr  7 00:11 pg10.20200407_0011.log
[postgres@centos7_pg pgdata]$

如果不想在pg_ctl每次指定特定log记录日志路径的话,可以设置参数实现配置数据库错误日志。

打开日志开关: logging_collector = on

记录日志路径: log_directory = 'log' 记录日志路径可以绝对路径,也可以相对路径(相对于$PGDATA的相对路径),如果目录文件夹没有创建的话,自动创建。

记录日志文件名: log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

记录日志文件的权限设置: log_file_mode = 0600

上面设置在postgresql.conf配置文件中默认都是没有设置的

[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep "=" | egrep "logging_collector|log_directory|log_filename|log_file_mode"
#logging_collector = off                # Enable capturing of stderr and csvlog
#log_directory = 'log'                  # directory where log files are written,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
#log_file_mode = 0600                   # creation mode for log files,
[postgres@centos7_pg pgdata]$ vi postgresql.conf
[postgres@centos7_pg pgdata]$ cat postgresql.conf | grep "=" | egrep "logging_collector|log_directory|log_filename|log_file_mode"
logging_collector = on          # Enable capturing of stderr and csvlog
log_directory = 'log'                   # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0660                    # creation mode for log files,
[postgres@centos7_pg pgdata]$
[postgres@centos7_pg pgdata]$ pg_ctl start -D $PGDATA
waiting for server to start....2020-04-07 00:22:00.464 CST [1965] LOG:  listening on IPv4 address "192.168.50.96", port 5432
2020-04-07 00:22:00.470 CST [1965] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-04-07 00:22:00.480 CST [1965] LOG:  redirecting log output to logging collector process
2020-04-07 00:22:00.480 CST [1965] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@centos7_pg pgdata]$
[postgres@centos7_pg pgdata]$ ls -ltr log
total 4
-rw-rw---- 1 postgres postgres 186 Apr  7 00:22 postgresql-2020-04-07_002200.log
[postgres@centos7_pg pgdata]$

停止数据库

停止数据库: pg_ctl stop -D DATADIR [-m SHUTDOWNMODE]

[postgres@centos7_pg pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 1715)
/data/pgsql10/bin/postgres "-D" "/data/pgsql10/pgdata"
[postgres@centos7_pg pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 1715)
/data/pgsql10/bin/postgres "-D" "/data/pgsql10/pgdata"
[postgres@centos7_pg pgdata]$ pg_ctl stop -D "/data/pgsql10/pgdata"
waiting for server to shut down.... done
server stopped
[postgres@centos7_pg pgdata]$ pg_ctl status
pg_ctl: no server running
[postgres@centos7_pg pgdata]$

-m 参数是指数据库的停止方式,有三种方式(默认是fast方式)。

方式 说明
smart 等待所有连接终止后关闭数据库
fast 快速断开连接并关闭数据库
immediate 立刻关闭数据库,下次启动时候需要进行恢复

重载数据库

发送SIGHUP给postgres服务器,重新载入配置文件(postgresql.conf,pg_hba.conf等)。

类似启停数据库载入配置文件。前提是数据库是启动状态的。

重载数据库: pg_ctl reload

[postgres@centos7_pg pgdata]$ pg_ctl reload
pg_ctl: PID file "/data/pgsql10/pgdata/postmaster.pid" does not exist
Is server running?
[postgres@centos7_pg pgdata]$ pg_ctl status
pg_ctl: no server running
[postgres@centos7_pg pgdata]$ pg_ctl start -D $PGDATA -l $PGDATA/pg10.`date +%Y%m%d_%H%M`.log
waiting for server to start.... done
server started
[postgres@centos7_pg pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 1771)
/data/pgsql10/bin/postgres "-D" "/data/pgsql10/pgdata"
[postgres@centos7_pg pgdata]$ pg_ctl reload
server signaled
[postgres@centos7_pg pgdata]$

查看日志:

LOG:  listening on IPv4 address "192.168.50.96", port 5432
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
LOG:  database system was shut down at 2020-04-06 19:53:56 CST
LOG:  database system is ready to accept connections
LOG:  received SIGHUP, reloading configuration files

pg_ctl 参考文档

[postgres@centos7_pg ~]$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db] [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start    [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                  [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop     [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart  [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                  [-o OPTIONS] [-c]
  pg_ctl reload   [-D DATADIR] [-s]
  pg_ctl status   [-D DATADIR]
  pg_ctl promote  [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl kill     SIGNALNAME PID

Common options:
  -D, --pgdata=DATADIR   location of the database storage area
  -s, --silent           only print errors, no informational messages
  -t, --timeout=SECS     seconds to wait when using -w option
  -V, --version          output version information, then exit
  -w, --wait             wait until operation completes (default)
  -W, --no-wait          do not wait until operation completes
  -?, --help             show this help, then exit
If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
  -c, --core-files       allow postgres to produce core files
  -l, --log=FILENAME     write (or append) server log to FILENAME
  -o, --options=OPTIONS  command line options to pass to postgres
                         (PostgreSQL server executable) or initdb
  -p PATH-TO-POSTGRES    normally not necessary

Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"

Shutdown modes are:
  smart       quit after all clients have disconnected
  fast        quit directly, with proper shutdown (default)
  immediate   quit without complete shutdown; will lead to recovery on restart

Allowed signal names for kill:
  ABRT HUP INT QUIT TERM USR1 USR2

Report bugs to <pgsql-bugs@postgresql.org>.
[postgres@centos7_pg ~]$

Have a good work&life! 2020/09 via LinHong


Similar Posts

Comments