四. 配置管理

配置管理在数据库管理中是非常重要的一件事，因为一个很小的参数配置可能就涉及到数据库性能很大的改变，因此在修改一个配置时，一定要在测试环境进行充分测试以后才能到生产环境上进行应用。
在上一节中，我们学习了怎么修改PostgreSQL的密码，以及如何查看PostgreSQL数据库中的一些基本信息。这一节中我们来学习怎么查看更多的配置信息，以及如何修改这些信息。

先登录到PostgreSQL上，在我们登陆到PostgreSQL的时候，就在客户端和服务端之间创建了一个会话（session）连接。PostgreSQL为每一个会话都生成一个默认的配置。就像我们每个人登录Windows电脑桌面的时候，使用不同的账号看到的桌面不一样，就是因为每个人的配置不一样。PostgreSQL的配置按照下面的等级来划分的：

1. 系统默认配置——postgresql.conf
2. 单个会话的配置
3. 会话里创建的事务配置

配置的作用范围逐渐变小，系统配置作用于全数据库，会话配置作用于当前建立的会话，事务配置作用于当前会话里创建的事务。事务配置会覆盖会话配置，会话配置覆盖系统配置。类似下图这样：

image.png

修改会话配置和事务配置

查看配置的命令是SHOW config_name，config_name就是你要查看的配置名称。
示例：查看当前会话的工作内存

postgres=# SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

可以看到，为当前会话分配的工作内存是4M，我嫌这个太小了，打算加大一点。

• 修改会话配置用的命令是SET config_name
• 修改事务配置的命令是SET LOCAL config_name

使用时有两种格式：

• 不带单位的格式，默认单位是kb，大小是64-2147483647，例如：
  SET work_mem=16384;
• 带单位的格式，值必须用单引号或双引号引起来，单位只能是kB，MB，GB和TB，例如：
  SET work_mem="16MB";

示例如下：

postgres=# SET work_mem=16384;
SET
postgres=# SHOW work_mem;
 work_mem 
----------
 16MB
(1 row)
postgres=# SET work_mem='32MB';
SET
postgres=# SHOW work_mem;
 work_mem 
----------
 32MB
(1 row)

上图说明这个参数已经正确设置了，那我们来尝试一下设置事务配置，将work_mem设置为64MB，命令如下：

postgres=# SET LOCAL work_mem=65536;
WARNING:  SET LOCAL can only be used in transaction blocks
SET

但是从上图中我们却看到提示出错，只能在事务块中设置这个变量。这下我们就了解了这两个命令怎么使用，以及在什么位置使用这个命令。上面说过，会话配置只在当前会话中生效，我们退出后再查看，验证一下：

postgres=# \q
-bash-4.2$ psql 
Password: 
psql (9.6.6)
Type "help" for help.

postgres=# SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

可以看到work_mem又恢复了正常配置，说明上面的说法是对的。而我们我们不想退出会话，又想这些配置恢复呢，则可以使用命令RESET config_name和RESET ALL，示例如下：

postgres=# SET work_mem=16384;
SET
postgres=# SHOW work_mem;
 work_mem 
----------
 16MB
(1 row)

postgres=# RESET work_mem;
RESET
postgres=# SHOW work_mem;
 work_mem 
----------
 4MB
(1 row)

从示例中我们可以看到，RESET将work_mem的值恢复到了默认配置。

会话配置的存放位置

当前会话的配置默认是保存在pg_catalog表的pg_settings视图里，pg_catalog是一张系统表。一般查看的字段有3个，分别是name、source、setting，即配置的名称、来源和设置值，这里截取了一部分默认的值，如下所示：

postgres=# select name, source, setting from pg_settings order by 2, 1;
                name                 |        source        |    setting                 
-------------------------------------+----------------------+---------------------
--------------------
 application_name                    | client               | psql
 client_encoding                     | client               | UTF8
 DateStyle                           | configuration file   | ISO, MDY
 default_text_search_config          | configuration file   | pg_catalog.english
 dynamic_shared_memory_type          | configuration file   | posix
 lc_messages                         | configuration file   | en_US.UTF-8
 lc_monetary                         | configuration file   | en_US.UTF-8
 lc_numeric                          | configuration file   | en_US.UTF-8
 lc_time                             | configuration file   | en_US.UTF-8
 listen_addresses                    | configuration file   | localhost, 192.168.1
