Thread: dblink does not resolve DNS, but works with IP
If I have this:
CREATE OR REPLACE VIEW Link.Foo AS
select * from dblink(
'hostaddr=123.123.123.123 dbname=KitchenPC user=Website password=secret',
'select * from Foo') as ...
Then it works. However, if I do:
CREATE OR REPLACE VIEW Link.Foo AS
select * from dblink(
'hostaddr=db.domain.com dbname=KitchenPC user=Website password=secret',
'select * from Foo') as ...
Then I get:
ERROR: could not establish connection
DETAIL: could not translate host name "db.domain.com" to address: Unknown host
However, from a command prompt I can ping db.domain.com and get 123.123.123.123.
Does dblink just not support DNS resolution? I really don't want to hard code IP addresses in my scripts. Thanks!
On 05/14/2013 09:17 PM, Mike Christensen wrote: > If I have this: > > CREATE OR REPLACE VIEW Link.Foo AS > select * from dblink( > 'hostaddr=123.123.123.123 dbname=KitchenPC user=Website > password=secret', > 'select * from Foo') as ... > > Then it works. However, if I do: > > CREATE OR REPLACE VIEW Link.Foo AS > select * from dblink( > 'hostaddr=db.domain.com <http://db.domain.com> dbname=KitchenPC > user=Website password=secret', > 'select * from Foo') as ... > > Then I get: > > ERROR: could not establish connection > DETAIL: could not translate host name "db.domain.com > <http://db.domain.com>" to address: Unknown host > > However, from a command prompt I can ping db.domain.com > <http://db.domain.com> and get 123.123.123.123. > > Does dblink just not support DNS resolution? I really don't want to > hard code IP addresses in my scripts. Thanks! See below for explanation of hostaddr and host. Short version, you are looking for host: http://www.postgresql.org/docs/9.2/interactive/libpq-connect.html#LIBPQ-PARAMKEYWORDS -- Adrian Klaver adrian.klaver@gmail.com
Excellent! Thanks so much.
On Tue, May 14, 2013 at 9:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
On 05/14/2013 09:17 PM, Mike Christensen wrote:If I have this:'hostaddr=db.domain.com <http://db.domain.com> dbname=KitchenPC
CREATE OR REPLACE VIEW Link.Foo AS
select * from dblink(
'hostaddr=123.123.123.123 dbname=KitchenPC user=Website
password=secret',
'select * from Foo') as ...
Then it works. However, if I do:
CREATE OR REPLACE VIEW Link.Foo AS
select * from dblink(<http://db.domain.com>" to address: Unknown host <http://db.domain.com> and get 123.123.123.123.
user=Website password=secret',
'select * from Foo') as ...
Then I get:
ERROR: could not establish connection
DETAIL: could not translate host name "db.domain.com
Does dblink just not support DNS resolution? I really don't want to
hard code IP addresses in my scripts. Thanks!
See below for explanation of hostaddr and host. Short version, you are looking for host:
http://www.postgresql.org/docs/9.2/interactive/libpq-connect.html#LIBPQ-PARAMKEYWORDS
--
Adrian Klaver
adrian.klaver@gmail.com
Though I'm a bit curious why there's a host and hostaddr. Why can't it just resolve whatever you give it?
On Tue, May 14, 2013 at 9:31 PM, Mike Christensen <mike@kitchenpc.com> wrote:
Excellent! Thanks so much.On Tue, May 14, 2013 at 9:25 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:On 05/14/2013 09:17 PM, Mike Christensen wrote:If I have this:'hostaddr=db.domain.com <http://db.domain.com> dbname=KitchenPC
CREATE OR REPLACE VIEW Link.Foo AS
select * from dblink(
'hostaddr=123.123.123.123 dbname=KitchenPC user=Website
password=secret',
'select * from Foo') as ...
Then it works. However, if I do:
CREATE OR REPLACE VIEW Link.Foo AS
select * from dblink(<http://db.domain.com>" to address: Unknown host <http://db.domain.com> and get 123.123.123.123.
user=Website password=secret',
'select * from Foo') as ...
Then I get:
ERROR: could not establish connection
DETAIL: could not translate host name "db.domain.com
Does dblink just not support DNS resolution? I really don't want to
hard code IP addresses in my scripts. Thanks!
See below for explanation of hostaddr and host. Short version, you are looking for host:
http://www.postgresql.org/docs/9.2/interactive/libpq-connect.html#LIBPQ-PARAMKEYWORDS
--
Adrian Klaver
adrian.klaver@gmail.com
Mike Christensen <mike@kitchenpc.com> writes: > Though I'm a bit curious why there's a host and hostaddr. Why can't it > just resolve whatever you give it? Well, it will ... if you use the "host" parameter. The whole point of "hostaddr" is that for that parameter, it will not try a DNS lookup. You'd only use that if you had issues with the speed or reliability of your DNS service. regards, tom lane
Ah, gotcha! I guess whatever sample I was originally copying from used hostaddr for some reason.. Thanks for the clarification, Tom!
On Wed, May 15, 2013 at 6:08 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Mike Christensen <mike@kitchenpc.com> writes:Well, it will ... if you use the "host" parameter. The whole point of
> Though I'm a bit curious why there's a host and hostaddr. Why can't it
> just resolve whatever you give it?
"hostaddr" is that for that parameter, it will not try a DNS lookup.
You'd only use that if you had issues with the speed or reliability
of your DNS service.
regards, tom lane