Once in a while I am rediscovering features in PostgreSQL, which have been there for many many years but which are so rarely used that people tend not to talk about them. One of those (in my experience) rarely used features is the ability to create a file called pg_service.conf.

The basic idea is to configure services in a config file and address them without having to worry about host, port, user and so on.

A simple pg_service.conf file

How does that work? Well, here is an example:

iMac:~ hs$ cat .pg_service.conf

# a sample service

[hansservice]

host=localhost

port=5432

dbname=test

user=hs

password=abc



[paulservice]

host=192.168.0.45

port=5432

dbname=xyz

user=paul

password=cde

In this case two services have been defined. Note that PostgreSQL uses a standard .ini-file format. Inside a section you can use all connection parameters available in PostgreSQL (a full list can be found here: http://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS).

The first important thing is that a service can be referenced by name. The way to do that is to set an environment variable:

 

iMac:~ hs$ export PGSERVICE=hansservice

A connection can now be established without passing parameters to psql:

 

iMac:~ hs$ psql

psql (9.5.0)

Type "help" for help.



test=#

Actually a pg_service.conf file is a pretty convenient thing to have. Maybe I should reconsider my behavior and use this thing more often.