cancel
Showing results for 
Search instead for 
Did you mean: 

Postgres System / session level set parameters

Level 2 Adventurer

Postgres System / session level set parameters

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

5 REPLIES 5
EDB Team Member

Re: Postgres System / session level set parameters

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

Highlighted
Level 2 Adventurer

Re: Postgres System / session level set parameters

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)

EDB Team Member

Re: Postgres System / session level set parameters

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.

Level 2 Adventurer

Re: Postgres System / session level set parameters

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

EDB Team Member

Re: Postgres System / session level set parameters

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