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 [pos[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