Thread: Remote Connection Help

I am at a loss for what to do. I have read article after article about how to allow remote connections on my PostgreSQL server and none of what the articles say do, worked for me. I have edited the “postgresql.conf” file and changed “listen_address = ‘localhost’ to listen_address = ‘*’. I have even tried it commented out and uncommented and I get the same results. I also edited the “pg_hba.conf” file and added the following at the end of the file:

 

host all all 0.0.0.0/0 md5

host all all ::/0 md5

For testing connection purposes I'm used to change md5 to trust, this way you won't have troubles with passwords nor users.

 

After that I restart the server, try to connect remotely, and I get nowhere. I have even added the following rules to my iptables:

 

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

 

iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

Have you tried turning of iptables service? If you turn it off and try to connect remotely after that, you can test if the problem is in iptables or not.

 

My computer that is running the server is Ubuntu and it has a static IP. I am trying to connect remotely with computers running Windows 10 using the static IP. When I run pgAdmin from my Windows 10 machine, or use the command line to connect, I get the following error:

 

unable to connect to server:

 

could not connect to server: Connection refused (Ox0000274D/10061) Is the server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

 

I would like to be able to connect to my Ubuntu PostgreSQL server from all Windows 10 machines as well as from a client on my Android phone while away from home. That is my goal now. I am hoping that someone can help me to get this working. This is been very frustrating.

 

El 21/11/19 a las 15:21, Jason L. Amerson escribió:

I am at a loss for what to do. I have read article after article about how to allow remote connections on my PostgreSQL server and none of what the articles say do, worked for me. I have edited the “postgresql.conf” file and changed “listen_address = ‘localhost’ to listen_address = ‘*’.

I think that's "listen_addresses" on recent versions of postgresql.

Did you stop and restart the service after you edited the config file? Check the logs for errors too, maybe.

I have even tried it commented out and uncommented and I get the same results. I also edited the “pg_hba.conf” file and added the following at the end of the file:

 

host all all 0.0.0.0/0 md5

host all all ::/0 md5

The first line in pg_hba.conf that matches a connection will take effect; later lines won't.

For testing connection purposes I'm used to change md5 to trust, this way you won't have troubles with passwords nor users.

Don't do this on a machine that's reachable from the open internet, ever. It's asking to get your box compromised.

 

My computer that is running the server is Ubuntu and it has a static IP. I am trying to connect remotely with computers running Windows 10 using the static IP. When I run pgAdmin from my Windows 10 machine, or use the command line to connect, I get the following error:

 

unable to connect to server:

 

could not connect to server: Connection refused (Ox0000274D/10061) Is the server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

 

I would like to be able to connect to my Ubuntu PostgreSQL server from all Windows 10 machines as well as from a client on my Android phone while away from home. That is my goal now. I am hoping that someone can help me to get this working. This is been very frustrating.

 

Can you connect to your server on it's external address at all? i.e. if it's external IP address is 10.11.12.13, can you run "psql -h 10.11.12.13" on your ubuntu box and connect / log in?

If you can then postgresql is configured correctly and you can focus on where the issue on the network is.

If not, then the problem is the local machine, either postgresql configuration or _maybe_ local network configuration silliness.

Cheers,
  Steve

On 11/21/19 7:15 AM, Jason L. Amerson wrote:
> Steve,
> 
> I cannot connect to the server by “psql -h xx.xx.xx.xx.” I can connect 
> to my Ubuntu machine from other computers using SSH and I can connect to 
> PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL 
> either through a client machine or if I run “psql -h xx.xx.xx.xx” while 
> using my Ubuntu machine.

1) Is the Postgres server listening on port 5432?

2) What is the full remote connection string you are using?

3) What is the remote machine, a hosted virtual machine, your own 
physical machine, etc?

> 
> Jason L. Amerson
> 
> *From:*Jason L. Amerson <drjason@alphagenius.org>
> *Sent:* Thursday, November 21, 2019 09:22 AM
> *To:* PostgreSQL <pgsql-general@lists.postgresql.org>
> *Subject:* Remote Connection Help
> 
> I am at a loss for what to do. I have read article after article about 
> how to allow remote connections on my PostgreSQL server and none of what 
> the articles say do, worked for me. I have edited the “postgresql.conf” 
> file and changed “listen_address = ‘localhost’ to listen_address = ‘*’. 
> I have even tried it commented out and uncommented and I get the same 
> results. I also edited the “pg_hba.conf” file and added the following at 
> the end of the file:
> 
> host all all 0.0.0.0/0 md5
> 
> host all all ::/0 md5
> 
> After that I restart the server, try to connect remotely, and I get 
> nowhere. I have even added the following rules to my iptables:
> 
> iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx  
> --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
> 
> iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport 
> 1024:65535 -m state --state ESTABLISHED -j ACCEPT
> 
> My computer that is running the server is Ubuntu and it has a static IP. 
> I am trying to connect remotely with computers running Windows 10 using 
> the static IP. When I run pgAdmin from my Windows 10 machine, or use the 
> command line to connect, I get the following error:
> 
> unable to connect to server:
> 
> could not connect to server: Connection refused (Ox0000274D/10061) Is 
> the server running on host " xx.xx.xx.xx" and accepting
> 
> TCP/IP connections on port 5432'
> 
> I would like to be able to connect to my Ubuntu PostgreSQL server from 
> all Windows 10 machines as well as from a client on my Android phone 
> while away from home. That is my goal now. I am hoping that someone can 
> help me to get this working. This is been very frustrating.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

