4

I have pg_service.conf file with following config

pg_service.conf

[test-service]
host=localhost
port=5432
user=test-user
password=*****
dbname=test-table

While running following query shows error

Query

SELECT id, name 
FROM                                                                                                      
  DBLINK('service=test-service',
    'SELECT id, name FROM student' 
  ) AS sq0(id int, name text) order by 1;

ERROR

ERROR:  could not establish connection
DETAIL:  definition of service "test-service" not found
Venkatesan
  • 43
  • 1
  • 7
  • try `SELECT id, name FROM DBLINK('server=test-localhost port=5432 user=test-user password=*** dbname=dbname ', 'SELECT id, name FROM student' ) AS sq0(id int, name text) order by 1;` – Vivek S. Oct 28 '15 at 06:39
  • 1
    if i give connection string ('server=test-localhost port=5432 user=test-user password=*** dbname=dbname ') directly it's working fine. But I want to use that service name(test-service) instead of connection string. – Venkatesan Oct 30 '15 at 05:52

1 Answers1

0

Did you define PGSERVICEFILE and PGSERVICE environment variables? If not please define those in the environment file as follows,

export PGSERVICEFILE=${HOME}/.pg_service.conf   # location to the .pg_service.conf file
export PGSERVICE=test-service

If you going to use multiple schema, you can use .pg_service.conf to define db name, ip and port and .pgpass file to define user names and passwords. Please not that .pgpass should be in your home folder. Also above variables should be set.

Sample .pg_service.conf

[test-service]
host=localhost
port=5432
dbname=test-table

Sample .pgpass

localhost:5432:test-table:test-user1:******
localhost:5432:test-table:test-user2:******

Then you can execute the query from the terminal by, psql --user=test-user1 -c "query"

Amith Chinthaka
  • 1,015
  • 1
  • 17
  • 24