cancel
Showing results for 
Search instead for 
Did you mean: 

View Database Level Set Parameters

SOLVED
Highlighted
Adventurer

View Database Level Set Parameters

Dear All,

 

Can anybody tell me how I can see parameters set at database level?

 

Example:

 

alter database test1 set temp_file_limit='128MB';

 

What is the SQL query which allows me to see changes that I have done for all databases at once?

 

Something similar to below, If I set a user level parameter,

 

alter user test_u set temp_file_limit='256MB';

 

I can see all user level parameters which I have set using,

 

select usename,useconfig from pg_shadow;

 

BR,

Jyrxs

1 ACCEPTED SOLUTION

Accepted Solutions
Adventurer

Re: View Database Level Set Parameters

Found the answer to my own question:

 

SELECT coalesce(role.rolname, 'database wide') as role,
               coalesce(db.datname, 'cluster wide') as database,
               setconfig as what_changed
FROM pg_db_role_setting role_setting
LEFT JOIN pg_roles role ON role.oid = role_setting.setrole
LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase;

 

Ref: https://dba.stackexchange.com/questions/40429/how-to-get-user-specific-settings-in-postgresql

1 REPLY
Adventurer

Re: View Database Level Set Parameters

Found the answer to my own question:

 

SELECT coalesce(role.rolname, 'database wide') as role,
               coalesce(db.datname, 'cluster wide') as database,
               setconfig as what_changed
FROM pg_db_role_setting role_setting
LEFT JOIN pg_roles role ON role.oid = role_setting.setrole
LEFT JOIN pg_database db ON db.oid = role_setting.setdatabase;

 

Ref: https://dba.stackexchange.com/questions/40429/how-to-get-user-specific-settings-in-postgresql