1) I am not sure if Postgres server is listening on port 5432. How do I
check?

2) I have tried "psql -h xx.xx.xx.xx" and "psql -h xx.xx.xx.xx -U postgres."
I even tried to telnet to it using the static IP and port 5432 but it would
not connect. I can connect to it remotely using the static IP with SSH.

3) It is my own physical machine. It is running Ubuntu and has a static IP.

Jason L. Amerson


-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com> 
Sent: Thursday, November 21, 2019 11:11 AM
To: Jason L. Amerson <drjason@alphagenius.org>; 'PostgreSQL'
<pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On 11/21/19 7:15 AM, Jason L. Amerson wrote:
> Steve,
> 
> I cannot connect to the server by "psql -h xx.xx.xx.xx." I can connect 
> to my Ubuntu machine from other computers using SSH and I can connect 
> to PostgreSQL if I SSH first. But I cannot connect directly to 
> PostgreSQL either through a client machine or if I run "psql -h 
> xx.xx.xx.xx" while using my Ubuntu machine.

1) Is the Postgres server listening on port 5432?

2) What is the full remote connection string you are using?

3) What is the remote machine, a hosted virtual machine, your own physical
machine, etc?

> 
> Jason L. Amerson
> 
> *From:*Jason L. Amerson <drjason@alphagenius.org>
> *Sent:* Thursday, November 21, 2019 09:22 AM
> *To:* PostgreSQL <pgsql-general@lists.postgresql.org>
> *Subject:* Remote Connection Help
> 
> I am at a loss for what to do. I have read article after article about 
> how to allow remote connections on my PostgreSQL server and none of 
> what the articles say do, worked for me. I have edited the
"postgresql.conf"
> file and changed "listen_address = 'localhost' to listen_address = '*'. 
> I have even tried it commented out and uncommented and I get the same 
> results. I also edited the "pg_hba.conf" file and added the following 
> at the end of the file:
> 
> host all all 0.0.0.0/0 md5
> 
> host all all ::/0 md5
> 
> After that I restart the server, try to connect remotely, and I get 
> nowhere. I have even added the following rules to my iptables:
> 
> iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx 
> --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
> 
> iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport
> 1024:65535 -m state --state ESTABLISHED -j ACCEPT
> 
> My computer that is running the server is Ubuntu and it has a static IP. 
> I am trying to connect remotely with computers running Windows 10 
> using the static IP. When I run pgAdmin from my Windows 10 machine, or 
> use the command line to connect, I get the following error:
> 
> unable to connect to server:
> 
> could not connect to server: Connection refused (Ox0000274D/10061) Is 
> the server running on host " xx.xx.xx.xx" and accepting
> 
> TCP/IP connections on port 5432'
> 
> I would like to be able to connect to my Ubuntu PostgreSQL server from 
> all Windows 10 machines as well as from a client on my Android phone 
> while away from home. That is my goal now. I am hoping that someone 
> can help me to get this working. This is been very frustrating.
> 


--
Adrian Klaver
adrian.klaver@aklaver.com

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> I connected to PostgreSQL locally. I ran “show listen_addresses;” and it returned “localhost.” I ran “show port;” and
itreturned “5432.” I am now confused. I edited the “postgresql.conf” file and change the setting to ‘*’. Then I
restartedthe server with “service postgresql restart.” I was in root since I had to edit the config files. I thought
maybeI edited the wrong file, like maybe there were two in two different locations or something. I ran “show
confg_file;”and it returned “/usr/local/psql/data/postgresql.conf.” That is the same file I edited from the start. To
besure, I edited the file by using “nano /usr/local/psql/data/postgresql.conf.” I went down and found that I did have
itas “listen_addresses = ‘*’ yet when I run “show listen_addresses”, it shows “localhost.” I am confused. When I run
“netstat-nlt”, the results show that it is listening to “127.0.0.1:5432.” 

According to what you wrote here, you did everything right, so it's
something you failed to mention.

One thing I'm wondering is whether you removed the comment symbol (#)
from the listen_addresses line when you edited it.  As installed,
postgresql.conf is pretty much all comments.

You might get more insight from

select * from pg_settings where name = 'listen_addresses';

particularly the source, sourcefile, sourceline fields.

            regards, tom lane

Yes "listen_addresses" is not commented. I did notice when I did the netstat, for tcp, it was all "127.0.0.1" on
variousports including 5432 but I have a listing for tcp6 that has my static IP using port 32305. Would that make a
difference?

Jason L. Amerson

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'Adrian Klaver' <adrian.klaver@aklaver.com>; 'PostgreSQL'
<pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> I connected to PostgreSQL locally. I ran “show listen_addresses;” and it returned “localhost.” I ran “show port;” and
itreturned “5432.” I am now confused. I edited the “postgresql.conf” file and change the setting to ‘*’. Then I
restartedthe server with “service postgresql restart.” I was in root since I had to edit the config files. I thought
maybeI edited the wrong file, like maybe there were two in two different locations or something. I ran “show
confg_file;”and it returned “/usr/local/psql/data/postgresql.conf.” That is the same file I edited from the start. To
besure, I edited the file by using “nano /usr/local/psql/data/postgresql.conf.” I went down and found that I did have
itas “listen_addresses = ‘*’ yet when I run “show listen_addresses”, it shows “localhost.” I am confused. When I run
“netstat-nlt”, the results show that it is listening to “127.0.0.1:5432.” 

