Setting application_name on Postgres/SQLAlchemy

Question

Looking at the output of select * from pg_stat_activity;, I see a column called application_name, described here.

I see psql sets this value correctly (to psql...), but my application code (psycopg2/SQLAlchemy) leaves it blank.

I'd like to set this to something useful, like web.1, web.2, etc, so I could later on correlate what I see in pg_stat_activity with what I see in my application logs.

I couldn't find how to set this field using SQLAlchemy (and if push comes to shove - even with raw sql; I'm using PostgresSQL 9.1.7 on Heroku, if that matters).

Am I missing something obvious?

zzzeek · Accepted Answer · 2013-03-28 20:12:43Z

89

the answer to this is a combination of:

http://initd.org/psycopg/docs/module.html#psycopg2.connect

Any other connection parameter supported by the client library/server can be passed either in the connection string or as keywords. The PostgreSQL documentation contains the complete list of the supported parameters. Also note that the same parameters can be passed to the client library using environment variables.

where the variable we need is:

http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-APPLICATION-NAME

The application_name can be any string of less than NAMEDATALEN characters (64 characters in a standard build). It is typically set by an application upon connection to the server. The name will be displayed in the pg_stat_activity view and included in CSV log entries. It can also be included in regular log entries via the log_line_prefix parameter. Only printable ASCII characters may be used in the application_name value. Other characters will be replaced with question marks (?).

combined with :

http://docs.sqlalchemy.org/en/rel_0_8/core/engines.html#custom-dbapi-args

String-based arguments can be passed directly from the URL string as query arguments: (example...) create_engine() also takes an argument connect_args which is an additional dictionary that will be passed to connect(). This can be used when arguments of a type other than string are required, and SQLAlchemy’s database connector has no type conversion logic present for that parameter

from that we get:

e = create_engine("postgresql://scott:tiger@localhost/test?application_name=myapp")

or:

e = create_engine("postgresql://scott:tiger@localhost/test", 
              connect_args={"application_name":"myapp"})

answered Mar 28, 2013 at 20:12

zzzeek

75.8k24 gold badges204 silver badges190 bronze badges

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

4 Comments

Yaniv Aknin Over a year ago

Hmmm, wait. I'm not sure why, but this works fine on my development database (9.1.4/OSX) but not on Heroku's shared database (9.1.8/Linux) (getting invalid connection option "application_name"). Same SA version, same psycopg2 version, probably different libpq etc. But psql application_name=foo_bar works fine, both locally and against Heroku. Any tips on that?

zzzeek Over a year ago

that would be psycopg2 doing that. try the psycopg2 connection directly and double check the version, as well as libpq version its built against.

Yaniv Aknin Over a year ago

For future readers, I'll note Heroku's libpq is from pg 8.4.9 by default. Looking at the commit application_name was added, I believe it was after 8.4 was feature frozen. In a chat with @craigkerstiens from Heroku, he confirmed that's the probable reason and suggested I vendor in a newer version of libpq.

Yaniv Aknin Over a year ago

Again, for future readers, here's the arduous way to vendor new libpq into your Heroku slug and set application_name successfully.

Bal Krishna Jha · Accepted Answer · 2023-04-12 05:53:55Z

7

If you're using asyncpg driver, you should use

conn = await asyncpg.connect(server_settings={'application_name': 'foo'})

and if you're using create_async_engine of sqlalchemy, you can use,

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine

create_async_engine(
    ...
    connect_args={"server_settings": {"application_name": "app_name"}},
)

src - https://github.com/MagicStack/asyncpg/issues/204#issuecomment-333917251

edited Apr 12, 2023 at 5:53

answered Jan 12, 2023 at 11:27

Bal Krishna Jha

7,5993 gold badges45 silver badges48 bronze badges

Collectives™ on Stack Overflow

Setting application_name on Postgres/SQLAlchemy

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related