PSQL Helper: Managing Connections and Simplifying Queries

For folks using PSQL to connect to PG databases, it can be a headache to manage a lot of different DB profile connections. PG makes it a bit easier by organizing db profiles in a file called, .pgpass. It contains one line for each DB Profile like this:

localhost:5432:mydb:myuser:mypassword

This file must reside in the user’s home directory and not have global permissions.

cd ~
touch .pgpass
chmod 600 .pgpass

But it only simplifies having to remember passwords. You still have to use a tedious psql command like this:

psql -h localhost -p 5432 -d mydb -U myuser

If the command matches a line in the .pgpass file, it will connect without you being prompted for a password.

Now comes the  Connection Service File to the rescue. Named .pg_service.conf, it is also like the .pgpass file in that it is located in your home directory. Also, like .pgpass, do the usual:

cd ~
touch .pg_service.conf
chmod 600 .pg_service.conf

The contents of this file are shown below for one profile example:

# Staging database for sales sector
[StagingDatabaseSales]
host=my-host-stage.cluster-whatever
dbname=mydb
port=5432
user=myuser
password=mypassword
application_name=MichaelV psql

Then to connect simply do this:

export PGSERVICE=StagingDatabaseSales
psql

Voila! Life just got a bit simpler. With a lot of profiles, it will be a wee bit difficult to remember them all, so grep to the rescue!

grep '\[' .pg_service.conf
[StagingDatabaseSales]
[another profile]

Now that we have got connecting using multiple DB Profiles working smoothly, let’s focus on the actual psql session. Sometimes there are things you do repeatedly once connected via psql. Wouldn’t it be nice to simplify that process as well? .psqlrc to the rescue again! Configure it just like the other 2 files:

cd ~
touch .psqlrc
chmod 600 .psqlrc

The example here makes it easier to execute a query I run all the time to see what’s a happenin’ in the database. Put the following snippet into your .psqlrc file.

\timing
\echo 'Administrative queries:\n'
\echo '\t:activity\t-- Server activity'

\set menu '\\i ~/.psqlrc'
\set activity 'select coalesce(datname,''N/A'') as datname, pid, coalesce(usename, ''N/A'') as usename, substring(coalesce(application_name, ''N/A''),1,15) as app, client_addr, wait_event, wait_event_type as we_type, CASE WHEN state = ''idle in transaction'' THEN ''idle in trans'' ELSE state END as state, (case when backend_type = ''logical replication launcher'' then ''logical rep launcher'' when backend_type = ''autovacuum launcher'' then ''autovac launcher'' when backend_type = ''autovacuum worker'' then ''autovac wrkr'' else backend_type end) as backend_type, to_char(backend_start, ''YYYY-MM-DD HH24:MI:SS'') as backend_start, coalesce(to_char(query_start, ''YYYY-MM-DD HH24:MI:SS''), ''N/A'') as query_start, cast(EXTRACT(EPOCH FROM (now() - backend_start)) as integer) as conn_secs,(case when state not in (''active'',''idle in transaction'') then cast(EXTRACT(EPOCH FROM (now() - state_change)) as integer) else -1 end) as idle_secs,(case when state in (''active'',''idle in transaction'') then cast(EXTRACT(EPOCH FROM (now() - query_start)) as integer) else -1 end) as q_secs, regexp_replace(replace(regexp_replace(substring(query,1,50), E''[\\n\\r]+'', '' '', ''g'' ),'' '',''''), ''[^\x20-\x7f\x0d\x1b]'', '''', ''g'') as query from pg_stat_activity where state in (''active'',''idle in transaction'') order by q_secs, state,usename;'
\echo ''

Now log into psql and run the activity query by simply typing :activity.

datname   | pid   | usename  | app           | client_addr | wait_event   | we_type | state  | backend_type   | backend_start       | query_start         | conn_secs | idle_secs | q_secs | query
----------+-------+----------+----------------+------------+--------------+---------+--------+----------------+---------------------+---------------------+-----------+-----------+--------+----------------------------------------------------
mydb      | 18928 | myuser   | MichaelV psql | 10.0.3.232  |              |         | active | client backend | 2022-06-21 12:56:09 | 2022-06-21 12:56:15 |         6 | -1        | 0      | select coalesce(datname,'N/A') as datname, pid,
mydb      | 30270 | dbadmin  |               | 10.0.3.232  |              |         | active | client backend | 2022-06-21 11:55:42 | 2022-06-21 12:56:14 |      3633 | -1        | 1      | DELETE FROM contracts.sales
mydb      | 30830 | dbadmin  | DataGrip      | 10.0.3.232  | DataFileRead | IO      | active | client backend | 2022-06-21 10:52:13 | 2022-06-21 11:55:26 |      7442 | -1        | 3649   | select po.gc_v_id, po.wid,gcv.details
(3 rows)

Presto! We got another way to make life in the psql world a whole lot easier.

— Michael Vitale,  Postgres DBA on Team Elephas

---