According to what you wrote here, you did everything right, so it's something you failed to mention.

One thing I'm wondering is whether you removed the comment symbol (#) from the listen_addresses line when you edited
it. As installed, postgresql.conf is pretty much all comments. 

You might get more insight from

select * from pg_settings where name = 'listen_addresses';

particularly the source, sourcefile, sourceline fields.

            regards, tom lane

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> Yes "listen_addresses" is not commented. I did notice when I did the netstat, for tcp, it was all "127.0.0.1" on
variousports including 5432 but I have a listing for tcp6 that has my static IP using port 32305. Would that make a
difference?

Hm, well, *something* is overriding the setting.  What did you
find in pg_settings?

            regards, tom lane

Mark Johnson <remi9898@gmail.com> writes:
> As I recall, if the listening address is set to '*' but is showing
> localhost, then the problem you describe is likely due to missing an IPv6
> address in pg_hba.conf.

No, the contents of pg_hba.conf don't directly impact the listen_addresses
setting.  Also, if that's where the problem is, I'd expect a failure
complaining about "no pg_hba.conf entry for <connection info>".  The
reported "connection refused" message suggests strongly that the
postmaster isn't actually listening on the desired port, which also
implicates listen_addresses rather than anything else.  (I think it
could also be caused by a firewall filter, if the firewall is configured
to send back a TCP RST rather than just drop the packet.  But if "show
listen_addresses" isn't showing what we expect, that's the first thing
to fix.)

The OP may well need to adjust pg_hba.conf too, but he's not got
that far yet :-(

            regards, tom lane

pg_settings still show localhost. I went back and added the line that
someone suggested to my "pg_hba" file so the end of mine now looks like
this:

host all all 0.0.0.0/0 md5
host all all ::1/128 md5

When I run "netstat -nlt | grep 5432", I still only get "tcp
127.0.0.1:5432." As I mentioned be-fore, I also see "127.0.0.1" on various
ports including 5432 but I have a listing for tcp6 that has my static IP
using port 32305. Is it supposed to be like that? Also, her is the weird
thing, I have two "postgresql.conf" and "pg_hba" files in two different
locations. I have one in "/usr/local/pgsql/data" and another set at
"/etc/postgresql/9.4/main." I just discovered this situation. I edited both
sets of files to have the same setting and still nothing. It seems that
some-thing very screwy is going on.

Jason L. Amerson


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Thursday, November 21, 2019 02:53 PM
To: Mark Johnson <remi9898@gmail.com>
Cc: Jason L. Amerson <drjason@alphagenius.org>; Steve Crawford
<scrawford@pinpointresearch.com>; Adrian Klaver <adrian.klaver@aklaver.com>;
PostgreSQL <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

Mark Johnson <remi9898@gmail.com> writes:
> As I recall, if the listening address is set to '*' but is showing 
> localhost, then the problem you describe is likely due to missing an 
> IPv6 address in pg_hba.conf.

No, the contents of pg_hba.conf don't directly impact the listen_addresses
setting.  Also, if that's where the problem is, I'd expect a failure
complaining about "no pg_hba.conf entry for <connection info>".  The
reported "connection refused" message suggests strongly that the postmaster
isn't actually listening on the desired port, which also implicates
listen_addresses rather than anything else.  (I think it could also be
caused by a firewall filter, if the firewall is configured to send back a
TCP RST rather than just drop the packet.  But if "show listen_addresses"
isn't showing what we expect, that's the first thing to fix.)

The OP may well need to adjust pg_hba.conf too, but he's not got that far
yet :-(

            regards, tom lane

pg_settings show localhost.

Jason L. Amerson


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Thursday, November 21, 2019 02:42 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'Adrian Klaver'
<adrian.klaver@aklaver.com>; 'PostgreSQL'
<pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> Yes "listen_addresses" is not commented. I did notice when I did the
netstat, for tcp, it was all "127.0.0.1" on various ports including 5432 but
I have a listing for tcp6 that has my static IP using port 32305. Would that
make a difference?

Hm, well, *something* is overriding the setting.  What did you find in
pg_settings?

            regards, tom lane

On 11/21/19 12:07 PM, Jason L. Amerson wrote:
> pg_settings show localhost.

Lets back up a bit and:

1) Show output of ps ax | grep post on Ubuntu machine.

2) Explain how you installed Postgres, source, package and if
package from what repo?

> 
> Jason L. Amerson
> 
> 
> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Thursday, November 21, 2019 02:42 PM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'Adrian Klaver'
> <adrian.klaver@aklaver.com>; 'PostgreSQL'
> <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
> 
> "Jason L. Amerson" <drjason@alphagenius.org> writes:
>> Yes "listen_addresses" is not commented. I did notice when I did the
> netstat, for tcp, it was all "127.0.0.1" on various ports including 5432 but
> I have a listing for tcp6 that has my static IP using port 32305. Would that
> make a difference?
> 
> Hm, well, *something* is overriding the setting.  What did you find in
> pg_settings?
> 
>             regards, tom lane
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> pg_settings show localhost.

What I asked you about was the "source" columns.

            regards, tom lane

I have attached a screenshot of the output.

Jason L. Amerson


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Thursday, November 21, 2019 03:32 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'Adrian Klaver'
<adrian.klaver@aklaver.com>; 'PostgreSQL'
<pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> pg_settings show localhost.

What I asked you about was the "source" columns.

            regards, tom lane

