Thread: Remote Connection

Remote Connection

From
"Bob Pawley"
Date:
Hi
 
I've been operating on localhost successfully for some time.
 
I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router.
 
Is this possible?
 
My first attempts have been with Postgresql Admin.
 
I changed host to the IP address of the computer and input the other info.
 
I also ran - listen virtual; on the receiving server.
 
It hasn't connected successfully. I get the message 'Server not listening'
 
What else can I do??
 
Bob
 
 

Re: Remote Connection

From
justin
Date:
Bob Pawley wrote: <blockquote cite="mid:58F3235CF9DB4B39AF56CF0C7AF2D9F3@Bob"
type="cite"><style></style><div>Hi</div><div> </div><div>I'vebeen operating on localhost successfully for some
time.</div><div> </div><div>Iam now attempting to access a Postgresql 8.3 database installed on one of my other
computersconneted through a router.</div><div> </div><div>Is this possible?</div><div> </div><div>My first attempts
havebeen with Postgresql Admin.</div><div> </div><div>I changed host to the IP address of the computer and input the
otherinfo.</div><div> </div><div>I also ran - listen virtual; on the receiving server.</div><div> </div><div>It hasn't
connectedsuccessfully. I get the message 'Server not listening'</div><div> </div><div>What else can I
do??</div><div> </div><div>Bob</div></blockquote><br/> Postgresql is configure by default to drop all connections that
arenot localhost <br /><br /> see <br /><a class="moz-txt-link-freetext"
href="http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html">http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html</a><br
/>to make sure its configured to accept connections other than localhost<br /><br /> Second on the router make sure
port5432 is open and porting to the Postgresql which is the default port.  <br /><br /><br /><br /><br /> 

Re: Remote Connection

From
Adrian Klaver
Date:
On Thursday 12 February 2009 3:04:54 pm Bob Pawley wrote:
> Hi
>
> I've been operating on localhost successfully for some time.
>
> I am now attempting to access a Postgresql 8.3 database installed on one of
> my other computers conneted through a router.
>
> Is this possible?
>
> My first attempts have been with Postgresql Admin.
>
> I changed host to the IP address of the computer and input the other info.
>
> I also ran - listen virtual; on the receiving server.
>
> It hasn't connected successfully. I get the message 'Server not listening'
>
> What else can I do??
>
> Bob


First what is listen_addresses in postgres.conf set to?

From the postgres file:
listen_addresses = '*'    # what IP address(es) to listen on;
          # comma-separated list of addresses;
          # defaults to 'localhost', '*' = all
          # (change requires restart)

Second did you set up pg_hba.conf to allow connections from outside?


--
Adrian Klaver
aklaver@comcast.net

Re: Remote Connection

From
Andrew Gould
Date:
On Thu, Feb 12, 2009 at 5:04 PM, Bob Pawley <rjpawley@shaw.ca> wrote:
Hi
 
I've been operating on localhost successfully for some time.
 
I am now attempting to access a Postgresql 8.3 database installed on one of my other computers conneted through a router.
 
Is this possible?
 
My first attempts have been with Postgresql Admin.
 
I changed host to the IP address of the computer and input the other info.
 
I also ran - listen virtual; on the receiving server.
 
It hasn't connected successfully. I get the message 'Server not listening'
 
What else can I do??
 
Bob
 
  1.  Configure pg_hba.conf to allow access from your network, internet, etc, as appropriate.
  2.  If you're accessing the database from outside of your network, across a NAT, ensure that your router forwards port 5432 to your database server.
  3.  Ensure that port 5432 is not blocked by your firewalls.

Best of luck,

Andrew

Re: Remote Connection

From
Alan Hodgson
Date:
On Thursday 12 February 2009, "Bob Pawley" <rjpawley@shaw.ca> wrote:
> I also ran - listen virtual; on the receiving server.
>
> It hasn't connected successfully. I get the message 'Server not
> listening'
>
> What else can I do??

Setup PostgreSQL on the server to listen on its external interface. Adjust
the firewall settings if necessary.  Learn how to use netstat, telnet,
iptables and tcpdump (or equivalents on your choice of OS) to figure out
which step is missing.

--
Even a sixth-grader can figure out that you can’t borrow money to pay off
your debt

Re: Remote Connection

From
John R Pierce
Date:
Bob Pawley wrote:
> Hi
>
> I've been operating on localhost successfully for some time.
>
> I am now attempting to access a Postgresql 8.3 database installed on
> one of my other computers conneted through a router.
>
> Is this possible?
>
> My first attempts have been with Postgresql Admin.
> I changed host to the IP address of the computer and input the other info.
> I also ran - listen virtual; on the receiving server.
> It hasn't connected successfully. I get the message 'Server not listening'


presumably, when you say 'through a router', you mean, both client and
server systems are on the LAN side of the router, so its just  a local
area network?

