RE: Remote Connection Help - Mailing list pgsql-general

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