1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.

2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how to
do.I got the source package from PostgreSQL. I basically followed these instructions: 

sudo ./configure
make world
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

Then I set the following environment variables:

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH

PATH=/usr/local/pgsql/bin:$PATH
export PATH

MANPATH=/usr/local/pgsql/share/man:$MANPATH
export MANPATH

Jason L. Amerson


-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, November 21, 2019 03:17 PM
To: Jason L. Amerson <drjason@alphagenius.org>; 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL' <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On 11/21/19 12:07 PM, Jason L. Amerson wrote:
> pg_settings show localhost.

Lets back up a bit and:

1) Show output of ps ax | grep post on Ubuntu machine.

2) Explain how you installed Postgres, source, package and if package from what repo?

>
> Jason L. Amerson
>
>
> -----Original Message-----
> From: Tom Lane <tgl@sss.pgh.pa.us>
> Sent: Thursday, November 21, 2019 02:42 PM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'Adrian Klaver'
> <adrian.klaver@aklaver.com>; 'PostgreSQL'
> <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
>
> "Jason L. Amerson" <drjason@alphagenius.org> writes:
>> Yes "listen_addresses" is not commented. I did notice when I did the
> netstat, for tcp, it was all "127.0.0.1" on various ports including
> 5432 but I have a listing for tcp6 that has my static IP using port
> 32305. Would that make a difference?
>
> Hm, well, *something* is overriding the setting.  What did you find in
> pg_settings?
>
>             regards, tom lane
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/21/19 1:14 PM, Jason L. Amerson wrote:
> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.

What program are you using to SSH into the remote machine?
It should allow you to copy 'n' paste the screen output without 
resorting to screenshots. Text is a lot handier and easy to read.

> 
> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how to
do.I got the source package from PostgreSQL. I basically followed these instructions:
 

One of your previous posts showed:

"/etc/postgresql/9.4/main."

which would indicate that Postgres was also installed using deb 
packaging. This is something you probably want to follow up on as 
different Postgres instances from different sources can be an issue in 
and of itself.

In the meantime as to your issue:

1) What Postgres version did you install from source?

2) In /usr/local/pgsql/data what is the exact setting for 
listen_addresses and port in postgresql.conf


> 
> sudo ./configure


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2019-11-21 17:27:04 +0100, Moreno Andreo wrote:
> Connection refused means somthing has blocked it. If it was all OK and simply
> Postgres was not listening, you should've received a "connection timed out"
> (10060) message.

Almost exactly the other way around. If you try to connect to a port
where no server is listening, you get a connection refused message.
If something is blocking the connection you may get a timeout.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 11/21/19 1:14 PM, Jason L. Amerson wrote:
> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
> 
> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how to
do.I got the source package from PostgreSQL. I basically followed these instructions:
 
> 

Additional questions that came to mind:

1) How are you starting/stopping server?

2) What do you see on the terminal when you restart the server? I see:

aklaver@ranger:~> sudo /etc/init.d/postgresql11 restart
Restarting PostgreSQL: ok 
 
 

aklaver@ranger:~> -2019-11-21 13:49:30.104 PST-0LOG:  listening on IPv6 
address "::1", port 5432 
 

-2019-11-21 13:49:30.104 PST-0LOG:  listening on IPv4 address 
"127.0.0.1", port 5432 
 

-2019-11-21 13:49:30.193 PST-0LOG:  listening on Unix socket 
"/tmp/.s.PGSQL.5432" 
 

-2019-11-21 13:49:30.322 PST-0LOG:  redirecting log output to logging 
collector process 
 

-2019-11-21 13:49:30.322 PST-0HINT:  Future log output will appear in 
directory "log".

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Il 21/11/19 22:40, Peter J. Holzer ha scritto:
> On 2019-11-21 17:27:04 +0100, Moreno Andreo wrote:
>> Connection refused means somthing has blocked it. If it was all OK and simply
>> Postgres was not listening, you should've received a "connection timed out"
>> (10060) message.
> Almost exactly the other way around. If you try to connect to a port
> where no server is listening, you get a connection refused message.
> If something is blocking the connection you may get a timeout.
>
>          hp
>

O.O
You're right, obviously. Don't know why, it's since school days that 
sometines I swap these 2 error meanings (and have to deal with the 
consequences)...
But I'm confident that one day there will be no more swapping... 
hopefully until I retire ...:-)
Thanks for pointing out and sorry for the mistake
(blushing)

Moreno.-

Adrian,

I originally did install PostgreSQL 12 from the repository. Then I removed it and decided to do it from source. I do
havetwo postgresql.conf files and two pg_hba.conf files in two different locations. I guess I need to know which one to
keep.When I enter SHOW config_file;, I get the location /usr/local/pgsql/data/postgresql.conf. There is a pg_hba.conf
filein there too. Anyways, the version that I installed from source is version 12. The setting in postgresql.conf is
listen_addresses= ‘*’ and the port is 5432. listen_addresses is uncommented but port is commented out. 

Jason L. Amerson


-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, November 21, 2019 04:32 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL' <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On 11/21/19 1:14 PM, Jason L. Amerson wrote:
> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.

What program are you using to SSH into the remote machine?
It should allow you to copy 'n' paste the screen output without resorting to screenshots. Text is a lot handier and
easyto read. 

>
> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how to
do.I got the source package from PostgreSQL. I basically followed these instructions: 

One of your previous posts showed:

"/etc/postgresql/9.4/main."

