How can I set logging level for Heroku Postgresql?

Question

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"

What username do you use to login to PostgreSQL? Does it corresponds to owner of my_database? — Sasha
– Sasha, Commented Oct 8, 2016 at 17:44
OK, I see the owner of d7c0sfp134dmml. And what user do you log under? Just do SELECT current_user;. — Sasha
– Sasha, Commented Oct 12, 2016 at 18:48

xavriley · Accepted Answer · 2017-11-06 08:04:02Z

3

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

answered Nov 6, 2017 at 8:04

xavriley

6115 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sasha · Accepted Answer · 2016-10-12 20:12:11Z

1

You interpreted documentation wrongly. The log_statement parameter doesn't allow the error value. Possible values are instead: none, ddl, mod, all (and default pseudo-value).

You may also want to change log_min_messages parameter or log_min_error_statement parameter. They allow these values: DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL and PANIC. Set PANIC for minimal logging.
The fact is that for all three aforementioned parameters documentation says:

Only superusers can change this setting.

So, unless you can connect to PostgreSQL database as the postgres user or edit PostgreSQL configuration files directly, you probably cannot change logging options.

You may ask Heroku support about some workarounds. But I don't see how you can change logging options, because they probably won't give you superuser access. Maybe you can ask them to do that for you (i.e. change logging options) or you can use some special tools to filter logs (without changing logging options), I don't know.

edited Oct 12, 2016 at 20:12

answered Oct 12, 2016 at 19:39

Sasha

4,1121 gold badge36 silver badges54 bronze badges

1 Comment

Will Lovett Over a year ago

What's odd is that I don't get all these logging messages on my PostgreSql Hobby-Basic tier but I do on the Standard 0

Collectives™ on Stack Overflow

How can I set logging level for Heroku Postgresql?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related