Postgres System / session level set parameters

[ram1992]

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

[Rahul]

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

[ram1992]

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)

[Rahul]

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.

[Diny2019]

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

[slonkar]

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