Postgresql 9.3 Server will not start after Ubuntu Upgrade
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. 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@9.3-main.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
Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
OK, there are some logs there, most have old date/time stamps. But there is one with today's time on it, but all it has is week-old data, likely from near the time I did the Ubuntu upgrade. The following entry makes me worry (it is about 2/3 of the way down...) 2018-03-22 10:14:28 MDT LOG: database system was not properly shut down; automatic recovery in progress 2018-03-22 10:14:28 MDT LOG: record with zero length at 0/16178450 2018-03-18 16:41:12 MDT LOG: received fast shutdown request 2018-03-18 16:41:12 MDT LOG: aborting any active transactions 2018-03-18 16:41:12 MDT LOG: autovacuum launcher shutting down 2018-03-18 16:41:12 MDT LOG: shutting down 2018-03-18 16:41:12 MDT LOG: database system is shut down 2018-03-19 17:00:41 MDT LOG: database system was shut down at 2018-03-18 16:41:12 MDT 2018-03-19 17:00:41 MDT FATAL: the database system is starting up 2018-03-19 17:00:41 MDT LOG: incomplete startup packet 2018-03-19 17:00:41 MDT FATAL: the database system is starting up 2018-03-19 17:00:42 MDT FATAL: the database system is starting up 2018-03-19 17:00:42 MDT LOG: MultiXact member wraparound protections are now enabled 2018-03-19 17:00:42 MDT LOG: database system is ready to accept connections 2018-03-19 17:00:42 MDT LOG: autovacuum launcher started 2018-03-19 18:10:24 MDT LOG: received fast shutdown request 2018-03-19 18:10:24 MDT LOG: aborting any active transactions 2018-03-19 18:10:24 MDT LOG: autovacuum launcher shutting down 2018-03-19 18:10:24 MDT LOG: shutting down 2018-03-19 18:10:24 MDT LOG: database system is shut down 2018-03-21 08:08:50 MDT LOG: incomplete startup packet 2018-03-21 08:08:50 MDT LOG: database system was shut down at 2018-03-19 18:10:24 MDT 2018-03-21 08:08:50 MDT FATAL: the database system is starting up 2018-03-21 08:08:51 MDT FATAL: the database system is starting up 2018-03-21 08:08:51 MDT FATAL: the database system is starting up 2018-03-21 08:08:52 MDT LOG: MultiXact member wraparound protections are now enabled 2018-03-21 08:08:52 MDT LOG: database system is ready to accept connections 2018-03-21 08:08:52 MDT LOG: autovacuum launcher started 2018-03-22 10:14:22 MDT LOG: incomplete startup packet 2018-03-22 10:14:22 MDT FATAL: the database system is starting up 2018-03-22 10:14:22 MDT LOG: database system was interrupted; last known up at 2018-03-21 08:08:51 MDT 2018-03-22 10:14:22 MDT FATAL: the database system is starting up 2018-03-22 10:14:23 MDT FATAL: the database system is starting up 2018-03-22 10:14:23 MDT FATAL: the database system is starting up 2018-03-22 10:14:24 MDT FATAL: the database system is starting up 2018-03-22 10:14:24 MDT FATAL: the database system is starting up 2018-03-22 10:14:25 MDT FATAL: the database system is starting up 2018-03-22 10:14:25 MDT FATAL: the database system is starting up 2018-03-22 10:14:26 MDT FATAL: the database system is starting up 2018-03-22 10:14:26 MDT FATAL: the database system is starting up 2018-03-22 10:14:27 MDT FATAL: the database system is starting up 2018-03-22 10:14:27 MDT LOG: incomplete startup packet 2018-03-22 10:14:28 MDT LOG: database system was not properly shut down; automatic recovery in progress 2018-03-22 10:14:28 MDT LOG: record with zero length at 0/16178450 2018-03-22 10:14:28 MDT LOG: redo is not required 2018-03-22 10:14:28 MDT LOG: MultiXact member wraparound protections are now enabled 2018-03-22 10:14:28 MDT LOG: database system is ready to accept connections 2018-03-22 10:14:28 MDT LOG: autovacuum launcher started 2018-03-22 11:15:02 MDT LOG: received fast shutdown request 2018-03-22 11:15:02 MDT LOG: aborting any active transactions 2018-03-22 11:15:02 MDT LOG: autovacuum launcher shutting down 2018-03-22 11:15:02 MDT LOG: shutting down 2018-03-22 11:15:02 MDT LOG: database system is shut down 2018-03-22 11:15:03 MDT LOG: database system was shut down at 2018-03-22 11:15:02 MDT 2018-03-22 11:15:03 MDT LOG: MultiXact member wraparound protections are now enabled 2018-03-22 11:15:03 MDT LOG: database system is ready to accept connections 2018-03-22 11:15:03 MDT LOG: autovacuum launcher started 2018-03-22 11:15:03 MDT LOG: incomplete startup packet Ken Beck Liberty, Utah, USA On 03/27/2018 12:04 PM, Joshua D. Drake wrote: > On 03/27/2018 11:00 AM, Ken Beck wrote: >> And, looking for log files, I find none. > > Nothing in /var/log/postgresql or /var/lib/postgresql/9.3/main/pg_log? > > JD > -- > > Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc > *** A fault and talent of mine is to tell it exactly how it is. *** > PostgreSQL centered full stack support, consulting and development. > Advocate: @amplifypostgres || Learn: https://postgresconf.org > * Unless otherwise stated, opinions are my own. * >
Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
I am working on two systems, one running in Oracle VirtualBox on my laptop, the other in a DigitalOcean droplet. I know on one of them I tried to remove the postgres-9.6, and it must have been my laptop, here is the output from pg_lsclusters: Ver Cluster Port Status Owner Data directory Log file 9.3 main 5432 down postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log Is it possible my effort to remove 9.6 was not complete or otherwise screwed things up? (I dont remember the commands I used to do that, sorry) Ken Beck Liberty, Utah, USA On 03/27/2018 12:17 PM, Adrian Klaver wrote: > 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@9.3-main.service loaded failed
Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
Thanks for both responses -- the file contents I sent were from file named postgresql-9.3-main.log.1, and I am assuming this means from the 9.3, not 9.6. Sorry I did not include that earlier. Starting to sousnds like I need to consult Ubuntu user groups. Ken Beck Liberty, Utah, USA On 03/28/2018 10:46 AM, Moreno Andreo wrote: > Il 28/03/2018 18:28, Adrian Klaver ha scritto: >> On 03/28/2018 09:24 AM, Moreno Andreo wrote: >>> Il 27/03/2018 22:57, Adrian Klaver ha scritto: >>>> On 03/27/2018 01:46 PM, Ken Beck wrote: >>>>> I am working on two systems, one running in Oracle VirtualBox on my >>>>> laptop, the other in a DigitalOcean droplet. I know on one of them I >>>>> tried to remove the postgres-9.6, and it must have been my laptop, >>>>> here >>>>> is the output from pg_lsclusters: >>>>> >>>>> Ver Cluster Port Status Owner Data directory Log file >>>>> 9.3 main 5432 down postgres /var/lib/postgresql/9.3/main >>>>> /var/log/postgresql/postgresql-9.3-main.log >>>> >>>> What happens if you do?: >>>> >>>> sudo pg_ctlcluster 9.3 main start >>> If possible, I'll take a look at >>> /var/log/postgresql/postgresql-9.3-main.log. >>> That may give us a big help to uncover what's goin'on. >> >> See this post: >> https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com >> > Yes I saw it, but I can't say if that log was from 9.3 or 9.6 > cluster... as you can see above he wrote that he had a 9.6, that he > tried to remove, and a 9.3, which was not starting. So it's not clear > to me if the log he sent was from one version or the another... > >> >>> >>> Cheers >>> >>> Moreno.- >>> >>> >>> >> >> > > >
Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade
The current log is 0 length, and indeed empty. I just tried 'sudo pg_ctlcluster 9.3 main start' and got the following error: Error: Config owner (kcb:1000) and data owner (postgres:116) do not match, and config owner is not root Changed to user postgres, tried again and got: Warning: the cluster will not be running as a systemd service. Consider using systemctl: sudo systemctl start postgresql@9.3-main Removed stale pid file. Next tried the suggestion: sudo systemctl start postgresql@9.3-main, but the system failed to accept the password for user postgres. That is NOT something I changed during the Ubuntu upgrade process, so that has been a puzzle as well. My main login, user kcb is OK, password still works. So I change back to user kcb and try the suggestion again: kcb: jobs(0)$ sudo systemctl start postgresql@9.3-main Job for postgresql@9.3-main.service failed because the control process exited with error code. See "systemctl status postgresql@9.3-main.service" and "journalctl -xe" for details. kcb: jobs(0)$ systemctl status postgresql@9.3-main.service ● postgresql@9.3-main.service - PostgreSQL Cluster 9.3-main Loaded: loaded (/lib/systemd/system/postgresql@.service; disabled; vendor preset: enabled) Active: failed (Result: exit-code) since Wed 2018-03-28 19:51:50 MDT; 47s ago Process: 2960 ExecStart=postgresql@%i --skip-systemctl-redirect %i start (code=exited, status= Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Starting PostgreSQL Cluster 9.3-main... Mar 28 19:51:50 kcb-VirtualBox postgresql@9.3-main[2960]: Error: Config owner (kcb:1000) and dat Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service: Control process exited, Mar 28 19:51:50 kcb-VirtualBox systemd[1]: Failed to start PostgreSQL Cluster 9.3-main. Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service: Unit entered failed stat Mar 28 19:51:50 kcb-VirtualBox systemd[1]: postgresql@9.3-main.service: Failed with result 'exit Is it possible a big problem is that the password for user postgres not longer works for some reason, not sure why. It appears the password can not be rest or changed without knowing the original, and what I thought was the original no longer works. Output of that is in the attached file; On 03/28/2018 04:58 PM, Adrian Klaver wrote: > On 03/28/2018 03:45 PM, Ken Beck wrote: >> Thanks for both responses -- the file contents I sent were from file >> named postgresql-9.3-main.log.1, and I am assuming this means from the > > Well that is an archived log and would show old information. The > current log would be postgresql-9.3-main.log. > >> 9.3, not 9.6. Sorry I did not include that earlier. Starting to sousnds >> like I need to consult Ubuntu user groups. > > Not necessarily as there are many Ubuntu users on this list. > > Did you try?: > > sudo pg_ctlcluster 9.3 main start > >> >> Ken Beck >> Liberty, Utah, USA >> >> On 03/28/2018 10:46 AM, Moreno Andreo wrote: >>> Il 28/03/2018 18:28, Adrian Klaver ha scritto: >>>> On 03/28/2018 09:24 AM, Moreno Andreo wrote: >>>>> Il 27/03/2018 22:57, Adrian Klaver ha scritto: >>>>>> On 03/27/2018 01:46 PM, Ken Beck wrote: >>>>>>> I am working on two systems, one running in Oracle VirtualBox on my >>>>>>> laptop, the other in a DigitalOcean droplet. I know on one of >>>>>>> them I >>>>>>> tried to remove the postgres-9.6, and it must have been my laptop, >>>>>>> here >>>>>>> is the output from pg_lsclusters: >>>>>>> >>>>>>> Ver Cluster Port Status Owner Data directory Log file >>>>>>> 9.3 main 5432 down postgres /var/lib/postgresql/9.3/main >>>>>>> /var/log/postgresql/postgresql-9.3-main.log >>>>>> >>>>>> What happens if you do?: >>>>>> >>>>>> sudo pg_ctlcluster 9.3 main start >>>>> If possible, I'll take a look at >>>>> /var/log/postgresql/postgresql-9.3-main.log. >>>>> That may give us a big help to uncover what's goin'on. >>>> >>>> See this post: >>>> https://www.postgresql.org/message-id/5bad1f01-d06a-1526-96d2-c9cfd6062a11%40gmail.com >>>> >>>> >>> Yes I saw it, but I can't say if that log was from 9.3 or 9.6 >>> cluster... as you can see above he wrote that he had a 9.6, that he >>> tried to remove, and a 9.3, which was not starting. So it's not clear >>> to me if the log he sent was from one version or the another... >>> >>>> >>>>> >>>>> Cheers >
