How to configure PostgreSQL client_min_messages on Heroku & Rails-CodePudding

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?

CodePudding user response：

You should be able to set it at the database level:

ALTER DATABASE your_database
    SET client_min_messages TO 'warning';

CodePudding user response：

Set this machine-wide instead of project-wide

psql -d rails_app_development
ALTER ROLE USER_NAME SET client_min_messages TO WARNING;

Change USER_NAME to be the user account for your machine.

CodePudding user response：

If the client_min_messages setting is not being retained when you set it in the PostgreSQL console, it may be because it is being overwritten by a default setting when a new session is started. To make the setting persistent, you will need to set it in the postgresql.conf file.

First, connect to your Heroku PostgreSQL instance using the heroku pg:psql command.

Then, run the following command to open the postgresql.conf file in the editor:

\e postgresql.conf

This will open the postgresql.conf file in the default editor for your system.

Next, find the following line in the file:

client_min_messages = notice

Change this line to:

client_min_messages = warning

Save and close the file.

Finally, restart the Heroku PostgreSQL instance by running the following command:

heroku pg:reset DATABASE_URL

This should set the client_min_messages setting to warning in the postgresql.conf file, making it persistent across sessions. You can verify this by running the SHOW client_min_messages command in the PostgreSQL console.