which would indicate that Postgres was also installed using deb packaging. This is something you probably want to
followup on as different Postgres instances from different sources can be an issue in and of itself. 

In the meantime as to your issue:

1) What Postgres version did you install from source?

2) In /usr/local/pgsql/data what is the exact setting for listen_addresses and port in postgresql.conf


>
> sudo ./configure


--
Adrian Klaver
adrian.klaver@aklaver.com

I am start/stopping the server by using sudo service postgresql start/stop/restart/status. When I run any of these
commands,I do not get anything. No confirmation, except for the status, it does show the server is running. But
otherwise,all I see is that it just goes to a new line with no confirmation or errors. 

Jason L. Amerson


-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, November 21, 2019 04:53 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL' <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On 11/21/19 1:14 PM, Jason L. Amerson wrote:
> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
>
> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how to
do.I got the source package from PostgreSQL. I basically followed these instructions: 
>

Additional questions that came to mind:

1) How are you starting/stopping server?

2) What do you see on the terminal when you restart the server? I see:

aklaver@ranger:~> sudo /etc/init.d/postgresql11 restart Restarting PostgreSQL: ok



aklaver@ranger:~> -2019-11-21 13:49:30.104 PST-0LOG:  listening on IPv6 address "::1", port 5432


-2019-11-21 13:49:30.104 PST-0LOG:  listening on IPv4 address
"127.0.0.1", port 5432


-2019-11-21 13:49:30.193 PST-0LOG:  listening on Unix socket
"/tmp/.s.PGSQL.5432"


-2019-11-21 13:49:30.322 PST-0LOG:  redirecting log output to logging
collector process


-2019-11-21 13:49:30.322 PST-0HINT:  Future log output will appear in
directory "log".

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/22/19 5:40 AM, Jason L. Amerson wrote:
> Adrian,
> 
> I originally did install PostgreSQL 12 from the repository. Then I removed it and decided to do it from source. I do
havetwo postgresql.conf files and two pg_hba.conf files in 
 

Well from your previous post "/etc/postgresql/9.4/main." That would 
indicate there is also a 9.4 package installed or at least its conf 
files. At the command line do:

pg_lsclusters

two different locations. I guess I need to know which one to keep. When 
I enter SHOW config_file;, I get the location 
/usr/local/pgsql/data/postgresql.conf. There is a pg_hba.conf file in 
there too. Anyways, the version that I installed from source is version 
12. The setting in postgresql.conf is listen_addresses = ‘*’ and the 
port is 5432. listen_addresses is uncommented but port is commented out.

What do you see if in psql you do?:

select version();

In your screenshot for pg_settings the value for listen_addresses is 
'localhost' and the source is default. That would indicate to me either 
listen_addresses is not uncommented or the server was not restarted. In 
any case that would be why you cannot connect remotely. Also the 
sourcefile is NULL. This is either because the value is not coming from 
a file or because you where not a superuser when you did the select on 
pg_settings. Can you run the select as a superuser?

> 
> Jason L. Amerson
> 
> 
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Thursday, November 21, 2019 04:32 PM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL' <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
> 
> On 11/21/19 1:14 PM, Jason L. Amerson wrote:
>> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
> 
> What program are you using to SSH into the remote machine?
> It should allow you to copy 'n' paste the screen output without resorting to screenshots. Text is a lot handier and
easyto read.
 
> 
>>
>> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how
todo. I got the source package from PostgreSQL. I basically followed these instructions:
 
> 
> One of your previous posts showed:
> 
> "/etc/postgresql/9.4/main."
> 
> which would indicate that Postgres was also installed using deb packaging. This is something you probably want to
followup on as different Postgres instances from different sources can be an issue in and of itself.
 
> 
> In the meantime as to your issue:
> 
> 1) What Postgres version did you install from source?
> 
> 2) In /usr/local/pgsql/data what is the exact setting for listen_addresses and port in postgresql.conf
> 
> 
>>
>> sudo ./configure
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/22/19 5:49 AM, Jason L. Amerson wrote:
> I am start/stopping the server by using sudo service postgresql start/stop/restart/status. When I run any of these
commands,I do not get anything. No confirmation, except for 
 

Where did the postgresql file in /etc/init.d/ come from?

Does it have something like:

# Setting environment variables for the postmaster here does not work; 
please
# set them in /etc/postgresql/<version>/<cluster>/environment instead.

[ -r /usr/share/postgresql-common/init.d-functions ] || exit 0

. /usr/share/postgresql-common/init.d-functions

near the top?

the status, it does show the server is running. But otherwise, all I see 
is that it just goes to a new line with no confirmation or errors.
> 
> Jason L. Amerson
> 
> 
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Thursday, November 21, 2019 04:53 PM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL' <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
> 
> On 11/21/19 1:14 PM, Jason L. Amerson wrote:
>> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
>>
>> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how
todo. I got the source package from PostgreSQL. I basically followed these instructions:
 
>>
> 
> Additional questions that came to mind:
> 
> 1) How are you starting/stopping server?
> 
> 2) What do you see on the terminal when you restart the server? I see:
> 
> aklaver@ranger:~> sudo /etc/init.d/postgresql11 restart Restarting PostgreSQL: ok
>   
>   
> 
> aklaver@ranger:~> -2019-11-21 13:49:30.104 PST-0LOG:  listening on IPv6 address "::1", port 5432
>   
> 
> -2019-11-21 13:49:30.104 PST-0LOG:  listening on IPv4 address
> "127.0.0.1", port 5432
>   
> 
> -2019-11-21 13:49:30.193 PST-0LOG:  listening on Unix socket
> "/tmp/.s.PGSQL.5432"
>   
> 
> -2019-11-21 13:49:30.322 PST-0LOG:  redirecting log output to logging
> collector process
>   
> 
> -2019-11-21 13:49:30.322 PST-0HINT:  Future log output will appear in
> directory "log".
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

