Connecting to Postgres With psql and .pg_service.conf

James Blackwood-Sewell -

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

Amazing! But looking at the list of services I needed to add to my .pg_services.conf file was daunting—what if the Timescale Console could do this for me? I posted the idea to our front-end team’s Slack channel, and a few weeks later, a new button appeared:

Clicking on the link downloads a file that contains the configuration information for all my Timescale services, as well as some instructions for installing it on your system:

# This file includes configuration for all current services in your project
# To use it from psql either move the downloaded file to ~/.pg_service.conf
# or set the PGSERVICECONF environment variable to point at it.
# You will then be able to run 'psql service=service_name' to connect!

I’ve been using this new workflow every day. If you’re not on Timescale, then I’d encourage you to build your own .pg_service.conf file, and if you are, then let us make one for you! You can create a free Timescale account in just a couple of minutes, time you will surely win back with our connection database configuration link.