Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/27/2018 11:00 AM, Ken Beck wrote:
> I recently upgraded my OS from Ubuntu 14.04 LTS to 16.04 LTS and since,
> postgresql refuses to re-start. I am concerned that if I poke around too
> much, I may lose my old data. I have consulted various web postings here
> and in ubuntu forums, and have not found an obvious solution. But please
> excuse me, I am not a Linux expert, and I had a friend help me with
> setup issues several years ago. They are no longer available, and I am
> having trouble getting things to run.

At a guess when you did the dist-upgrade(14.04 --> 16.04) you got the
default version of Postgres for 16.04(9.6)

Try, at the command line:

pg_lsclusters

>
> Again, this is after upgrading from ubuntu 14.04 LTS to 16.04 LTS.
> Consulting the book "PostgreSQL: Up and Running", I have looked at my
> .conf files in /etc/postgresql/9.3/main, things seem to be in order.
>
> Here are the un-commented lines of postgresql.conf: (I have NOT changed
> these, they are the settings that used to work in ubuntu 14.04)
>
> NOTE: my comments have // at beginning of the line, these lines are NOT
> in the .conf file
>
> data_directory = '/var/lib/postgresql/9.3/main'        # use data in
> another directory
> hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'    # host-based
> authentication file
> ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'    # ident
> configuration file
>
> external_pid_file = '/var/run/postgresql/9.3-main.pid'            #
> write an extra PID file
>
> listen_addresses = 'localhost'        # what IP address(es) to listen on;
> port = 5432                # (change requires restart)
> max_connections = 100            # (change requires restart)
> unix_socket_directories = '/var/run/postgresql'    # comma-separated
> list of directories
> ssl = false                # (change requires restart)
> ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'        # (change
> requires restart)
> ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'        # (change
> requires restart)
>
> // In the logging section, these are the only entries (is this why I
> cant see any log files when I try to start the server??)
> log_line_prefix = '%t '            # special values:
> log_timezone = 'Navajo'
>
> // In Client Connections section, these are the only items active:
> datestyle = 'iso, mdy'
> timezone = 'Navajo'
> lc_messages = 'en_US.UTF-8'            # locale for system error message
>                     # strings
> lc_monetary = 'en_US.UTF-8'            # locale for monetary formatting
> lc_numeric = 'en_US.UTF-8'            # locale for number formatting
> lc_time = 'en_US.UTF-8'                # locale for time formatting
> default_text_search_config = 'pg_catalog.english'
>
> The pg_ctl.conf file is basically empty
>
> The pg_hba.conf file has these active lines:
> local   all             postgres                                trust
> local   all             all                                     peer
> host    all             all             127.0.0.1/32            trust
> host    all             all             ::1/128                 md5
>
> The start.conf file has one active line, 'auto'
>
> The pg_ident.conf file has no entries.
>
> When I try to start postgresql service using this line:
> service postgresql reload
>
> then check running services using this:
> systemctl list-units --type service --all
> I see the lines related to postgres as follows:
> postgresql.service                 loaded    active   exited  PostgreSQL
> RDBMS
> ● postgresql(at)9(dot)3-main(dot)service        loaded    failed   failed
> PostgreSQL Cluster 9.3-main
>
> And, looking for log files, I find none.
>
> If I try to start pgAdmin, I see two servers on localhost, when I tried
> to connect, I get an error first that says it can not open the log file,
> on clicking that I get an error can't open file /home/XXX/.pgpass,
> permission denied. Then I get a prompt for the password for postgres,
> and when I enter the known password for the user, I then get a big 'Guru
> Error' box with a message:
> could not connect to server: Connection refused Is the server running on
> host "127.0.0.1" and accepting TCP/IP connections on port 5432?
> Note that the postgresql.conf file does list 5432 as the port number.
>
> I hope someone has the patience to look this over and suggest remedies. --
>
> Ken Beck
> Liberty, Utah, USA
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com