Thread: Remote Connection
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
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 />
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
On Thu, Feb 12, 2009 at 5:04 PM, Bob Pawley <rjpawley@shaw.ca> wrote:
1. Configure pg_hba.conf to allow access from your network, internet, etc, as appropriate.HiI'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
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
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
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.
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
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 ------------------------------------------------------------------
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
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"
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.