Search code examples
heroku-postgres

How can I set logging level for Heroku Postgresql?


Using Heroku with the Postgresql add-on. Upon reviewing my logs, it seems postgresql is logging every ... single ... transaction. I understand you can set the log level by doing something like (https://www.postgresql.org/docs/9.1/static/runtime-config-logging.html)

ALTER DATABASE my_database SET log_statement=error;

However, Heroku says

ERROR:  permission denied to set parameter "log_statement"

Note that might be a possible duplicate of how to turn off Heroku SQL logs from postgres but they never addressed how to get around the Heroku permissions. (Should I start a bounty on that ticket or keep this one?)

How do I modify the Heroku Postgresql logging levels?

Update

Folowing Get DB owner's name in PostgreSql

pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
ORDER BY 1;

I get the following result

Name        Owner
d7c0sfp134dmml  u2mqn7a68c982v
postgres    postgres
template0   postgres
template1   postgres

ALTER DATABASE d7c0sfp134dmml SET log_statement = error;

Results in

ERROR: permission denied to set parameter "log_statement" Query failed PostgreSQL said: permission denied to set parameter "log_statement"


Solution

  • As of August 8th 2017, Heroku Postgres now offers the ability to change this via the PGSettings feature. For example the command to log all statements on a standard-0 or above (no hobby plans) would be:

    heroku pg:settings:log-statement all postgresql-large-1234 -a sushi
    

    ref: https://devcenter.heroku.com/articles/heroku-postgres-settings#log-statement