PostgreSQL簡單管理(一)

1、初始化數據庫集群

和其他RDBMS一樣，在開始使用PostgreSQL數據庫之前需要在磁盤上初始化一個數據庫，這里稱為數據庫集群。數據庫集群是一個運行著的數據庫服務實例管理的數據庫的集合。初始化完后，集群中包含一個名為postgres的數據庫，作為默認的數據庫。還會創建另一個叫作template1的數據庫，它被用作后續創建數據庫的一個模版。

在文件系統層面，一個數據庫集群是一個存儲所有數據的目錄(data directory)。它取決于你選擇在哪存儲你的數據。默認的目錄是/usr/local/pgsql/data或/var/lib/pgsql/data。

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

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

可以指定環境變量PGDATA指向PostgreSQL的目錄。

還可以調用pg_ctl命令來初始化數據庫集群：

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

指定的目錄必須為空，否則則無法初始化。初始化后整個目錄的權限變為700(drwx------)。

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

2、啟動數據庫服務

數據庫服務程序叫作postgres。啟動數據庫時必須指定數據目錄。簡單的一個示例：

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參數，命令會去找PGDATA環境變量，如果兩個都沒有則啟動報錯。

上面的命令是在前臺啟動數據庫服務，不過最好在后臺啟動。后臺啟動的例子：

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

另一個封裝好的命令pg_ctl也可以提供相應的功能。如下例：

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

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

服務啟動后，對應的PID被記錄在數據目錄的postmaster.pid文件中。防止啟動多次，也可以用來關閉服務。

3、關閉數據庫服務

關閉PostgreSQL數據庫有多種模式。主要有如下幾種：

SIGTERM

Smart Shutdown模式。數據庫接到SIGTERM后，服務端不允許新的連接，但已連接的會話繼續工作直到會話完成。當所有會話完成后才關閉數據庫。如果數據庫在熱備狀態，會等備份完成。如果在恢復狀態則等所有進程終止。

SIGINT

Fast Shutdown模式。服務端不允許新的連接，并且給所有已存在的服務進程發送SIGTERM，使它們終止當前的事務并立即退出。所有服務進程退出后數據庫關閉。

SIGQUIT

Immediate Shutdown模式。服務端發送SIGQUIT給所有的子進程，并等待它們終止，如果5秒鐘后沒有終止，這些進行被SIGKILL。等所有子進程停止后，主服務進程退出。它不做正常的關閉進程，下次啟動時會啟動恢復。建議僅在緊急情況下使用。

pg_ctl提供關閉數據庫的方式：

pg_ctl stop #默認fast模式關閉

[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

指定用某種方式關閉：

pg_ctl stop -m smart/fast/immediate 

還可以直接kill進程號的方式，進程號在數據目錄的postmaster.pid文件中

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

4、服務端配置

參數配置文件在數據目錄中的postgresql.conf

查看當前的配置

使用show命令

show all/paramter_name;

使用函數

select current_setting('paramter_name');

4.1、修改參數

4.1.1 使用SQL語句修改參數

ALTER SYSTEM   #修改系統級相當于修改psotgresql.conf，改后的參數記錄在postgresql.auto.conf文件中

ALTER DATABASE #修改數據庫級

ALTER ROLE     #修改ROLE級

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

另外，還有一個系統視圖pg_settings可以用來查看及修改session級別的參數。

SET configuration_parameter TO DEFAULT;

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

上面兩個語句是等價的。

4.1.2 在命令行中指定參數

在啟動數據庫時，通過postgres命令使用-c添加指定參數

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

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

在啟動session時，可以通過設置環境變量PGOPTIONS，來設置session中參數的值

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

4.1.3 引用其他參數文件

參數文件postgresql.conf可以引用其他參數文件，可以嵌套引用。可以由以下參數參數指定：

include='special.conf'  #直接指定文件，與postgresql.conf不在同一目錄下需要指定絕對路徑，如果文件不存在，則啟動報錯。

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

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

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