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