.244
 log_destination                     | configuration file   | stderr
 log_directory                       | configuration file   | pg_log
 log_filename                        | configuration file   | postgresql-%a.log
 logging_collector                   | configuration file   | on
 log_line_prefix                     | configuration file   | < %m > 
 log_rotation_age                    | configuration file   | 1440
 log_rotation_size                   | configuration file   | 0
 log_timezone                        | configuration file   | PRC
 log_truncate_on_rotation            | configuration file   | on
 max_connections                     | configuration file   | 100
 shared_buffers                      | configuration file   | 16384
 TimeZone                            | configuration file   | PRC
 allow_system_table_mods             | default              | off
 archive_command                     | default              | (disabled)

source值是client表示该配置来自客户端，configuration file表示来自配置文件，default表示是服务端的默认值。
目前的理解是，来自configuration file的值都可以修改（配置文件或者通过SET 命令修改）。默认的值只能在编译安装时通过相应的编译的开关来修改。

修改配置文件

在上一节的内容中，我们修改过两个配置，分别是postgresql.conf文件里的listen_addresses和pg_hba.conf里的认证方式。postgresql.conf是PostgreSQL数据库的默认配置文件，里面的配置都是初始化时提供的默认配置。
如果我们修改了postgresql.conf文件，则重启PostgreSQL服务后，修改的配置生效。但是我们一般不建议这么做。这是因为，很多初始化配置都是经过很多生产环境实验后得出来的数值，当你修改以后发现数据库运行出了问题，再想改回原值时却发现已经不记得原来的数值了。因此如果你想修改默认的配置，建议你这样操作：

1. 不修改原来的配置，将原来的配置注释，另起一行写新的配置。
2. 将初始的配置文件备份一份，当修改后的配置文件出问题时，可以及时恢复
3. 通过PostgreSQL支持的include语法，在新的文件里编辑新的配置，然后在postgresql里使用include语法将新文件包含进来，此时新的配置会覆盖旧的配置，但是初始配置不会被修改；
4. 直接在命令行界面通过ALTER SYSTEM SET confi_name=value修改对应的配置，此时的修改不会写入postgresql.conf文件中，而是保存到postgresql.auto.conf文件中，当修改后的配置出错时，删除这个文件重启postgresql服务即可恢复。

修改配置文件以后，需要重新加载配置文件，重新加载配置文件也有两种格式：

1. root用户下，systemctl reload postgresql-9.6
2. 普通用户下，pg_ctl reload -D /path/to/data/directory/

为特定的用户组设置参数

PostgreSQL还支持为单独的用户组设置不同的参数，当这些用户登录时，即在特定的参数环境下使用提供的服务。命令格式如下：

1. 为某个数据库里的所有用户设置参数：
   ALTER DATABASE databasename SET configuration_parameter=value1
2. 为某个用户在所有的数据库下设置某个参数
   ALTER ROLE username SET configuration_parameter=value2
3. 为某个用户连接到特定的数据库时使用特定的参数
   ALTER ROLE username IN DATABASE databasename SET configuration_parameter=value3
   这样就可以实现对用户和数据库的参数配置的精细化管理。

几个关系到PostgreSQL性能的参数

系统参数

• /etc/sysctl.conf里的kernel.shmmax值，涉及到PostgreSQL的shared_buffers值。

PostgreSQL参数

• shared_buffers，PostgreSQL的共享内存，调大可以提高PostgreSQL的性能，但是不能抄过系统内存大小，特别是在Linux上，Linux上的OOM机制会在一个服务占用过大内存时，将其结束。
• wal_buffers，写入缓存，在写任务比较繁重的时候，可以适当调大这个值。一般情况下wal_buffers会根据shared_buffers的值来自动调整，但是在需要的时候需要手动调整。
• checkpoint_segments，写任务比较繁重时可以调大的值。
• work_mem，在大查询比较多的时候可以适当调大，但是这个值是针对每个会话设置的，调大的话，会话较多的情况下会导致内存占用过大。

以上就是配置管理的全部内容，这篇文章结束时，我自己也有若干问题：

1. 除了最开始的几个命令比较容易理解，后面的几个配置参数都没有明确的值或者范围，还需要进一步查找资料。