When I run pg_lsclusters, I get the following:

Ver Cluster Port Status Owner    Data directory
9.4 main    5432 down   postgres /var/lib/postgresql/9.4/main

Log file
/var/log/postgresql/postgresql-9.4-main.log

When I run select version();, I get the following:

 version

--------------------------------------------------------------------------------------------------------------
PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~
18.04.1) 7.4.0, 64-bit
(1 row)

I logged into root and tried to run select, and now I cannot log into the server under root. What the hell! I am so
frustrated!This should not be so difficult. Obviously something is very screwed up with two servers. I would like to
properlyremove the repository version and all files and then uninstall the install I did from source code and get rid
ofany other files with that so that I can just start over. Will you please help me do this the best way? Will I have to
manuallyremove some of the files associated with the two installs? I really appreciate how great everyone has been in
helpingme. I just do not want to waste any more of our time when I can just get rid of everything and start over. 

Also, do my Windows 10 computers that will be clients, need PostgreSQL installed in order to connect remotely to my
serveror is pgAdmin or some other client all I need? 

Jason L. Amerson


-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, November 22, 2019 09:56 AM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'PostgreSQL' <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On 11/22/19 5:40 AM, Jason L. Amerson wrote:
> Adrian,
>
> I originally did install PostgreSQL 12 from the repository. Then I
> removed it and decided to do it from source. I do have two
> postgresql.conf files and two pg_hba.conf files in

Well from your previous post "/etc/postgresql/9.4/main." That would indicate there is also a 9.4 package installed or
atleast its conf files. At the command line do: 

pg_lsclusters

two different locations. I guess I need to know which one to keep. When I enter SHOW config_file;, I get the location
/usr/local/pgsql/data/postgresql.conf.There is a pg_hba.conf file in there too. Anyways, the version that I installed
fromsource is version 12. The setting in postgresql.conf is listen_addresses = ‘*’ and the port is 5432.
listen_addressesis uncommented but port is commented out. 

What do you see if in psql you do?:

select version();

In your screenshot for pg_settings the value for listen_addresses is 'localhost' and the source is default. That would
indicateto me either listen_addresses is not uncommented or the server was not restarted. In any case that would be why
youcannot connect remotely. Also the sourcefile is NULL. This is either because the value is not coming from a file or
becauseyou where not a superuser when you did the select on pg_settings. Can you run the select as a superuser? 

>
> Jason L. Amerson
>
>
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Thursday, November 21, 2019 04:32 PM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL'
> <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
>
> On 11/21/19 1:14 PM, Jason L. Amerson wrote:
>> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
>
> What program are you using to SSH into the remote machine?
> It should allow you to copy 'n' paste the screen output without resorting to screenshots. Text is a lot handier and
easyto read. 
>
>>
>> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how
todo. I got the source package from PostgreSQL. I basically followed these instructions: 
>
> One of your previous posts showed:
>
> "/etc/postgresql/9.4/main."
>
> which would indicate that Postgres was also installed using deb packaging. This is something you probably want to
followup on as different Postgres instances from different sources can be an issue in and of itself. 
>
> In the meantime as to your issue:
>
> 1) What Postgres version did you install from source?
>
> 2) In /usr/local/pgsql/data what is the exact setting for
> listen_addresses and port in postgresql.conf
>
>
>>
>> sudo ./configure
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/22/19 8:27 AM, Jason L. Amerson wrote:
> When I run pg_lsclusters, I get the following:
> 
> Ver Cluster Port Status Owner    Data directory
> 9.4 main    5432 down   postgres /var/lib/postgresql/9.4/main
> 
> Log file
> /var/log/postgresql/postgresql-9.4-main.log
> 
> When I run select version();, I get the following:
> 
>   version
>                      
> --------------------------------------------------------------------------------------------------------------
> PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~
> 18.04.1) 7.4.0, 64-bit
> (1 row)

Alright so you are running the compiled version and you have a package 
version which is not running.

> 
> I logged into root and tried to run select, and now I cannot log into the server under root. What the hell! I am so
frustrated!This should not be so difficult. Obviously something is very screwed up with two servers. I 
 

I should have been more specific, I meant as database superuser(postgres).

would like to properly remove the repository version and all files and 
then uninstall the install I did from source code and get rid of any 
other files with that so that I can just start over. Will you please 
help me do this the best way? Will I have to manually remove some of the 
files associated with the two installs? I really appreciate how great 
everyone has been in helping me. I just do not want to waste any more of 
our time when I can just get rid of everything and start over.

Starting over sounds like a good idea. To that end:

1) Do you have data on the running Postgres server you care about?
If so use pg_dump to backup it up.

2) I would stick with the package system instead of compiling from source.

3) Uninstall the current package version(9.4).

4) Assuming you did the pg_dump from 1) or don't care about the data, 
stop the compiled version. Then delete the /usr/local/pgsql/ directory 
and clean up the /etc/init.d/. If you want to be extra safe you could 
copy the previous to a backup location.

