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
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
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