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 server or is pgAdmin or some other client all I need?
Jason L. Amerson
-----Original Message-----
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Friday, November 22, 2019 09:56 AM
To: Jason L. Amerson <drjason@xxxxxxxxxxxxxxx>
Cc: 'PostgreSQL' <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
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.klaver@xxxxxxxxxxx>
Sent: Thursday, November 21, 2019 04:32 PM
To: Jason L. Amerson <drjason@xxxxxxxxxxxxxxx>
Cc: 'Steve Crawford' <scrawford@xxxxxxxxxxxxxxxxxxxx>; 'PostgreSQL'
<pgsql-general@xxxxxxxxxxxxxxxxxxxx>
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.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
