PG笔记（4）配置文件

运维笔记本 2019-12-20

2353

配置文件

查看配置文件

DB=# show config_file;
DB=# show hba_file;
DB=# show ident_file;

test=# select name,setting from pg_settings where category ='File Locations';
       name        |                 setting                  
-------------------+------------------------------------------
 config_file       | var/lib/postgresql/data/postgresql.conf
 data_directory    | var/lib/postgresql/data
 external_pid_file | 
 hba_file          | var/lib/postgresql/data/pg_hba.conf
 ident_file        | var/lib/postgresql/data/pg_ident.conf

postgresql.conf

“
postgresql.conf 文件包含了Postgresql服务能够正常运行所必需的基础设置以及新建数据库时所使用的默认配置。你可以在数据库级、用户级、会话级甚至函数级修改这些设置。
默认在 $PGDATA下。很多参数修改后都需要重启。9.4之后支持了alter system来修改，修改后的会报存在$PGDATA/postgresql.auto.conf下，可以reload或者 restart来使之生效。
若两个文件中存在同名配置项，则系统会优先使用postgresql.auto.conf设定的值。

查看当前会话的参数值

DB=# show all;

查看参数文件的参数值

select * from pg_file_settings;
                  sourcefile                   | sourceline | seqno |            name            |      setting       | applied | error 
-----------------------------------------------+------------+-------+----------------------------+--------------------+---------+-------
 /var/lib/postgresql/data/postgresql.conf      |         59 |     1 | listen_addresses           | *                  | t       | 
 /var/lib/postgresql/data/postgresql.conf      |         64 |     2 | max_connections            | 100                | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        121 |     3 | shared_buffers             | 128MB              | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        140 |     4 | dynamic_shared_memory_type | posix              | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        224 |     5 | max_wal_size               | 1GB                | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        225 |     6 | min_wal_size               | 80MB               | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        539 |     7 | log_timezone               | Etc/UTC            | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        649 |     8 | datestyle                  | iso, mdy           | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        651 |     9 | timezone                   | Etc/UTC            | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        665 |    10 | lc_messages                | en_US.utf8         | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        667 |    11 | lc_monetary                | en_US.utf8         | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        668 |    12 | lc_numeric                 | en_US.utf8         | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        669 |    13 | lc_time                    | en_US.utf8         | t       | 
 /var/lib/postgresql/data/postgresql.conf      |        672 |    14 | default_text_search_config | pg_catalog.english | t       | 
 /var/lib/postgresql/data/postgresql.auto.conf |          3 |    15 | work_mem                   | 8MB                | t       | 
(15 rows)

postgres=# select name,context,unit,setting,boot_val,reset_val from pg_settings where name in ('listen_address','max_connections','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem');
         name         |  context   | unit | setting | boot_val | reset_val 
----------------------+------------+------+---------+----------+-----------
 effective_cache_size | user       | 8kB  | 524288  | 524288   | 524288
 maintenance_work_mem | user       | kB   | 65536   | 65536    | 65536
 max_connections      | postmaster |      | 100     | 100      | 100
 shared_buffers       | postmaster | 8kB  | 16384   | 1024     | 16384
 work_mem             | user       | kB   | 8192    | 4096     | 8192
(5 rows)

如果将context设置为postmaster,那么更改此形参后需要重启PostgreSQL服务才能生效;如果将其设置为user,那么只需要执行一次重新加载即可全局生效。重启数据库服务会终止活动连接，但重新加载不会。

unit字段表示这些设置的单位。上面的输出结果中内存设置的单位可能会让你觉得有点乱，有些是以8 KB为单位，有些是以KB为单位。在postgresql.conf中设置内存时，请尽量选择一个合适的数值单位，比如我们要将内存设定为128 MB，那么“xxx= 128 MB”就是一个比较好的写法，而“xxx = 131072 KB”显然就不太好，虽然二者是等价的。

setting是指当前设置: boot val是指默认设置; reset_ val 是指重新启动服务器或重新加载设置之后的新设置n在postgresql.conf中修改了设置后，一定要记得查看一 .下setting和reset_Val并确保二者是一致的，否则说明设置并未生效，需要重新启动服务器或者重新加载设置。

参数生效

重启数据库
重新加载，重新加载的过程不会中断当前已经建立的连接

pg_ctl reload -D your_data_directory_here

超级用户登陆到任意一个数据库中执行SQL语句

select pg_reload_conf();

