View Database Level Set Parameters

[jyrxs]

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

[jyrxs · Accepted solution]

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