PostgreSQL簡(jiǎn)單管理(一)

1、初始化數(shù)據(jù)庫(kù)集群

和其他RDBMS一樣，在開始使用PostgreSQL數(shù)據(jù)庫(kù)之前需要在磁盤上初始化一個(gè)數(shù)據(jù)庫(kù)，這里稱為數(shù)據(jù)庫(kù)集群。數(shù)據(jù)庫(kù)集群是一個(gè)運(yùn)行著的數(shù)據(jù)庫(kù)服務(wù)實(shí)例管理的數(shù)據(jù)庫(kù)的集合。初始化完后，集群中包含一個(gè)名為postgres的數(shù)據(jù)庫(kù)，作為默認(rèn)的數(shù)據(jù)庫(kù)。還會(huì)創(chuàng)建另一個(gè)叫作template1的數(shù)據(jù)庫(kù)，它被用作后續(xù)創(chuàng)建數(shù)據(jù)庫(kù)的一個(gè)模版。

在文件系統(tǒng)層面，一個(gè)數(shù)據(jù)庫(kù)集群是一個(gè)存儲(chǔ)所有數(shù)據(jù)的目錄(data directory)。它取決于你選擇在哪存儲(chǔ)你的數(shù)據(jù)。默認(rèn)的目錄是/usr/local/pgsql/data或/var/lib/pgsql/data。

使用iniddb命令初始化數(shù)據(jù)庫(kù)集群，使用-D參數(shù)指定數(shù)據(jù)庫(kù)的路徑。示例如下：

initdb -D /usr/local/pgsql/data

[postgres@rhel7 ~]$ initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /usr/local/pgsql/data -l logfile start

可以指定環(huán)境變量PGDATA指向PostgreSQL的目錄。

還可以調(diào)用pg_ctl命令來(lái)初始化數(shù)據(jù)庫(kù)集群：

pg_ctl -D /usr/local/pgsql/data initdb

指定的目錄必須為空，否則則無(wú)法初始化。初始化后整個(gè)目錄的權(quán)限變?yōu)?00(drwx------)。

[postgres@rhel7 pgsql]$ ls -l
total 4
drwx------ 19 postgres postgres 4096 Mar 23 16:31 data

2、啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)

數(shù)據(jù)庫(kù)服務(wù)程序叫作postgres。啟動(dòng)數(shù)據(jù)庫(kù)時(shí)必須指定數(shù)據(jù)目錄。簡(jiǎn)單的一個(gè)示例：

postgres -D /usr/local/pgsql/data

[postgres@rhel7 data]$ postgres -D /usr/local/pgsql/data/
LOG:  database system was shut down at 2017-03-23 16:31:28 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

如果不指定-D參數(shù)，命令會(huì)去找PGDATA環(huán)境變量，如果兩個(gè)都沒(méi)有則啟動(dòng)報(bào)錯(cuò)。

上面的命令是在前臺(tái)啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)，不過(guò)最好在后臺(tái)啟動(dòng)。后臺(tái)啟動(dòng)的例子：

postgres -D /usr/local/pgsql/data > logfile 2>&1 &

另一個(gè)封裝好的命令pg_ctl也可以提供相應(yīng)的功能。如下例：

pg_ctl start -l logfile 

[postgres@rhel7 data]$ pg_ctl -D /usr/local/pgsql/data start -l logfile
server starting
[postgres@rhel7 data]$ cat logfile 
LOG:  database system was shut down at 2017-03-23 16:34:12 CST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

上面的命令可以在后臺(tái)啟用數(shù)據(jù)庫(kù)服務(wù)，并把輸出寫入到日志文件中。-D參數(shù)同樣用于指定數(shù)據(jù)目錄。pg_ctl還可以用于停止數(shù)據(jù)庫(kù)服務(wù)。

服務(wù)啟動(dòng)后，對(duì)應(yīng)的PID被記錄在數(shù)據(jù)目錄的postmaster.pid文件中。防止啟動(dòng)多次，也可以用來(lái)關(guān)閉服務(wù)。

3、關(guān)閉數(shù)據(jù)庫(kù)服務(wù)

關(guān)閉PostgreSQL數(shù)據(jù)庫(kù)有多種模式。主要有如下幾種：

SIGTERM

