PostgreSQL application_name can be set in the connection string. The view pg_stat_activity will show the application_name to help to identify the sessions. The article shows how to set application_name and how to benefit from it.

It is highly recommended to set the application_name in any application regardless of the programming language. The command within the PostgreSQL command line pgsql is: “set application_name = ‘<application>’;”. The application_name makes it easier to identify sessions during troubleshooting, monitoring, or similar activities. The following examples show the difference if application_name is not set and is set. At the end of the article are other possibilities to set application_name within an application.

Application name not set

The following Python code does not set an application name. The code connects to the DWH database, creates a cursor and sends a SELECT statement.

import psycopg2

conn = psycopg2.connect(“dbname=’dwh’ user=’postgres’ password=’…’ host=’172.17.0.2′ port=’5432′”)
cur = conn.cursor()
sql = “select count(*) from sales;”

cur.execute(sql)
cur.close()
conn.close()

The screenshot on the right shows the output of the Python code. There are 4 sessions. The last session (fourth rows) is my console querying pg_stat_activity. The psql console sets the application name by default. But which of the other three sessions in the Python session?

pg_stat_activity without application_name

Application name set

The following Python code does set an application name: ETL. The code connects to the DWH database, creates a cursor and sends a SELECT statement.

import psycopg2

conn = psycopg2.connect(“dbname=’dwh’ user=’postgres’ password=’…’ host=’172.17.0.2′ port=’5432′ application_name=’ETL'”)
cur = conn.cursor()
sql = “select count(*) from sales;”

cur.execute(sql)
cur.close()
conn.close()

The screenshot on the right shows the output of the Python code. There are again the 4 sessions. The last session (fourth rows) is my console querying pg_stat_activity. It is now clear that the third row is the Python code as the application_name is set to “ETL”.

pg_stat_activity without application_name

Other options to set application name

The example shows one possibility to set the application_name in Python. There are also other options:

  • set application_name in the connection string
    postgresql://postgres:<pw>@172.17.0.2/dwh?application_name=<application_name>
    or
    postgresql://postgres:<pw>@172.17.0.2/dwh”, connect_args={“application_name”:”<application_name>”}
  • execute the setting of application_name as a command:
    cur.execute(“set application_name = ‘<application>’”;)

There can be more connections from one application or connection pooling can be used. Application name is in any case useful.
Other programming languages offer analogous commands. Just do it!