postgresql中参数的查看和修改

1.查看参数文件的位置

使用show 命令查看,比较常用的show  config_file.此还可以查看pg_settings数据字典.

test=# show config_file;

         config_file          

------------------------------

 /data/pgdata/postgresql.conf

(1 row)

test=# show hba_file 

test-# ;

         hba_file         

--------------------------

 /data/pgdata/pg_hba.conf

(1 row)

test=# show ident_file ;

         ident_file         

----------------------------

 /data/pgdata/pg_ident.conf

2.查看当前会话的参数值

可以使用show命令或者查看pg_settings字典.

使用show all可以查看全部的参数值.show 参数名查看指定参数

 

test=# show all;

-------------------------------------+------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------

 allow_system_table_mods             | off                                                  | Allows modifications of the structure of system tables.

 application_name                    | psql                                                 | Sets the application name to be reported in statistics and logs.

 archive_command                     | test ! -f /data/archive/%f && cp %p /data/archive/%f | Sets the shell command that will be called to archive a WAL file.

 archive_mode                        | on                                                   | Allows archiving of WAL files using archive_command.

 archive_timeout                     | 0                                                    | Forces a switch to the next WAL file if a new file has not been started within N seconds.

 array_nulls                         | on                                                   | Enable input of NULL elements in arrays.

...

 

test=# show work_mem;

 work_mem 

----------

 4MB

(1 row)

 

test=# \x

Expanded display is on.

test=# select * from pg_settings where name in ('work_mem')

test-# ;

-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------------

name            | work_mem

setting         | 4096

unit            | kB

category        | Resource Usage / Memory

short_desc      | Sets the maximum memory to be used for query workspaces.

extra_desc      | This much memory can be used by each internal sort operation and hash table before switching to temporary disk files.

context         | user

vartype         | integer

source          | default

min_val         | 64

max_val         | 2147483647

enumvals        | 

boot_val        | 4096

reset_val       | 4096

sourcefile      | 

sourceline      | 

pending_restart | f

3.修改pg的参数值

1.全局修改pg的参数.

有些参数只有当pg服务重启的时候才生效,典型的例子就是shared_buffers,定义了共享内存的大小.

许多参数在pg服务运行的时候就能修改.再更改之后像服务器执行一个reload操作,强制pg重新读取postgresql.conf,因此你只需要编辑postgresql.conf文件,再执行 pg_ctl reload 即可 . 对于需要重启的,在修改完postgresql后需要执行 pg_ctl restart

对于9.5以后的版本,可以通过查看pg_file_settings查看你设置的参数是否生效.例如如果你设置了一个参数需要重启数据库才能生效或者设置错误,那么在此字典中会出现报错.

 

test=# select * from pg_file_settings where error is not null;

            sourcefile             | sourceline | seqno |      name       | setting | applied |            error             

-----------------------------------+------------+-------+-----------------+---------+---------+------------------------------

 /data/pgdata/postgresql.auto.conf |          4 |    22 | max_connections | 10000   | f       | setting could not be applied

(1 row)

对于9.4以后的版本,你还可以使用 alter system 命令修改参数.使用alter system命令将修改postgresql.auto.conf文件,而不是postgresql.conf,这样可以很好的保护postgresql.conf文件,加入你使用很多alter system命令后搞的一团糟,那么你只需要删除postgresql.auto.conf,再重新加载即可.

 

test=# show work_mem;

 work_mem 

----------

 4MB

(1 row)

test=# alter system set work_mem='8MB';

ALTER SYSTEM

test=# show work_mem;

 work_mem 

----------

 4MB

(1 row)

查看postgresql.auto.conf:

 

[postgres@postgresql1 pgdata]$ cat postgresql.auto.conf 

# Do not edit this file manually!

# It will be overwritten by the ALTER SYSTEM command.

work_mem = '8MB'

使用pg_ctl reload重新load配置文件,再查看参数值:

 

test=# show work_mem ;

 work_mem 

----------

 8MB

(1 row)

2.直接使用set命令,在会话层修改,修改之后将被用于未来的每一个事务,只对当前会话有效:

 

test=# 

test=# set work_mem='16MB';

SET

test=# show work_mem;

 work_mem 

----------

 16MB

(1 row)

我们打开另外一个会话,查看work_mem参数,可以发现work_mem还是4MB

 

postgres=# show work_mem;

 work_mem 

----------

 4MB

(1 row)

3.set命令后添加 local关键字, 只在当前事务中修改,只在当前事务内有效:

 

test=# show work_mem;

 work_mem 

----------

 16MB

(1 row)

test=# begin;

BEGIN

test=# set local work_mem='8MB';

SET

test=# show work_mem;

 work_mem 

----------

 8MB

(1 row)

test=# commit;

COMMIT

test=# show work_mem;

 work_mem 

----------

 16MB

4.使用 reset恢复参数的默认值

再pg_settings字典reset_val字段表示了如果使用reset,则此参数恢复的默认值为多少

使用 reset 参数名 来恢复某个参数的默认值,使用 reset all来恢复所有的参数值.

 

test=# show work_mem;

 work_mem 

----------

 16MB

(1 row)

test=# reset work_mem;

RESET

test=# show work_mem;

 work_mem 

----------

 4MB

(1 row)

 

test=# reset all;

RESET

5.为特定的用户组设置参数

一.为特定的数据库里的所有的用户设置参数,例如为test数据库所有的连接设置work_mem为16MB:

 

test=# alter database test set work_mem='16MB';

ALTER DATABASE

二.为数据库中的某个特定用户设置参数.例如为brent用户,设置work_mem为2MB:

 

postgres=# alter role brent set work_mem='2MB';

ALTER ROLE

经过测试发现,如果你同时为数据库和用户设置了特定参数,那么以用户为准.例如上面的,如果我用brent用户连接到test数据库,那么我的work_mem应该为2MB:

 

postgres=# \c test brent

You are now connected to database "test" as user "brent".

test=> 

test=> 

test=> show work_mem;

 work_mem 

----------

 2MB

三.为某个特定用户连接到特定的数据库设置参数.例如为用户brent在数据库test中设置work_mem为8MB

 

test=# alter role brent in database test set work_mem='8MB';

ALTER ROLE

上面说的三种设置,优先级递增,也就是说,如果设置了1,2,3那么就以第3个为准,如果设置了1,2那么就是以2为准,以此类推.

pg对此的实现方法和当用户连接数据库的时候,立刻手动执行set命令的效果完全相同

查看你当前的参数值是从何处指定,可以通过查询pg_setttings中的source字段获取,例如如果设置了database级别的参数.那么查询结果应该如下:

test=# select name,setting,source from pg_settings where name='work_mem';

   name   | setting |  source  

----------+---------+----------

 work_mem | 16384   | database

其它的,例如设置了第三种:

 

test=# \c test brent

You are now connected to database "test" as user "brent".

test=> select name,setting,source from pg_settings where name='work_mem';

   name   | setting |    source     

----------+---------+---------------

 work_mem | 8192    | database user