TNS file functionality for PostgreSQL

I was exploring TNS file functionality for PostgreSQL and came across Connection service file functionality in PostgreSQL. As per PostgreSQL documentation,

The connection service file can be a per-user service file at ~/.pg_service.conf or the location specified by the environment variable PGSERVICEFILE, or it can be a system-wide file at `pg_config --sysconfdir`/pg_service.conf or in the directory specified by the environment variable PGSYSCONFDIR.

To connect to database, we need to specify the filename and DB connection detail.

We can use PGSYSCONFIDIR to specify directory or PGSERVICEFILE to specify exact file location. If we are using PGSYSCONFIDIR, then psql looks for .pg_service.conf file. If you are transitioning from Oracle DBA, PGSYSCONFIDIR is equivalent of TNS_ADMIN variable and .pg_service.conf is equivalent of tnsnames.ora.

Example : Create .pg_service.conf file which has host,port,dbname details

cat /home/postgres/.pg_service.conf
#Db connection file
[db1]
host=db1.cxxx.us-east-1.rds.amazonaws.com
port=5432
dbname=postgres

[db2]
host=db2.cxxx.rds.cn-north-1.amazonaws.com.cn
port=8192
dbname=postgres

In below example, we would specify PGSERVICEFILE variable to specify our connection service config file. PGSERVICE will be used to instruct psql client on which database to connect

[[email protected]]~% export PGSERVICEFILE=/home/postgres/.pg_service.conf
[[email protected]]~% export PGSERVICE=db2
[[email protected]]~% psql -U postgres
Password for user postgres:
psql (9.1.23, server 9.6.2)
WARNING: psql version 9.1, server version 9.6.
Some psql features might not work.
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "postgres" on host "db2.cxxx.rds.cn-north-1.amazonaws.com.cn" at port "8192".

Let’s connect to different database

[[email protected]]~% export PGSERVICE=db1
[[email protected]]~% psql -U postgres
Password for user postgres:
psql (9.1.23, server 9.5.4)
WARNING: psql version 9.1, server version 9.5.
Some psql features might not work.
Type "help" for help.

postgres=> \conninfo
You are connected to database "postgres" as user "postgres" on host "db1.cxxx.us-east-1.rds.amazonaws.com" at port "8192".

You can also use ldap functionality to maintain DB connection repository. To know more about it, refer to PostgreSQL link on LDAP lookup

Leave a Reply