Connecting to Postgres With psql and .pg_service.conf

Every now and then, I stumble upon random Postgres features that I’ve simply forgotten about. In this case, it’s somewhat poetic that the very thing I’d forgotten about (.pg_service.conf) is something that ensures I won’t forget how to connect to my database services ever again.

It probably comes as no surprise that the Developer Advocacy project on Timescale contains a lot of databases. Every database has a name, but unfortunately, you need to know the hostname and the port they are exposed on to connect to them. If you’re a Timescale user, you can get that information from the Timescale Console, but when you’re jumping between instances a lot, that becomes pretty tiresome.

Goodbye, psql -h; hello, .pg_service.conf

In the past, from a terminal, I’ve relied on searching my history for the correct psql command, which works but can be pretty hit-and-miss. Sometimes I can’t remember if I need to connect to g10hmvdlg9.jj7sbwx9nt.tsdb.cloud.timescale.com:37525 or jb4cnq3gyf.jj7sbwx9nt.tsdb.cloud.timescale.com:42188 because, sadly, I don’t have a photographic memory. I do know that I need to connect to my big_benchmark machine, so off to the Console I go to look it up.

A while ago, I remembered about the .pg_service.conf file, which lets you alias names with PostgreSQL connection information. I quickly made myself a file that looked like this:

[big_benchmark]
host=g10hmvdlg9.jj7sbwx9nt.tsdb.cloud.timescale.com
port=37525
dbname=tsdb
user=tsdbadmin

The command line psql client knows about this file, so now I can use the magic service flag to connect to my big_benchmark service directly:

$ psql service=big_benchmark

Password for user tsdbadmin: 
psql (15.4, server 15.5 (Ubuntu 15.5-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

tsdb=> 

And goodbye, .pg_services.conf; hello, database connection config button