Smart Shutdown模式。數(shù)據(jù)庫(kù)接到SIGTERM后，服務(wù)端不允許新的連接，但已連接的會(huì)話繼續(xù)工作直到會(huì)話完成。當(dāng)所有會(huì)話完成后才關(guān)閉數(shù)據(jù)庫(kù)。如果數(shù)據(jù)庫(kù)在熱備狀態(tài)，會(huì)等備份完成。如果在恢復(fù)狀態(tài)則等所有進(jìn)程終止。

SIGINT

Fast Shutdown模式。服務(wù)端不允許新的連接，并且給所有已存在的服務(wù)進(jìn)程發(fā)送SIGTERM，使它們終止當(dāng)前的事務(wù)并立即退出。所有服務(wù)進(jìn)程退出后數(shù)據(jù)庫(kù)關(guān)閉。

SIGQUIT

Immediate Shutdown模式。服務(wù)端發(fā)送SIGQUIT給所有的子進(jìn)程，并等待它們終止，如果5秒鐘后沒(méi)有終止，這些進(jìn)行被SIGKILL。等所有子進(jìn)程停止后，主服務(wù)進(jìn)程退出。它不做正常的關(guān)閉進(jìn)程，下次啟動(dòng)時(shí)會(huì)啟動(dòng)恢復(fù)。建議僅在緊急情況下使用。

pg_ctl提供關(guān)閉數(shù)據(jù)庫(kù)的方式：

pg_ctl stop #默認(rèn)fast模式關(guān)閉

[postgres@rhel7 data]$ > logfile 
[postgres@rhel7 data]$ pg_ctl stop -D /usr/local/pgsql/data/
waiting for server to shut down.... done
server stopped
[postgres@rhel7 data]$ cat logfile 
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down

指定用某種方式關(guān)閉：

pg_ctl stop -m smart/fast/immediate 

還可以直接kill進(jìn)程號(hào)的方式，進(jìn)程號(hào)在數(shù)據(jù)目錄的postmaster.pid文件中

kill -INT ‘head -1 /usr/local/pgsql/data/postmaster.pid‘

4、服務(wù)端配置

參數(shù)配置文件在數(shù)據(jù)目錄中的postgresql.conf

查看當(dāng)前的配置

使用show命令

show all/paramter_name;

使用函數(shù)

select current_setting('paramter_name');

4.1、修改參數(shù)

4.1.1 使用SQL語(yǔ)句修改參數(shù)

ALTER SYSTEM   #修改系統(tǒng)級(jí)相當(dāng)于修改psotgresql.conf，改后的參數(shù)記錄在postgresql.auto.conf文件中

ALTER DATABASE #修改數(shù)據(jù)庫(kù)級(jí)

ALTER ROLE     #修改ROLE級(jí)

直接修改postgresql.conf文件，需要pg_ctl reload或執(zhí)行select pg_reload_conf();把配置重新讀入系統(tǒng);

另外，還有一個(gè)系統(tǒng)視圖pg_settings可以用來(lái)查看及修改session級(jí)別的參數(shù)。

SET configuration_parameter TO DEFAULT;

UPDATE pg_settings SET setting = reset_val WHERE name = ’configuration_parameter’;

上面兩個(gè)語(yǔ)句是等價(jià)的。

4.1.2 在命令行中指定參數(shù)

在啟動(dòng)數(shù)據(jù)庫(kù)時(shí)，通過(guò)postgres命令使用-c添加指定參數(shù)

postgres -c log_connections=yes -c log_destination=’syslog’

這種方式指定的參數(shù)除非重啟數(shù)據(jù)庫(kù)，否則ALTER SYSTEM命令也不能修改。指定的參數(shù)信息記錄在postmaster.opts文件中。

在啟動(dòng)session時(shí)，可以通過(guò)設(shè)置環(huán)境變量PGOPTIONS，來(lái)設(shè)置session中參數(shù)的值

env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql

4.1.3 引用其他參數(shù)文件

參數(shù)文件postgresql.conf可以引用其他參數(shù)文件，可以嵌套引用?？梢杂梢韵聟?shù)參數(shù)指定：

include='special.conf'  #直接指定文件，與postgresql.conf不在同一目錄下需要指定絕對(duì)路徑，如果文件不存在，則啟動(dòng)報(bào)錯(cuò)。

include_if_exists='exists.conf' #用法同include，但如果文件不存在則忽略該參數(shù)

include_dir='conf_dir'   #引用指定目錄下所有的后綴為.conf的文件。

參考：https://www.postgresql.org/docs/9.6/static/server-start.html