Hi All,
Is there any system table / view to see system level parameters that has been altered using " ALTER SYSTEM SET configuration='some_value' " command
Thanks
Hi ram1992,
The parameter changes altered through ALTER SYSTEM SET can be viewed through pg_settings.
Only postgresql.auto.conf gets updated through the alter system command through reload and SGHUP.
test=# select * from pg_settings where name = 'log_checkpoints';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | re
set_val | sourcefile | sourceline | pending_restart
-----------------+---------+------+-------------------------------------+-----------------------+------------+---------+---------+---------+---------+---------+----------+----------+---
--------+------------+------------+-----------------
log_checkpoints | off | | Reporting and Logging / What to Log | Logs each checkpoint. | | sighup | bool | default | | | | off | of
f | | | f
(1 row)
test=#
test=# alter system set log_checkpoints = on;
ALTER SYSTEM
test=#
test=# select * from pg_settings where name = 'log_checkpoints';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | re
set_val | sourcefile | sourceline | pending_restart
-----------------+---------+------+-------------------------------------+-----------------------+------------+---------+---------+---------+---------+---------+----------+----------+---
--------+------------+------------+-----------------
log_checkpoints | off | | Reporting and Logging / What to Log | Logs each checkpoint. | | sighup | bool | default | | | | off | of
f | | | f
(1 row)
test=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
test=#
test=# select * from pg_settings where name = 'log_checkpoints';
name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | bo
ot_val | reset_val | sourcefile | sourceline | pending_restart
-----------------+---------+------+-------------------------------------+-----------------------+------------+---------+---------+--------------------+---------+---------+----------+---
-------+-----------+-----------------------------------+------------+-----------------
log_checkpoints | on | | Reporting and Logging / What to Log | Logs each checkpoint. | | sighup | bool | configuration file | | | | of
f | on | /data01/data/postgresql.auto.conf | 3 | f
(1 row)
test=# \q
Thanks, Rahul
Is there any way to verify the same if paramteres are configured at user's session level (in past like a week ago)
Hi ram1992,
Not feasible.There are no system tables that can have the parameters changed at session level.
As said, the changes are session specific done with intention.
Hi @ram1992
I thik we can find user session level parameters using Pg_roles and pg_shadow.
select usename,useconfig from pg_shadow ;
select rolname,rolconfig from pg_roles;
Regards
Dineshkumar Ashokan
Hi @ram1992,
We can get the parameters modified at the session level with reloading the cluster as below :
1) postgresql.auto.conf
2) Database logs for the last week.
Regards,
Sudhir