5、服务控制

作者: shark_tear | 来源:发表于2017-11-24 14:14 被阅读0次

PostgreSQL的服务控制主要包括以下3类：

服务端的启动（start）、停止（stop）、重启（restart）、重载（reload）。
服务端的会话连接管理
服务端的实例管理

服务端的启动（start）、停止（stop）、重启（restart）、重载（reload）

在不同的平台上使用的命令稍有区别

在RHEL/CentOS 平台上，有两种方式来管理，分别是PostgreSQL自带的管理命令pg_ctl和系统自带工具systemctl，命令用法如下：

#pg_ctl的命令用法：
pg_ctl start   [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
pg_ctl stop    [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
               [-o "OPTIONS"]
pg_ctl reload  [-D DATADIR] [-s]
选项解释：
-D/--pgdata=DATADIR  设置启动的postgres进程的数据存储目录，如果不带这个参数，先查找PGDATA变量，没有这个变量，则默认为/var/lib/pgsql/9.6/data/目录。
-s  silent    安静模式，带上这个选项时，程序只会输出错误信息，不会输出其他信息
-t  --timeout=SECS  等待的时间，和-w一起合用
-w  在操作完成之前，一直等待
-W  不等到操作完成
-l --log=FILENAME  启动时，将日志记录到哪个文件，PostgreSQL默认的日志文件是记录在data目录下的pg_log目录中。
-m  --mode=MODE，表示执行这个操作时使用的模式，可以是smart、fast或immediate。

这个命令默认在postgres用户身份下才能执行，PostgreSQL自带的管理命令都在/usr/pgsql-9.6/bin目录下。因此执行这个命令需要使用绝对路径，或者将这个目录添加到系统路径。示例如下：

启动

-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl start 
server starting
-bash-4.2$ < 2017-11-23 14:51:05.274 CST > LOG:  redirecting log output to logging collector process
< 2017-11-23 14:51:05.274 CST > HINT:  Future log output will appear in directory "pg_log".

不带任何参数时，默认将日志输出到shell界面。

-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl start -l /var/lib/pgsql/9.6/data/pg_log/postgresql.log
server starting

带日志记录消息时，只会返回一个服务器启动的消息。

停止

-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl stop -m smart
waiting for server to shut down.... done
server stopped
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl start -l /var/lib/pgsql/9.6/data/pg_log/postgresql.log
server starting
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl start -l /var/lib/pgsql/9.6/data/pg_log/postgresql.log
server starting
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl stop -m immediate
waiting for server to shut down.... done
server stopped

停止命令后面可以带3个参数，如上面代码所示，带的三个参数结束进程时，返回的信息都是一样的，那停止运行的三种模式区别在于什么呢？区别是：

smart：智能停止模式，根据服务端正在进行的工作来判断，比较忙碌时，等业务结束连接断开时停止。
fast：快速模式，即迅速断开所有连接，结束所有用户的事务，然后停止服务。
immediate：立即结束服务进程。

重启

restart命令后面的模式和stop命令的模式一致，相关控制也是一致的。restart里面包含stop和start两步。但是先停止服务再重启服务，在停止服务之前，数据库会将缓存中的数据写入到数据文件中，这在数据库操作中称为CHECKPOINT，这个动作花费的时间越长，重启时花费的时间就越短。同时这个动作中做的事情越多，则花费的时间越多。因此在重启数据库之前手动执行一次CHECKPOINT，可以让重启的时间更短，命令如下：
psql -c 'CHECKPOINT';
但是重启以后，因为数据库缓存被清空，需要通过数据库自带的数据文件来重建缓存，在大型数据库中，这样的操作非常耗时。因此预热缓存需要很长的时间，这中间的取舍就要根据业务需要来进行均衡操作（自己的想法）。

重载

重载一般用于修改配置文件后重新加载配置时使用，命令执行过程如下：

-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl  reload
server signaled

也是只给出一个信号。

上面还说到，可以使用系统自带的systemctl工具来管理服务。用法如下：

[root@cephadmin ~]# systemctl start postgresql-9.6
[root@cephadmin ~]# ps aux | grep postgres 
root      94651  0.0  0.3 212040  3196 pts/2    S    15:23   0:00 su - postgres
postgres  94652  0.6  0.3 116424  3124 pts/2    S+   15:23   0:00 -bash
postgres  94773  1.5  1.5 357552 15236 ?        Ss   15:23   0:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
postgres  94776  0.2  0.1 212556  1568 ?        Ss   15:23   0:00 postgres: logger process   
postgres  94778  0.0  0.1 357552  1684 ?        Ss   15:23   0:00 postgres: checkpointer process   
postgres  94779  0.0  0.1 357552  1920 ?        Ss   15:23   0:00 postgres: writer process   
postgres  94780  0.2  0.1 357552  1684 ?        Ss   15:23   0:00 postgres: wal writer process   
postgres  94781  0.0  0.2 357976  2756 ?        Ss   15:23   0:00 postgres: autovacuum launcher process   
postgres  94782  0.0  0.1 212552  1896 ?        Ss   15:23   0:00 postgres: stats collector process   
root      94819  0.0  0.0 112644   952 pts/0    R+   15:23   0:00 grep --color=auto postgres
[root@cephadmin ~]# systemctl stop postgresql-9.6
[root@cephadmin ~]# systemctl reload postgresql-9.6
Job for postgresql-9.6.service invalid.
[root@cephadmin ~]# systemctl start postgresql-9.6
[root@cephadmin ~]# systemctl restart postgresql-9.6

命令执行成功时，不会有任何提示，基本上都是要到日志里去查看。reload和restart选项必须在postgres进程已经在运行时才能使用，否则会有一个提示出错信息。

这里有一个地方需要注意，那就是使用pg_ctl命令启动的postgres进程和systemctl启动的postgres进程时不同的，在上面的代码中，我们看到postgres的启动命令是：
/usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
我们将这个进程结束，然后使用pg_ctl启动进程再看一下：

[root@cephadmin ~]# systemctl stop postgresql-9.6
[root@cephadmin ~]# su - postgres
Last login: Thu Nov 23 15:23:13 CST 2017 on pts/2
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl start -l /var/lib/pgsql/9.6/data/pg_log/postgresql.log 
server starting
-bash-4.2$ ps aux | grep postgres
root      94651  0.0  0.3 212040  3196 pts/2    S    15:23   0:00 su - postgres
postgres  94652  0.0  0.3 116424  3124 pts/2    S+   15:23   0:00 -bash
root      95166  0.0  0.3 212040  3196 pts/0    S    15:28   0:00 su - postgres
postgres  95167  0.9  0.3 116552  3336 pts/0    S    15:28   0:00 -bash
postgres  95262  0.6  1.5 357552 15240 pts/0    S    15:29   0:00 /usr/pgsql-9.6/bin/postgres
postgres  95263  0.0  0.1 212556  1560 ?        Ss   15:29   0:00 postgres: logger process   
postgres  95265  0.0  0.1 357552  1676 ?        Ss   15:29   0:00 postgres: checkpointer process  
postgres  95266  0.0  0.1 357552  1916 ?        Ss   15:29   0:00 postgres: writer process   
postgres  95267  0.0  0.1 357552  1676 ?        Ss   15:29   0:00 postgres: wal writer process  
postgres  95268  0.0  0.2 357976  2752 ?        Ss   15:29   0:00 postgres: autovacuum launcher process  
postgres  95269  0.0  0.1 212552  1900 ?        Ss   15:29   0:00 postgres: stats collector process  
postgres  95270  0.0  0.1 139492  1628 pts/0    R+   15:29   0:00 ps aux
postgres  95271  0.0  0.0 112648   952 pts/0    R+   15:29   0:00 grep --color=auto postgres

从上面的显示信息里可以看到，启动postgres进程的命令是：
/usr/pgsql-9.6/bin/postgres
因此，当你使用pg_ctl命令启动postgres进程时，无法使用systemctl命令来停止。但是，使用systemctl启动的postgres进程，可以使用pg_ctl命令来结束。应该是在PostgreSQL的命令体系里，pg_ctl命令的权限更高。

服务端的会话连接管理

阻止新的连接

某些紧急情况下，需要完全锁定数据库，或者禁止用户访问数据库，阻止数据库连接的几种方式：

暂停/恢复连接池
停止服务（快速停止或紧急关闭进程，immediate级别，一般不推荐）
设置数据库允许的连接数为0：ALTER DATABASE dbname CONNECTION LIMIT 0; 此命令会限制普通用户连接，但是超级用户还是可以连接；如下所示：

[root@cephadmin ~]# psql -U postgres
Password for user postgres: 
psql (9.6.6)
Type "help" for help.
postgres=# CREATE USER dbuser WITH PASSWORD 'dbuser1234';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE postgres to dbuser;
GRANT
postgres=# exit
postgres-# \q
[root@cephadmin ~]# psql -U dbuser -d postgres
Password for user dbuser: 
psql: FATAL:  too many connections for database "postgres"
[root@cephadmin ~]# psql -U postgres
Password for user postgres: 
psql (9.6.6)
Type "help" for help.

postgres=#

在上面的示例中，首先创建一个普通用户dbuser，然后将postgres数据库的所有权限赋予给这个用户，最后尝试用这个用户登录。可以看到提示连接过多。但是再用postgres用户登录时，发现可以正常登录。

限制用户连接数

限制每个用户允许的连接数为0：ALTER USER username CONNECTION LIMIT 0;也是限制普通用户，超级用户postgres不受限制。示例如下：

postgres=# ALTER DATABASE postgres CONNECTION LIMIT -1;
ALTER DATABASE
postgres=# \q
^[[A[root@cephadmin ~]# psql -U dbuser -d postgres  
Password for user dbuser: 
psql (9.6.6)
Type "help" for help. 

postgres=> \q
[root@cephadmin ~]# psql -U postgres
Password for user postgres: 
psql (9.6.6)
Type "help" for help.

postgres=# ALTER USER dbuser CONNECTION LIMIT 0;
ALTER ROLE
postgres=# \q
[root@cephadmin ~]# psql -U dbuser -d postgres
Password for user dbuser: 
psql: FATAL:  too many connections for role "dbuser"

在上面的示例中，我们先将数据库的连接设置为-1，表示允许连接。然后使用dbuser登录，看到可以正常登录，然后使用postgres登录，修改配置，将dbuser的连接数设置为0，再尝试用dbuser登录，提示连接过多。

修改连接配置文件pg_hba.conf来限制所有的连接，然后重载文件，配置文件的配置如下：

#TYPE       DATABASE        USER        CIDR-ADDRESS        METHOD
local           all             all                         reject
host            all             all         0.0.0.0/0       reject

如果允许postgres可以登陆，则可以在将下面这一行添加为第一行：

local           all             postgres          peer

这运行postgres通过本地地址的socket连接（localhost）登陆。

限制每个用户只允许一个会话：ALTER ROLE fred CONNECTION LIMIT 1;
可以将值设为-1解除限制，改变了配置以后，已经建立的会话不受影响，但是重新连接的时候会提示连接受限。查看角色允许的连接数SQL语句如下：
SELECT roleconnlimit FROM pg_roles WHERE rolename='fred';
此时可以查看此角色已经建立的连接数，SQL语句如下：
SELECT COUNT(*) FROM pg_stat_activity WHERE usename= 'fred';
如果连接数超过限定的连接数，继续看下节的内容。

断开用户的连接

可以使用postgresql自带的函数pg_terminate_backend()来断开用户的连接，这个函数需要连接的pid作为参数。而pid可以从pg_stat_activity表中获得，筛选条件则有多种：

WHERE application_name = 'myappname'; #根据连接的应用名来筛选

postgres=# select pid from pg_stat_activity where application_name='psql';
  pid  
-------
 96661
(1 row)

WHERE waiting='t' #等待连接的会话，t表示true，即这个查询正处于等待状态。这个条件适用于PostgreSQL9.5及以下版本，在9.6版本中，这个字段被变更，不再适用。
WHERE state = '<IDLE> in transaction' #正在处理事务的会话
WHERE state = '<IDLE>' #处于空闲状态的会话

用来断开用户连接的SQL语句用法：

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE ....
SELECT count(pg_terminate_backend(pid)) FROM pg_stat_activity WHERE usename NOT IN ( SELECT usename FROM pg_user WHERE usesuper);

第一个是根据筛选条件得到会话进程的PID，然后结束该进程。第二个是更安全的做法，避免断开自己的连接：
因为从9.3版本开始，普通用户也可以结束自己的会话。断开会话的过程还可能出现下面的意外情况：

筛选会话的过程中，被筛选的会话已经断开，新建立的会话被系统分配了已断开的会话相同的PID，结果把新会话断开；
同一个用户建立了2个会话，要把这两个会话结束，当筛选出这两个会话的PID的时候，此用户又新建立了多个会话。此时只会结束已经被筛选出PID的会话，而不会将新建立的会话结束。因此要注意前面的限制用户会话数，避免用户在结束会话时创建新的会话。

服务端的实例管理

为多租户进行数据库设计

存在4个不同的方案：

同一个实例的同一个数据库中，在不同的模式下创建表；
同一个实例中创建多个数据库；
同一台物理机或虚拟机上创建多个postgresql实例，监听在不同端口；
同一个物理机上创建多个虚拟机，每个虚拟机上创建一个postgresql实例；

1，2一般用于将表分离，3，4一般用于资源隔离。

使用多个模式

在PostgreSQL的数据库中，每一组表都有一个自己的命名空间，被称为模式（schema），默认的是public。因此同一个数据库中可以创建多个不同的模式，每个模式底下可以有多张表。
创建模式：
CREATE SCHEMA schema_name;
在此模式下创建表
CREATE TABLE schema_name.table_name (filed1, filed2,.....);
查看当前的模式：
SELECT current_schema;
删除模式：
DROP SCHEMA schema_name;
目前没有切换模式的命令，不同的用户登陆数据库时都会有一个默认的模式，创建用户时如果没有设置，则默认的是public，如果设置了，则使用设置的默认模式。用户的模式设置是通过查找路径search_path变量来确定的。为角色设置默认模式命令如下：
ALTER ROLE role_name SET search_path = 'schema_name';或
ALTER USER user_name SET search_path = 'schema_name';
设置完成了以后，登录时，就登录到这个默认的schema里面。

限制某个用户只能在某个模式下处理表的权限
REVOKE ALL ON SCHEMA test FROM public;#取消此用户在其他所有模式下的权限
GRANT ALL ON SCHEMA test TO user1;#给予user1用户在test模式下的所有权限

限制用户user1在模式test下的创建对象权限，以及user1在其他模式下使用对象的权限；
REVOKE ALL ON SCHEMA test FROM public;
GRANT USEAGE ON SCHEMA test TO user1;
GRANT CREATE ON SCHEMA test TO user1;

为模式中的函数对象等设置相应的权限
GRANT SELECT ON month_end_snapshot TO public;

设置用户在模式中的默认权限，创建对象后，对象自动继承对应的用户权限；
ALTER DEFAULT PRIVILEGES FOR USER user1 IN SCHEMA test GRANT SELECT ON TALBES TO PUBLIC;
这里的模式类似于目录，数据表类似于子目录，函数对象等类似于目录下的文件，search_path类似于路径。

给用户分配单独的数据库

为单个用户创建同名的数据库
CREATE USER fred;
CREATE DATABASE fred owner = fred;
但是此时存在问题，默认情况下，新创建的用户拥有登陆其他数据库的能力，其他用户也可以登陆此数据库。因此数据库和角色创建完成后，一定要取消此用户对其他数据库的访问能力，同时取消其他用户访问此数据库的能力。此任务需要在一个事务处理中完成，SQL语句如下：

BEGIN;
REVOKE connect ON DATABASE fred  FROM public;    #取消其他用户连接fred数据库的权限
GRANT connect ON DATABASE fred  TO fred;    #将fred数据的连接权限赋予fred用户
COMMIT;

上面的事务处理中取消了其他用户登陆新建的数据库fred的权限，但是并没有做到取消新建的用户访问其他数据库的能力。（这个问题要等到后面学习了权限管理来解决。）
超级用户可以不受限制的连接到任何数据库和模式。经过处理以后，不在数据库中的用户无法看到库中的表以及任何对象。在库中的角色可以看到表和数据库的属主。

在一个系统上运行多个服务

在RHEL/CentOS/Federo平台上，需要先运行initdb命令先初始化一个数据目录，命令如下：
su - postgres /usr/pgsql/9.6/bin/initdb -D /var/lib/pgsql/<version>/datadir2
修改配置文件data2/postgresql.conf中的端口号，设置参数port=5433，然后用pg_ctl命令启动：
su - postgres /usr/pgsql/9.6/bin/pg_ctl start -D /var/lib/pgsql/<version>/datadir2
此时会使用默认的版本创建一个新的服务，根据端口来区分，如下所示：

tcp        0      0 192.168.1.244:5432      0.0.0.0:*               LISTEN      95999/postmaster    
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      95999/postmaster    
tcp        0      0 127.0.0.1:5433          0.0.0.0:*               LISTEN      112325/postgres     
tcp6       0      0 ::1:5432                :::*                    LISTEN      95999/postmaster    
tcp6       0      0 ::1:5433                :::*                    LISTEN      112325/postgres

原来配置在9.6/data目录下的postgresql进程监听在5432端口，新配置在9.6/data2目录下的进程监听在5433端口。

以上就是服务控制的所有内容。

5、服务控制

服务端的启动（start）、停止（stop）、重启（restart）、重载（reload）

启动

停止

重启

重载

服务端的会话连接管理

阻止新的连接

限制用户连接数

断开用户的连接

服务端的实例管理

为多租户进行数据库设计

使用多个模式

给用户分配单独的数据库

在一个系统上运行多个服务

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

PostgreSQL学习手册