RE: Remote Connection Help

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 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 <adrian(dot)klaver(at)aklaver(dot)com>
Sent: Friday, November 22, 2019 09:56 AM
To: Jason L. Amerson <drjason(at)alphagenius(dot)org>
Cc: 'PostgreSQL' <pgsql-general(at)lists(dot)postgresql(dot)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 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(dot)klaver(at)aklaver(dot)com>
> Sent: Thursday, November 21, 2019 04:32 PM
> To: Jason L. Amerson <drjason(at)alphagenius(dot)org>
> Cc: 'Steve Crawford' <scrawford(at)pinpointresearch(dot)com>; 'PostgreSQL'
> <pgsql-general(at)lists(dot)postgresql(dot)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 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(dot)klaver(at)aklaver(dot)com