A) make sure LISTEN_ADDRESS is * and not localhost in postgresql.conf

B) if the server has a firewall make sure you allow inbound port 5432/tcp

C) make sure pg_hba.conf on the postgres server has a line similar to...
    host all all     192.168.1.0/24     md5
where 192.168.1.* is the IP address of your local network.   md5 says to
use password authentication.



Re: Remote Connection

From
"Bob Pawley"
Date:
Hi John

You are right about the router.

I now have PG Admin displaying the server. However it still won't connect
even with the firewall off.

Actually, the message says the the connection was 'actively' rejected.

I am not sure what you mean by this IP format 192.168.1.0/24.

I notice that the pg_hba file also includes an adress in similar format -
127.0.0.1/32.

How does this compare to my address 192.168.1.100?

Thanks in advance.

Bob

----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Thursday, February 12, 2009 3:26 PM
Subject: Re: [GENERAL] Remote Connection


> Bob Pawley wrote:
>> Hi
>>  I've been operating on localhost successfully for some time.
>>  I am now attempting to access a Postgresql 8.3 database installed on one
>> of my other computers conneted through a router.
>>  Is this possible?
>>  My first attempts have been with Postgresql Admin.
>> I changed host to the IP address of the computer and input the other
>> info.
>> I also ran - listen virtual; on the receiving server.
>> It hasn't connected successfully. I get the message 'Server not
>> listening'
>
>
> presumably, when you say 'through a router', you mean, both client and
> server systems are on the LAN side of the router, so its just  a local
> area network?
>
> A) make sure LISTEN_ADDRESS is * and not localhost in postgresql.conf
>
> B) if the server has a firewall make sure you allow inbound port 5432/tcp
>
> C) make sure pg_hba.conf on the postgres server has a line similar to...
>    host all all     192.168.1.0/24     md5
> where 192.168.1.* is the IP address of your local network.   md5 says to
> use password authentication.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Remote Connection

From
Raymond O'Donnell
Date:
On 13/02/2009 18:24, Bob Pawley wrote:

> I am not sure what you mean by this IP format 192.168.1.0/24.

Hi Bob,

That's what's called a CIDR address - have a look at
http://en.wikipedia.org/wiki/CIDR - and in this instance it means that
any host in the address range 192.168.1.1 - 192.168.1.254 with a network
mask of 255.255.255.0 should be able to connect.

Ray.


------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: Remote Connection

From
"Bob Pawley"
Date:
In pg_hba there is a reference to reloading postmaster using pg_ctl.

Does postmaster reload when the server is restarted??

Bob
----- Original Message -----
From: "Raymond O'Donnell" <rod@iol.ie>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "John R Pierce" <pierce@hogranch.com>; "PostgreSQL"
<pgsql-general@postgresql.org>
Sent: Friday, February 13, 2009 10:31 AM
Subject: Re: [GENERAL] Remote Connection


> On 13/02/2009 18:24, Bob Pawley wrote:
>
>> I am not sure what you mean by this IP format 192.168.1.0/24.
>
> Hi Bob,
>
> That's what's called a CIDR address - have a look at
> http://en.wikipedia.org/wiki/CIDR - and in this instance it means that
> any host in the address range 192.168.1.1 - 192.168.1.254 with a network
> mask of 255.255.255.0 should be able to connect.
>
> Ray.
>
>
> ------------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: Remote Connection

From
John R Pierce
Date:
Bob Pawley wrote:
> In pg_hba there is a reference to reloading postmaster using pg_ctl.
>
> Does postmaster reload when the server is restarted??


depending on your OS, there's a variety of ways of forcing the
postmaster to reload or restart....

some changes require a restart, like LISTEN_ADDRESS, others just a
reload (pg_hba.conf changes), so for a restart, just replace the word
reload with restart in the following...

RHEL, Fedora, CentOS...

    # service postgresql reload

other SysV init based systems...

    # /etc/init.d/postgresql reload

Solaris 10, using SMF...

   # svcadm refresh svc:/application/database/postgresql:version_82_64bit
(or restart instead of refresh, and the service name will vary per the
version)

most Unix systems without a OS specific service manager script...

    # su - postgres -c "pg_ctl reload -D /path/to/pg/data"



Re: Remote Connection

From
Scott Marlowe
Date:
On Fri, Feb 13, 2009 at 3:43 PM, Bob Pawley <rjpawley@shaw.ca> wrote:
> In pg_hba there is a reference to reloading postmaster using pg_ctl.
>
> Does postmaster reload when the server is restarted??

Yep.  restart shuts down pgsql, and then starts it up fresh, so it has
to read its config files etc.  Reload is useful because it doesn't
shut down a server, which can be handy on 24/7 machines that need
config changes.

As noted by others, some changes require a restart, basically the
things the server can't change while it's running, like share_buffers,
which are allocated at start up.