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:

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:

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:

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.