pgAdmin工具重新加载

例如：

查看某个参数值,比如参数work_mem

DB=# show work_mem

修改某个参数值,比如参数work_mem

DB=# alter system set work_mem='8MB'

执行reload生效

postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

使用alter system命令将修改postgresql.auto.conf文件，而不是postgresql.conf，这样可以很好的保护postgresql.conf文件
你使用很多alter system命令后搞的一团糟，,那么你只需要删除postgresql.auto.conf，再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。

查看是否归档

DB=# show archive_mode;

查看运行日志的相关配置，运行日志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。

pg_hba.conf

“
pg_hba.conf文件指定了允许哪些用户以何种方式连接到Postgresql数据库。该文件的修改可动态生效。黑名单和白名单设置。

# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

“
type 列有local，host，hostssl，hostnossl四种。

local是本地认证
database 可以是all，或者指定的数据库
user列可以是all，或者具体的用户
address 可以是ip或者网段
method比较重要，有"trust", "reject", "md5", "password", "scram-sha-256",

最常用的身份验证方式有下面这些：

trust
“
这是最不安全的身份验证模式。该模式允许用户“自证清白”，即可以不用密码就连接到数据库。只要源端IP地址、连接用户名、要访问的database名都与该条规则匹配，用户就可以连上来。trust 模式很不安全，因此应对其使用予以限制，即只能允许从数据库服务器本机发起的连接或者是同属内网的用户发起的连接使用此模式。但即使加了前述限制也不能保证安全，因为会有人通过伪装IP地址的方式来冒用此权限，所以有人认为该模式应该被彻底禁用。然而在单用户的桌面环境下这却是最常用的身份验证模式，因为一般这种场景下系统的安全性根本不是问题。连接时如果未指定用户名，那么默认会使用当前登录的操作系统用户名。
md5
“
该模式很常用，要求连接发起者携带用md5算法加密的密码。
password
“
不推荐，因为该模式使用明文密码进行身份验证，不安全。
ident
“
该身份验证模式下，系统会将请求发起者的操作系统用户映射为PostgreSQL数据库内部用户，并以该内部用户的权限登录，且此时无需提供登录密码。操作系统用户与数据库内部用户之间的映射关系会记录在Pg_ident.conf 文件中。
peer
“
该模式使用连接发起端的操作系统名进行身份验证。仅可用于Linux. BSD. Mac osx 和Soars并且仅可用于本地服务器发起的连接。
多种身份验证模式是可以同时使用的，即使是针对同一个 database 也可以这么做，也就是说我们可以针对同一个database设置多条身份验证规则，并且每条规则的身份验证模式都不一样。虽然身份验证模式很灵活，但请你务必牢记postgrsql对于pg_hba.conf中的规则的查找顺序是从上到到下，第一条匹配到的规则就是系统使用的规则。

pg_ident.conf

“
操作系统用户与数据库用户作映射

同名用户好办，各新建一个同名的操作系统用户和数据库用户，两个用户密码不必相同，但名字必须相同。用该用户登录到操作系统或su到该用户后，即可$ psql dbname。

如果不想新建同名用户，也可以配置pg_ident.conf文件。pg_ident.conf用来配置哪些操作系统用户可以映射为数据库用户。本文以PostgreSQL 9为例。

pg_ident.conf的格式如下：

# MAPNAME    SYSTEM-USERNAME    PG-USERNAME
usermap      username           dbuser

usermap为映射名，要在pg_hba.conf中用到，多个映射可以共用同一个映射名，username为操作系统用户名，dbuser为映射到的数据库用户。

例：操作系统用户 a1，使用数据库用户 b1 连接数据库，而操作系统用户 a2，使用数据库用户 b2 连接数据库。

pg_ident.conf如下：

# MAPNAME    SYSTEM-USERNAME    PG-USERNAME
mapa　　　　a1　　　　　　　　b1
mapa　　　　a2　　　　　　　　b2

pg_hba.conf如下：

# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
local    all      all                 ident  map=mapa

map为pg_hba.conf的auth-options项，map=mapa指示该认证条件使用mapa映射。指定映射后原本的同名操作系统用户就不能连接数据库了。

参考

https://cloud.tencent.com/developer/article/1469101

https://www.cnblogs.com/hiloves/archive/2011/08/24/2152144.html

postgresql

最后修改时间：2020-01-14 09:47:25

文章转载自运维笔记本，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。