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