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 (Ox274D/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.
RE: Remote Connection Help
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. Jason L. Amerson From: Jason L. Amerson Sent: Thursday, November 21, 2019 09:22 AM To: PostgreSQL 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 (Ox274D/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.
RE: Remote Connection Help
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. Jason L. Amerson From: Steve Atkins Sent: Thursday, November 21, 2019 09:49 AM To: pgsql-general@lists.postgresql.org Subject: Re: Remote Connection Help On 21/11/2019 14:30, Ekaterina Amez wrote: 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 (Ox274D/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
RE: Remote Connection Help
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 Sent: Thursday, November 21, 2019 11:11 AM To: Jason L. Amerson ; 'PostgreSQL' 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 > *Sent:* Thursday, November 21, 2019 09:22 AM > *To:* PostgreSQL > *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 (Ox274D/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.kla...@aklaver.com
RE: ***SPAM*** Re: [SPAM] Remote Connection Help
I have removed the rules in the iptables and restarted it and got nothing. iptables is turned off and still nothing. Jason L. Amerson From: Moreno Andreo Sent: Thursday, November 21, 2019 11:27 AM To: pgsql-general@lists.postgresql.org Subject: ***SPAM*** Re: [SPAM] Remote Connection Help Il 21/11/19 15:21, Jason L. Amerson ha scritto: could not connect to server: Connection refused (Ox274D/10061) Is the server running on host " xx.xx.xx.xx" and accepting TCP/IP connections on port 5432' 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. Have you tried adjusting/tearing off iptables and check what happens, as also Ekaterina pointed out? And, just as a side note, I normally don't activate IPv6 if it's not necessary (it has not been necessary in the last 10 years :-) ), 'cause I've run in some troubles that have been cleared getting rid of IPv6) so I'll try editing postgresql.conf as listen = '127.0.0.1' HTH, Moreno.-
RE: Remote Connection Help
I connected to PostgreSQL locally. I ran “show listen_addresses;” and it returned “localhost.” I ran “show port;” and it returned “5432.” I am now confused. I edited the “postgresql.conf” file and change the setting to ‘*’. Then I restarted the server with “service postgresql restart.” I was in root since I had to edit the config files. I thought maybe I 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 be sure, I edited the file by using “nano /usr/local/psql/data/postgresql.conf.” I went down and found that I did have it as “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.” Jason L. Amerson From: Steve Crawford Sent: Thursday, November 21, 2019 12:05 PM To: Jason L. Amerson Cc: Adrian Klaver ; PostgreSQL Subject: Re: Remote Connection Help On Thu, Nov 21, 2019 at 8:48 AM Jason L. Amerson mailto:drja...@alphagenius.org> > wrote: 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 Can you connect locally? I.e. on the machine running PostgreSQL? If so, you can run: show listen_addresses; and show port; to verify the settings. On some distros (including Ubuntu) you can have PostgreSQL running on a non-standard port due to an upgrade or installation of multiple versions. Other thoughts. Did you restart PostgreSQL after changing settings? Are you sure that you are editing the postgresql.conf file associated with your running instance? Is there anything on the *client* machine or between the client machine and your PostgreSQL server that could be blocking ports? Have you used netstat or lsof to verify that PostgreSQL is listening on 5432? Cheers, Steve
RE: Remote Connection Help
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? Jason L. Amerson -Original Message- From: Tom Lane Sent: Thursday, November 21, 2019 01:18 PM To: Jason L. Amerson Cc: 'Steve Crawford' ; 'Adrian Klaver' ; 'PostgreSQL' Subject: Re: Remote Connection Help "Jason L. Amerson" writes: > I connected to PostgreSQL locally. I ran “show listen_addresses;” and it > returned “localhost.” I ran “show port;” and it returned “5432.” I am now > confused. I edited the “postgresql.conf” file and change the setting to ‘*’. > Then I restarted the server with “service postgresql restart.” I was in root > since I had to edit the config files. I thought maybe I 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 be sure, I edited the file by using “nano > /usr/local/psql/data/postgresql.conf.” I went down and found that I did have > it as “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
RE: Remote Connection Help
I went back and added the line you 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 before, 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 something very screwy is going on. Jason L. Amerson From: Mark Johnson Sent: Thursday, November 21, 2019 02:02 PM To: Jason L. Amerson Cc: Tom Lane ; Steve Crawford ; Adrian Klaver ; PostgreSQL Subject: Re: Remote Connection Help 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. For me, I just added a line to pg_hba.conf like this: hostall all ::1/128 md5 So, even though my client app is on the db server and the connection string has an IPv4 address the connection request still gets to PostgreSQL as IPv6 and fails until I added the line shown above. Did your netstat output have two lines for the port numbers used by PostgreSQL or just one of them? My computer has two like this, $ netstat -nlt | grep 5432 tcp0 0 127.0.0.1:5432 <http://127.0.0.1:5432> 0.0.0.0:* LISTEN tcp6 0 0 ::1:5432:::*LISTEN On Thu, Nov 21, 2019 at 1:41 PM Jason L. Amerson mailto:drja...@alphagenius.org> > wrote: 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? Jason L. Amerson -Original Message- From: Tom Lane mailto:t...@sss.pgh.pa.us> > Sent: Thursday, November 21, 2019 01:18 PM To: Jason L. Amerson mailto:drja...@alphagenius.org> > Cc: 'Steve Crawford' mailto:scrawf...@pinpointresearch.com> >; 'Adrian Klaver' mailto:adrian.kla...@aklaver.com> >; 'PostgreSQL' mailto:pgsql-general@lists.postgresql.org> > Subject: Re: Remote Connection Help "Jason L. Amerson" mailto:drja...@alphagenius.org> > writes: > I connected to PostgreSQL locally. I ran “show listen_addresses;” and it > returned “localhost.” I ran “show port;” and it returned “5432.” I am now > confused. I edited the “postgresql.conf” file and change the setting to ‘*’. > Then I restarted the server with “service postgresql restart.” I was in root > since I had to edit the config files. I thought maybe I 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 be sure, I edited the file by using “nano > /usr/local/psql/data/postgresql.conf.” I went down and found that I did have > it as “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
RE: Remote Connection Help
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 Sent: Thursday, November 21, 2019 02:53 PM To: Mark Johnson Cc: Jason L. Amerson ; Steve Crawford ; Adrian Klaver ; PostgreSQL Subject: Re: Remote Connection Help Mark Johnson 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 ". 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
RE: Remote Connection Help
pg_settings show localhost. Jason L. Amerson -Original Message- From: Tom Lane Sent: Thursday, November 21, 2019 02:42 PM To: Jason L. Amerson Cc: 'Steve Crawford' ; 'Adrian Klaver' ; 'PostgreSQL' Subject: Re: Remote Connection Help "Jason L. Amerson" 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
RE: Remote Connection Help
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 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. Jason L. Amerson -Original Message- From: Adrian Klaver Sent: Thursday, November 21, 2019 04:32 PM To: Jason L. Amerson Cc: 'Steve Crawford' ; 'PostgreSQL' 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 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.kla...@aklaver.com
RE: Remote Connection Help
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 Sent: Thursday, November 21, 2019 04:53 PM To: Jason L. Amerson Cc: 'Steve Crawford' ; 'PostgreSQL' 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.kla...@aklaver.com
RE: Remote Connection Help
When I run pg_lsclusters, I get the following: Ver Cluster Port Status OwnerData directory 9.4 main5432 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 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. Also, do my Windows 10 computers that will be clients, need PostgreSQL installed in order to connect remotely to my server or is pgAdmin or some other client all I need? Jason L. Amerson -Original Message- From: Adrian Klaver Sent: Friday, November 22, 2019 09:56 AM To: Jason L. Amerson Cc: 'PostgreSQL' 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 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 > Sent: Thursday, November 21, 2019 04:32 PM > To: Jason L. Amerson > Cc: 'Steve Crawford' ; 'PostgreSQL' > > 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 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.kla...@aklaver.com
Client Computers
Do my Windows 10 computers that will be clients, need PostgreSQL installed to connect remotely to my server or is pgAdmin or some other client all I need? Jason L. Amerson
RE: Client Computers
Thanks Jason L. Amerson From: Daniel Baktiar Sent: Saturday, November 23, 2019 07:15 AM To: Jason L. Amerson Cc: PostgreSQL Subject: Re: Client Computers Hi Jason, AFAIK, you just need the pgadmin installed. Regards, Daniel On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson mailto:drja...@alphagenius.org> > wrote: Do my Windows 10 computers that will be clients, need PostgreSQL installed to connect remotely to my server or is pgAdmin or some other client all I need? Jason L. Amerson
RE: Client Computers
Thanks Ron. Jason L. Amerson From: Ron Sent: Saturday, November 23, 2019 09:38 AM To: pgsql-general@lists.postgresql.org Subject: Re: Client Computers On 11/23/19 4:25 AM, Jason L. Amerson wrote: Do my Windows 10 computers that will be clients, need PostgreSQL installed to connect remotely to my server or is pgAdmin or some other client all I need? They only need pgAdmin installed if they're going to actually run pgAdmin. If they're "just" going to run some specific program, then they only need that program and the relevant driver (JDBC, ODBC, etc). -- Angular momentum makes the world go 'round.
RE: Client Computers
Thanks Tim. Jason L. Amerson -Original Message- From: Tim Clarke Sent: Saturday, November 23, 2019 09:32 AM To: pgsql-general@lists.postgresql.org Subject: Re: Client Computers The ODBC drivers are generally very useful. Tim Clarke IT Director Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420 On 23/11/2019 12:23, Jason L. Amerson wrote: > > Thanks > > Jason L. Amerson > > *From:* Daniel Baktiar > *Sent:* Saturday, November 23, 2019 07:15 AM > *To:* Jason L. Amerson > *Cc:* PostgreSQL > *Subject:* Re: Client Computers > > Hi Jason, > > AFAIK, you just need the pgadmin installed. > > Regards, > > Daniel > > On Sat, Nov 23, 2019 at 6:26 PM Jason L. Amerson > mailto:drja...@alphagenius.org>> wrote: > > Do my Windows 10 computers that will be clients, need PostgreSQL > installed to connect remotely to my server or is pgAdmin or some > other client all I need? > > Jason L. Amerson > Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee you must not use or disclose such information, instead please report it to ad...@minerva.info<mailto:ad...@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The Manifest Voting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/ for further information.
RE: Remote Connection Help
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? I just wanted to ask before I did it and screwed something up and it would not have made that much difference. Thanks again to everyone, Jason L. Amerson -Original Message- From: Adrian Klaver Sent: Friday, November 22, 2019 01:11 PM To: Jason L. Amerson Cc: 'PostgreSQL' 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 OwnerData directory > 9.4 main5432 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 server or is pgAdmin or some > other client all I need? > > Jason L. Amerson > > > -Original Message- > From: Adrian Klaver > Sent: Friday, November 22, 2019 09:56 AM > To: Jason L. Amerson > Cc: 'PostgreSQL' > 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 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 uncommen
MS Access Frontend
I am trying to setup MS Access as a frontend so that it would be easier on my wife and children to interact with PostgreSQL. I looked online for some tutorials but the ones I found are out-date or only pick up after Access is connected to PostgreSQL. I was wondering if someone knew of some updated material that they could point me to or maybe walk me through it. I have used Access quite a bit years ago and things have changed since then. I know I must install the ODBC drivers, which I have already done. I have already setup the DSN and I clicked on test and it says everything is fine. I know that my next step has something to do with Linked Tables in Access, but I am not sure how to set it up. I guess that is where I start to need help. The client computers using the frontend will be running Windows 10 and Office 365, both are updated to the latest versions. Thank you, Jason L. Amerson
RE: MS Access Frontend
Thanks Martin. I have decided to go another route. I have nothing but problems whenever I use Microsoft products. I personally think that Microsoft was shit when it started, and it is still shit 35 years later. So, I am just going to take Windows off my computers and put Linux on them and just use a different client. Jason L. Amerson From: Martin Gainty Sent: Saturday, November 30, 2019 08:02 AM To: Jason L. Amerson Subject: Re: MS Access Frontend Hi Jason this is how i got the majority of work in the 90s I have Database X that wont talk to Database Y you can write a program that parses the query from accesses the data from inserts to you can save time and $ if you accomplish one of these steps and contract for the remainder Makes Sense? Martin _ From: Jason L. Amerson mailto:drja...@alphagenius.org> > Sent: Friday, November 29, 2019 12:23 PM To: PostgreSQL mailto:pgsql-general@lists.postgresql.org> > Subject: MS Access Frontend I am trying to setup MS Access as a frontend so that it would be easier on my wife and children to interact with PostgreSQL. I looked online for some tutorials but the ones I found are out-date or only pick up after Access is connected to PostgreSQL. I was wondering if someone knew of some updated material that they could point me to or maybe walk me through it. I have used Access quite a bit years ago and things have changed since then. I know I must install the ODBC drivers, which I have already done. I have already setup the DSN and I clicked on test and it says everything is fine. I know that my next step has something to do with Linked Tables in Access, but I am not sure how to set it up. I guess that is where I start to need help. The client computers using the frontend will be running Windows 10 and Office 365, both are updated to the latest versions. Thank you, Jason L. Amerson
RE: MS Access Frontend
I am a Linux user too. I just bought my children Windows laptops so it would be easier for them to use. I had to buy me a Windows one too so that I can do the whole Microsoft Family thing and monitor them a little. But I think it is time to throw them into the deep end and see if they can swim. Jason L. Amerson -Original Message- From: Adrian Klaver Sent: Saturday, November 30, 2019 01:52 PM To: Jason L. Amerson ; 'Martin Gainty' ; PostgreSQL Subject: Re: MS Access Frontend On 11/30/19 11:04 AM, Jason L. Amerson wrote: > Thanks Martin. I have decided to go another route. I have nothing but > problems whenever I use Microsoft products. I personally think that > Microsoft was shit when it started, and it is still shit 35 years later. > So, I am just going to take Windows off my computers and put Linux on > them and just use a different client. I'm a Linux user and I applaud your move. Just be aware you will not find an Access replacement on Linux. You will find things that have subsets of its functionality, but not a drop in replacement. > > Jason L. Amerson > -- Adrian Klaver adrian.kla...@aklaver.com
