How to configure PostgreSQL client_min_messages on Heroku & Rails

Question

I am trying to reduce some logging noise I am getting from PostgreSQL on my Heroku/Rails application. Specifically, I am trying to configure the client_min_messages setting to warning instead of the default notice.

I followed the steps in this post and specified min_messages: warning in my database.yml file but that doesn't seem to have any effect on my Heroku PostgreSQL instance. I'm still seeing NOTICE messages in my logs and when I run SHOW client_min_messages on the database it still returns notice.

Here is a redacted example of the logs I'm seeing in Papertrail:

Nov 23 15:04:51 my-app-name-production app/postgres.123467 [COLOR] [1234-5]  sql_error_code = 00000 log_line="5733" application_name="puma: cluster worker 0: 4 [app]" NOTICE:  text-search query contains only stop words or doesn't contain lexemes, ignored

I can also confirm that the setting does seem to be in the Rails configuration - Rails.application.config.database_configuration[Rails.env] in a production console does show a hash containing "min_messages"=>"warning"

I also tried manually updating that via the PostgreSQL console - so SET client_min_messages TO WARNING; - but that setting doesn't 'stick'. It seems to be reset on the next session.

How do I configure client_min_messages to be warning on Heroku/Rails?

Answer 1

I think you want log_min_messages, not client_min_messages:

Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. The default is WARNING. Note that LOG has a different rank here than in client_min_messages. Only superusers and users with the appropriate SET privilege can change this setting.

I'm not sure if your database user will be allowed to set it, but you can try doing so at the database level:

ALTER DATABASE your_database
    SET log_min_messages TO 'warning';

If this doesn't work, and setting at the role or connection level doesn't work, and heroku pg:settings doesn't work (confirmed via other answers and comments), the answer might unfortunately be that this isn't possible on Heroku.

Heroku Postgres is a managed service, so the vendor makes certain decisions that aren't configurable. This might be one of those situations.