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?
2
Answers
I think you want
log_min_messages
, notclient_min_messages
:I’m not sure if your database user will be allowed to set it, but you can try doing so at the database level:
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.
If all else fails and your log is flooded by the server logs you can’t control or client logs you can’t trace and switch off, Papertrail lets you filter out anything you don’t want. The database/client still generate them, Heroku still passes them to Papertrail, but Papertrail discards them once they come in.
Shotgun method, PostgreSQL-specific
And then you need to either wait, restart the app, force it to re-connect or restart the db/instance/server/cluster it connects to.
If your app opens and closes connections – just wait and with time old connections will be replaced by new ones using the new settings.
If it uses a pool, it’ll keep re-using connections it already has, so you’ll have to force it to re-open them for the change to propagate. You might need to restart the app, or they can be force-closed:
The reason is that there’s no way for you to alter session-level settings on the fly, from the outside – and all of the above only affects defaults for new sessions. The
REVOKE
will prevent the app user from changing the setting but it’ll also throw an error if they actually try. I’m leaving this in for future reference, keeping in mind that at the moment Heroku supports PostgreSQL versions up to 14, andREVOKE SET ON PARAMETER
was added in version 15.To need all these at once, you’d have to be seeing logs from both ends of each connection in your Papertrail, connecting to multiple databases, using different users, who also can keep changing the settings. Check one by one to isolate the root cause.
Context
There’s one log written to each client, one or more written on the server.
client_min_messages
applies the client log, sent back in each connection.log_min_messages
applies to the server log(s).Depending on what feeds the log into your Papertrail, you might need to change only one of these. Manipulating settings can always be tricky because of how and when they apply. You have multiple levels where parameters can be specified, then overriden:
system
-level parameters, loaded frompostgresql.conf
, thenpostgresql -c
/pg_ctl -o
andpostgresql.auto.conf
, which reflects changes applied usingALTER SYSTEM SET ...
or directly.database
overridessystem
. Applied withALTER DATABASE db SET...
user
/role
overridesdatabase
.ALTER ROLE user SET...
session
overridesuser
/role
. Changed withSET...
that clients also use upon connection init. If the value forclient_min_messages
set undermin_messages
is specified both indatabase.yml
andENV['DATABASE_URL']
, Rails will use the env setting, overriding the one found in .yml with itDATABASE_URL=postgres://localhost/rails_event_store_active_record?min_messages=warning
transaction
-level parameters are the most specific, overriding all else – they are basically session-level parameters that will change back to their initial setting at the end of transaction.SET LOCAL...
When a new session opens, it loads the system defaults, overrides them with the database-level, then role-level, after which clients typically issue their own
SET
s.It might be a good idea to make sure you’re using the settings in
database.yml
that you think you’re using, since it’s possible to have multiple sets of them. There can be some logic in your app that keeps altering the setting.