PostgreSQL的参数设置级别及查询各级别的参数值

PostgreSQL的参数设置级别及查询各级别的参数值

系统级别
用户/角色级别
用户/角色 + 数据库级别
会话级别



1.查询某参数在系统级别的设置
直接到postgresql.conf中去过滤



2.查询某参数在某用户级别的设置
testdb=# create user testa password 'testa';
CREATE ROLE
Time: 85.415 ms


testdb=# alter role testa set log_min_duration_statement = 100;
ALTER ROLE
Time: 12.714 ms

--方法一：查询pg_user表
testdb=# select * from pg_user where usename='testa';
 usename | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil |            useconfig             
---------+----------+-------------+----------+-----------+---------+----------+----------+----------------------------------
 testa   |    25697 | f           | f        | f         | f       | ******** |          | {log_min_duration_statement=100}
(1 row)

Time: 1.003 ms

--方法二：查询pg_db_role_setting表
testdb=# select * from pg_db_role_setting
testdb-# where setrole in (select usesysid from pg_user where usename in ('testa'))
testdb-# order by setrole,setdatabase;
 setdatabase | setrole |            setconfig             
-------------+---------+----------------------------------
           0 |   25697 | {log_min_duration_statement=100}
(1 rows)

Time: 1.132 ms



3.查询某参数在某用户下针对数据库级别的设置
testdb=# alter role testa in database testdb set client_min_messages='warning';
ALTER ROLE
Time: 2.111 ms
testdb=# select * from pg_db_role_setting
testdb-# where setrole in (select usesysid from pg_user where usename in ('testa'))
testdb-# order by setrole,setdatabase;
 setdatabase | setrole |            setconfig             
-------------+---------+----------------------------------
           0 |   25697 | {log_min_duration_statement=100}
       16412 |   25697 | {client_min_messages=warning}
(2 rows)

Time: 1.132 ms



4.查询某参数在当前会话级别的设置
testdb=> \c testdb testa
You are now connected to database "testdb" as user "testa".
testdb=>
testdb=> select user;
 current_user
--------------
 testa
(1 row)

testdb=> show client_min_messages;
 client_min_messages
---------------------
 warning
(1 row)

testdb=> set client_min_messages='error';
SET
testdb=> show client_min_messages;
 client_min_messages
---------------------
 error
(1 row)



重置/取消这些参数的设置
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL;