Tip of the Month (From this months SeaPUG meeting)

If you have multiple application logging into the database, it is really nice to know what the application is, not just the user and the user's ip address.

After you make a connection to the database, execute the following sql:

SET application_name = 'xxxx';

You may string must be less than NAMEDATALEN. For a normal compile this is 64, so your max characters for application_name would be 63 characters.

Once you have set the application_name and are still connected, you may query the pg_stat_activity and see the application_name.

SELECT * FROM pg_stat_activity;

Some examples that you may see as the listed application_name:
pgAdmin III - Query Tool
pgAdmin III - Browser
psql
pg_restore
EMS SQL Manager for PostgreSQL

By default application_name is not logged, but you may add it to your log file by adding a %a to the log_line_prefix in the postgresql.conf.

Besides just having the application name, you may also choose to store into the name the version and/or the user name. The reason I say user name is that I have some applications that make database connections as a generic user and also log in as the windows user. This was on the generic user loging, you would be able to see the windows user name as part of the application_name.

Lloyd

Lloyd Albin
Database Programmer

Statistical Center for HIV/AIDS Research and Prevention (SCHARP)
Vaccine and Infectious Disease Division (VIDD)
Fred Hutchinson Cancer Research Center (FHCRC)
1100 Fairview Ave N., LE-400
P.O. Box 19024 * Seattle, WA 98109-1024
206.667.6477 * 206.667.4812 fax
Hours: 9:00am-6:00pm (Pacific)

CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential, proprietary, and/or privileged information protected by law. If you are not the intended recipient, you may not use, copy, or distribute this e-mail message or its attachments. If you believe you have received this e-mail message in error, please contact the sender by reply e-mail and destroy all copies of the original message.

P Please consider the environment before printing this email.