4

I'm running PostgreSQL 10.2 and I'm trying to learn about logging. I've read that I can set the configuration for an individual database following this documentation:

https://www.postgresql.org/docs/10/manage-ag-config.html

But after I make a configuration change to a database, how do I check/view what the current settings are? I can't seem to find documentation on this.

Thanks!

Hoonerbean
  • 367
  • 1
  • 4
  • 12

1 Answers1

14

All settings are exposed through pg_settings

So you can query it: 

select *
from pg_settings
where name like '%log%';

If you change a setting for one specific database, that value will show up in pg_settings. The source column's value "database" will indicate that the configuration value was set on database level. The value "user" will indicate it was set on the current user's level.

A short version of that is show

show log_destination;

Or you can use current_setting()

As e.g. the setting for the logfile might contain placeholders, you can query the active value through the function pg_current_logfile()

Many configuration settings can be changed online, but you need to "activate" them by reloading the configuration using pg_reload_conf()

Note that some settings are only visible to the superuser.

  • I thought that records in the "pg_settings" table were related to the overall PostgreSQL server settings found in the "postgresql.conf" file. Do you know how to view the configuration settings that have been set for an individual database? Thanks. – Hoonerbean Nov 30 '18 at 21:12
  • Yes, that's still shown in `pg_settings`. The `source` column will indicate where a value was set. –  Nov 30 '18 at 21:22
  • Ahh. I see. When I query `pg_settings` it shows me the configuration settings for that database and the `source` column tells me where the database is picking up those settings from. Got it. Thanks! – Hoonerbean Nov 30 '18 at 21:39
  • 1
    I should `\c mydatabase` first, thanks! – WangYudong Apr 19 '22 at 07:15
  • I was not able to see the change in pg_settings until I reconnected to the database, thanks to @WangYudong. is there a reason why this is the case? and can we update the parameter without reconnecting? – Deepam Goel Jul 13 '22 at 15:26