5) Decide where you want to get your packages from. I would suggest 
using the Postgres community repo:

https://www.postgresql.org/download/linux/ubuntu/
https://wiki.postgresql.org/wiki/Apt

If you are not already then point at the above repo.

6) Follow instructions at link in 5) to install Postgres.
Substitute 12 for 11 if you want 12. FYI just specifying postgresql (no 
-version) will get you the latest.

7) Assuming installing 12 then go to:

/etc/postgresql/12/main

to make the necessary changes to postgresql.conf and pg_hba.conf.


8) For using the Debian/Ubuntu Postgres cluster management take a look at:

https://wiki.debian.org/PostgreSql

> 
> Also, do my Windows 10 computers that will be clients, need PostgreSQL installed in order to connect remotely to my
serveror is pgAdmin or some other client all I need?
 
> 
> Jason L. Amerson
> 
> 
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Friday, November 22, 2019 09:56 AM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'PostgreSQL' <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
> 
> On 11/22/19 5:40 AM, Jason L. Amerson wrote:
>> Adrian,
>>
>> I originally did install PostgreSQL 12 from the repository. Then I
>> removed it and decided to do it from source. I do have two
>> postgresql.conf files and two pg_hba.conf files in
> 
> Well from your previous post "/etc/postgresql/9.4/main." That would indicate there is also a 9.4 package installed or
atleast its conf files. At the command line do:
 
> 
> pg_lsclusters
> 
> two different locations. I guess I need to know which one to keep. When I enter SHOW config_file;, I get the location
/usr/local/pgsql/data/postgresql.conf.There is a pg_hba.conf file in there too. Anyways, the version that I installed
fromsource is version 12. The setting in postgresql.conf is listen_addresses = ‘*’ and the port is 5432.
listen_addressesis uncommented but port is commented out.
 
> 
> What do you see if in psql you do?:
> 
> select version();
> 
> In your screenshot for pg_settings the value for listen_addresses is 'localhost' and the source is default. That
wouldindicate to me either listen_addresses is not uncommented or the server was not restarted. In any case that would
bewhy you cannot connect remotely. Also the sourcefile is NULL. This is either because the value is not coming from a
fileor because you where not a superuser when you did the select on pg_settings. Can you run the select as a
superuser?
> 
>>
>> Jason L. Amerson
>>
>>
>> -----Original Message-----
>> From: Adrian Klaver <adrian.klaver@aklaver.com>
>> Sent: Thursday, November 21, 2019 04:32 PM
>> To: Jason L. Amerson <drjason@alphagenius.org>
>> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL'
>> <pgsql-general@lists.postgresql.org>
>> Subject: Re: Remote Connection Help
>>
>> On 11/21/19 1:14 PM, Jason L. Amerson wrote:
>>> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
>>
>> What program are you using to SSH into the remote machine?
>> It should allow you to copy 'n' paste the screen output without resorting to screenshots. Text is a lot handier and
easyto read.
 
>>
>>>
>>> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how
todo. I got the source package from PostgreSQL. I basically followed these instructions:
 
>>
>> One of your previous posts showed:
>>
>> "/etc/postgresql/9.4/main."
>>
>> which would indicate that Postgres was also installed using deb packaging. This is something you probably want to
followup on as different Postgres instances from different sources can be an issue in and of itself.
 
>>
>> In the meantime as to your issue:
>>
>> 1) What Postgres version did you install from source?
>>
>> 2) In /usr/local/pgsql/data what is the exact setting for
>> listen_addresses and port in postgresql.conf
>>
>>
>>>
>>> sudo ./configure
>>
>>
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

I removed both version of PostgreSQL from Ubuntu. I had to delete some files manually. I used the repository to install
PostgreSQLand then I configured it for remote access and now everything works great! I can log in from my Windows
computersusing pgAdmin. Thank you to you and everyone else who had the patience to work with me. I appreciate all of
yourhelp. I just have one last question. I see many tutorials on the Internet showing you how to setup iptables with
PostgreSQL.Should I do this or not? I just wanted to ask before I did it and screwed something up and it would not have
madethat much difference. 

Thanks again to everyone,

Jason L. Amerson



-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Friday, November 22, 2019 01:11 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'PostgreSQL' <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help


On 11/22/19 8:27 AM, Jason L. Amerson wrote:
> When I run pg_lsclusters, I get the following:
>
> Ver Cluster Port Status Owner    Data directory
> 9.4 main    5432 down   postgres /var/lib/postgresql/9.4/main
>
> Log file
> /var/log/postgresql/postgresql-9.4-main.log
>
> When I run select version();, I get the following:
>
>   version
>
> ----------------------------------------------------------------------
> ----------------------------------------
> PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 7.4.0-1ubuntu1~
> 18.04.1) 7.4.0, 64-bit
> (1 row)

Alright so you are running the compiled version and you have a package version which is not running.

>
> I logged into root and tried to run select, and now I cannot log into
> the server under root. What the hell! I am so frustrated! This should
> not be so difficult. Obviously something is very screwed up with two
> servers. I

I should have been more specific, I meant as database superuser(postgres).

would like to properly remove the repository version and all files and then uninstall the install I did from source
codeand get rid of any other files with that so that I can just start over. Will you please help me do this the best
way?Will I have to manually remove some of the files associated with the two installs? I really appreciate how great
everyonehas been in helping me. I just do not want to waste any more of our time when I can just get rid of everything
andstart over. 

Starting over sounds like a good idea. To that end:

