postgresql参数的设置

#服务配置文件postgresql.conf [postgres@fnddb data]$ ls -l postgresql.* -rw-------.

1 postgres postgres 88 Feb 4 22:20 postgresql.auto.conf -rw-------.

1 postgres postgres 21253 Feb 5 00:10 postgresql.conf postgresql.conf是默认的服务配置文件.因此大多数的修改参数都是在此文件中修改

postgresql.auto.conf是通过ALTER SYSTEM命令修改的参数

,这里的参数值生效会覆盖postgresql.conf文件中的值. 手动不要修改postgresql.auto.conf文件

 

 

postgresql参数的设置

 +

 

set shared_buffers='15GB';

 

1. [postgres@fnddb data]$ cat postgresql.auto.conf
2. # Do not edit this file manually!
3.  # It will be overwritten by the ALTER SYSTEM command.

#如何让刚修改的配置文件生效.
服务器进程收到SIGHUP信号,就会触发读取这两个配置文件.并且会把此信号传播到其他运行中的服务进程去.
pg自带的重载配置文件功能如下:

1.  [postgres@fnddb data]$ pg_ctl reload
2.  server signaled
3.  [postgres@fnddb data]$ psql
4.  psql (9.4.0)
5.  Type "help" for help.
6.   
7.  postgres=# select pg_reload_conf();
8.  pg_reload_conf
9.  ----------------
10.  t
11.  
    (1 row)

#通过sql来修改参数配置 不是所有的参数都可以在数据库运行的时候进行修改

###数据库级别参数设置###

1.  postgres=# create user u01 password 'postgres';
2.  CREATE ROLE
3.  postgres=# create database db01 owner u01;
4.  CREATE DATABASE
5.  postgres=# alter database postgres set enable_indexscan=off;
6.  ALTER DATABASE
7.  postgres=# \d pg_settings
8.  View "pg_catalog.pg_settings"
9.  Column | Type | Modifiers
10.  ------------+---------+-----------
11.  name | text |
12.  ......
13.  postgres=# \x
14.  Expanded display is on.
15.  postgres=# select * from pg_settings where name='enable_indexscan';
16.  -[ RECORD 1 ]----------------------------------------------
17.  name | enable_indexscan
18.  setting | off
19.  ......

换个用户

1.  postgres=# \c postgres u01
2.  You are now connected to database "postgres" as user "u01".
3.  postgres=# \x
4.  Expanded display is off.
5.  postgres=> select current_setting('enable_indexscan');
6.  current_setting
7.  -----------------
8.  off
9.  (1 row)

切换到db01数据库去

1.  postgres=> \c db01
2.  You are now connected to database "db01" as user "u01".
3.  db01=> select current_setting('enable_indexscan');
4.  current_setting
5.  -----------------
6.  on
7.  (1 row)

###用户级别的参数设置###

1.  db01=> \c
2.  You are now connected to database "db01" as user "u01".
3.  db01=> select current_setting('enable_indexscan');
4.  current_setting
5.  -----------------
6.  on
7.  (1 row)
8.   
9.  db01=> \c postgres
10.  You are now connected to database "postgres" as user "u01".
11.  postgres=> select current_setting('enable_indexscan');
12.  current_setting
13.  -----------------
14.  off
15.  (1 row)
16.   
17.  postgres=> alter role u01 set enable_indexscan=off;
18.  ALTER ROLE
19.  postgres=> \c db01
20.  You are now connected to database "db01" as user "u01".
21.  db01=> select current_setting('enable_indexscan');
22.  current_setting
23.  -----------------
24.  off
25.  (1 row)

###会话级别的参数设置###

1.  db01=> set enable_indexscan=off;
2.  SET
3.  db01=> select current_setting('enable_indexscan');
4.  current_setting
5.  -----------------
6.  off
7.  (1 row)
8.   
9.  db01=> \c
10.  You are now connected to database "db01" as user "u01".
11.  db01=> select current_setting('enable_indexscan');
12.  current_setting
13.  -----------------
14.  on
15.  (1 row)

###可以通过update pg_setting 来设置会话级参数

1.  db01=> update pg_settings set setting='on' where name = 'enable_indexscan';
2.  -[ RECORD 1 ]--
3.  set_config | on
4.   
5.  UPDATE 0
6.  db01=> select * from pg_settings where name='enable_indexscan';
7.  -[ RECORD 1 ]----------------------------------------------
8.  name | enable_indexscan
9.  setting | on
10.  ......

#参数还原

1.  db01=> set enable_indexscan to default;
2.  SET
3.  db01=> alter role u01 set enable_indexscan to default;
4.  ALTER ROLE
5.  db01=> alter database db01 set enable_indexscan to default;
6.  ALTER DATABASE

#结论:

1. ALTER DATABASE --影响范围是数据库级别
2. ALTER ROLE --影响范围是用户级别
3. SET --影响范围是会话级别
4. pg_settings,current_setting 查看的都是会话级别的

优先级: 会话级别 -> 用户级别 -> 数据库级别