How to associate a unique identifier with a PostgreSQL connection from a Python app?

Question

Say that I have connections from multiple Python instances to the same Postgres DB.

From the Postgres server, I want to be able to identify which connection is from which instance. I want to be able to run select * from pg_stat_activity where datname = 'db_name' and be able to distinguish the connections from one another.

I'm using psycopg2 as the database driver from the Python instances. Is there a way I can associate a string value or an UID with the connection string, or in another way during connection instantiation, and afterwards be able to see this value in one of the columns returned by the above query?

Maybe you can set up a different Postgresql user for each connection? — shevron, Oct 17 '20 at 11:27
The instances are replicas running in kubernetes, so i'm not sure how I would do that as they all use the same connection details. — Mike Hawkins, Oct 17 '20 at 11:49

score 3 · Accepted Answer · answered Oct 17 '20 at 12:03

You can use the application_name (optional) parameter which is offered by libpq

import psycopg2 as psp

stuff = {
  'dbname': 'twitters',
 # 'host': '127.0.0.1',
  'user': 'twitwww',
  'password': 'secret',
  'application_name': 'myap-1.0' }
  
  
conn = psp.connect(**stuff) # use kwargs
print(conn)
  
curs = conn.cursor()
print(curs)
  
curs.execute("select * from pg_stat_activity where datname = 'twitters' ")

for row in curs:
        print ''
        print (row)
        print ''
  
conn.close()

Result:

$ python psychopg.py
<connection object at 0x7f40e391a7c0; dsn: 'user=twitwww password=xxxxxxxxxxxx application_name=myap-1.0 dbname=twitters', closed: 0>
<cursor object at 0x7f40e38ff528; closed: 0>

(20529, 'twitters', 2586, 10, 'postgres', 'pgAdmin III - Browser', None, None, None, None, None, None, None, None, None, None, None, None, '<insufficient privilege>', None)


(20529, 'twitters', 25763, 5223264, 'twitwww', 'myap-1.0', None, None, -1, datetime.datetime(2020, 10, 17, 14, 3, 10, 946424, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)), datetime.datetime(2020, 10, 17, 14, 3, 10, 948886, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)), datetime.datetime(2020, 10, 17, 14, 3, 10, 949015, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)), datetime.datetime(2020, 10, 17, 14, 3, 10, 949015, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)), None, None, 'active', None, '119920335', "select * from pg_stat_activity where datname = 'twitters' ", 'client backend')

score 1 · Answer 2 · answered Oct 17 '20 at 12:01

I see that the pg_stat_activity includes the client ip address, which should be sufficient for your needs: your replicas will all have unique addresses, and you could expose those in your app or get at them with kubectl get pod -o wide command.

It also looks as if you can set an application_name that is associated with your Postgres connection that will show up in pg_stat_activity table. I've never tried that before, but there are some instructions in the answers to this question that should help out.

You beat me by one minute! (but I have the codez ...) – wildplasser Oct 17 '20 at 12:04 — wildplasser, Oct 17 '20 at 12:04

How to associate a unique identifier with a PostgreSQL connection from a Python app?

2 Answers2