1) Do you have data on the running Postgres server you care about?
If so use pg_dump to backup it up.

2) I would stick with the package system instead of compiling from source.

3) Uninstall the current package version(9.4).

4) Assuming you did the pg_dump from 1) or don't care about the data, stop the compiled version. Then delete the
/usr/local/pgsql/directory and clean up the /etc/init.d/. If you want to be extra safe you could copy the previous to a
backuplocation. 

5) Decide where you want to get your packages from. I would suggest using the Postgres community repo:

https://www.postgresql.org/download/linux/ubuntu/
https://wiki.postgresql.org/wiki/Apt

If you are not already then point at the above repo.

6) Follow instructions at link in 5) to install Postgres.
Substitute 12 for 11 if you want 12. FYI just specifying postgresql (no
-version) will get you the latest.

7) Assuming installing 12 then go to:

/etc/postgresql/12/main

to make the necessary changes to postgresql.conf and pg_hba.conf.


8) For using the Debian/Ubuntu Postgres cluster management take a look at:

https://wiki.debian.org/PostgreSql

>
> Also, do my Windows 10 computers that will be clients, need PostgreSQL installed in order to connect remotely to my
serveror is pgAdmin or some other client all I need? 
>
> Jason L. Amerson
>
>
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Friday, November 22, 2019 09:56 AM
> To: Jason L. Amerson <drjason@alphagenius.org>
> Cc: 'PostgreSQL' <pgsql-general@lists.postgresql.org>
> Subject: Re: Remote Connection Help
>
> On 11/22/19 5:40 AM, Jason L. Amerson wrote:
>> Adrian,
>>
>> I originally did install PostgreSQL 12 from the repository. Then I
>> removed it and decided to do it from source. I do have two
>> postgresql.conf files and two pg_hba.conf files in
>
> Well from your previous post "/etc/postgresql/9.4/main." That would indicate there is also a 9.4 package installed or
atleast its conf files. At the command line do: 
>
> pg_lsclusters
>
> two different locations. I guess I need to know which one to keep. When I enter SHOW config_file;, I get the location
/usr/local/pgsql/data/postgresql.conf.There is a pg_hba.conf file in there too. Anyways, the version that I installed
fromsource is version 12. The setting in postgresql.conf is listen_addresses = ‘*’ and the port is 5432.
listen_addressesis uncommented but port is commented out. 
>
> What do you see if in psql you do?:
>
> select version();
>
> In your screenshot for pg_settings the value for listen_addresses is 'localhost' and the source is default. That
wouldindicate to me either listen_addresses is not uncommented or the server was not restarted. In any case that would
bewhy you cannot connect remotely. Also the sourcefile is NULL. This is either because the value is not coming from a
fileor because you where not a superuser when you did the select on pg_settings. Can you run the select as a superuser? 
>
>>
>> Jason L. Amerson
>>
>>
>> -----Original Message-----
>> From: Adrian Klaver <adrian.klaver@aklaver.com>
>> Sent: Thursday, November 21, 2019 04:32 PM
>> To: Jason L. Amerson <drjason@alphagenius.org>
>> Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'PostgreSQL'
>> <pgsql-general@lists.postgresql.org>
>> Subject: Re: Remote Connection Help
>>
>> On 11/21/19 1:14 PM, Jason L. Amerson wrote:
>>> 1) I have attached a screenshot of the output of "ps ax | grep post" on the Ubuntu machine.
>>
>> What program are you using to SSH into the remote machine?
>> It should allow you to copy 'n' paste the screen output without resorting to screenshots. Text is a lot handier and
easyto read. 
>>
>>>
>>> 2) Since I was new to PostgreSQL, I followed a tutorial online. I did install from source which I already knew how
todo. I got the source package from PostgreSQL. I basically followed these instructions: 
>>
>> One of your previous posts showed:
>>
>> "/etc/postgresql/9.4/main."
>>
>> which would indicate that Postgres was also installed using deb packaging. This is something you probably want to
followup on as different Postgres instances from different sources can be an issue in and of itself. 
>>
>> In the meantime as to your issue:
>>
>> 1) What Postgres version did you install from source?
>>
>> 2) In /usr/local/pgsql/data what is the exact setting for
>> listen_addresses and port in postgresql.conf
>>
>>
>>>
>>> sudo ./configure
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

"Jason L. Amerson" <drjason@alphagenius.org> writes:
> I removed both version of PostgreSQL from Ubuntu. I had to delete some
> files manually. I used the repository to install PostgreSQL and then I
> configured it for remote access and now everything works great! I can
> log in from my Windows computers using pgAdmin. Thank you to you and
> everyone else who had the patience to work with me. I appreciate all of
> your help. I just have one last question. I see many tutorials on the
> Internet showing you how to setup iptables with PostgreSQL. Should I do
> this or not?

Usually the reason for messing with iptables is either

1. You have to, because your machine's default firewall rules aren't
letting in connections to Postgres.  You evidently haven't got that
problem.

2. You want to block off access to the database server from addresses
you don't trust.  Configuring pg_hba.conf is typically a sufficient
answer for that; but you might want an iptables filter too, either
because you're a belt-and-suspenders-too kind of guy, or because you're
afraid of DDoS storms with more connection requests/sec than Postgres
can reject easily.  I can't say whether either of those apply to you;
but I would note that the DDoS scenario only applies if your server
is reachable from the net-at-large, which is seldom a good idea in
the first place.

            regards, tom lane