Thread: Debian and Postgres
Hello, This is a Debian problem that has caused a problem starting Postgres. So, I'm posting it here in the hope that somebody has experienced it previously and can assist me in fixing it. Yesterday, it started up as per normal and first command issued via JDBC driver was run:- 2016-05-04 10:39:39 AESTLOG: MultiXact member wraparound protections are now enabled 2016-05-04 10:39:39 AESTLOG: database system is ready to accept connections 2016-05-04 10:39:39 AESTLOG: autovacuum launcher started 2016-05-04 10:40:00 AESTLOG: duration: 224.906 ms parse <unnamed>: SET extra_float_digits = 3 Today, the following was in the log:- 2016-05-05 03:44:53 AESTLOG: test message did not get through on socket for statistics collector 2016-05-05 03:44:53 AESTLOG: disabling statistics collector for lack of working socket 2016-05-05 03:44:53 AESTWARNING: autovacuum not started because of misconfiguration 2016-05-05 03:44:53 AESTHINT: Enable the "track_counts" option. 2016-05-05 03:44:53 AESTLOG: database system was shut down at 2016-05- 04 11:56:37 AEST 2016-05-05 03:44:54 AESTLOG: MultiXact member wraparound protections are now enabled 2016-05-05 03:44:54 AESTLOG: database system is ready to accept connections 2016-05-05 03:58:29 AESTLOG: duration: 787.241 ms statement: select count(*) from boiler_plate; Relative section of conf file unchanged for months:- # - Query/Index Statistics Collector - #track_activities = on track_counts = on #track_io_timing = off #track_functions = none # none, pl, all #track_activity_query_size = 1024 # (change requires restart) stats_temp_directory = 'pg_stat_tmp' # - Statistics Monitoring - #log_parser_stats = off #log_planner_stats = off #log_executor_stats = off #log_statement_stats = off I can connect via psql and issue queries without any problems. Trying to connect via JDBC fails. Trying to connect by an application fails. I use synaptic for package management. Yesterday I was running short on space and removed some packages described as *obsolete* by synaptic. My guess is that a dependency chain is incorrect and a certain package is not in fact obsolete. For some reason the synaptic history log only shows tzdata-java as being removed but in fact several libraries and other packages were permanently removed. The dpkg logs only show installation details. I don't know why synaptic failed to record the removals in its history logs as if it had, then I could trawl thru the logs and restore the missing packages. The apt logs do not list removals either. Versions:- Java JDK version 1.8.0_91 Linux [ 4.5.0-1-amd64 ] JDBC 9.4-1208 Postgres psql (9.5.2) libpq5_9.5.2-1_amd64.deb Any assistance appreciated. Thanks, Rob
rob stone <floriparob@gmail.com> writes:
> This is a Debian problem that has caused a problem starting Postgres.
> Today, the following was in the log:-
> 2016-05-05 03:44:53 AESTLOG: test message did not get through on
> socket for statistics collector
> I can connect via psql and issue queries without any problems. Trying
> to connect via JDBC fails. Trying to connect by an application fails.
Sounds like you've got a firewall that is blocking local TCP connections
into and out of Postgres. This doesn't have anything to do with package
addition or removal, it's an over-aggressive packet filter.
regards, tom lane
On 05/04/2016 01:55 PM, rob stone wrote: > Hello, > > This is a Debian problem that has caused a problem starting Postgres. > So, I'm posting it here in the hope that somebody has experienced it > previously and can assist me in fixing it. > > Yesterday, it started up as per normal and first command issued via > JDBC driver was run:- > > 2016-05-04 10:39:39 AESTLOG: MultiXact member wraparound protections > are now enabled > 2016-05-04 10:39:39 AESTLOG: database system is ready to accept > connections > 2016-05-04 10:39:39 AESTLOG: autovacuum launcher started > 2016-05-04 10:40:00 AESTLOG: duration: 224.906 ms parse <unnamed>: > SET extra_float_digits = 3 > > > Today, the following was in the log:- > > 2016-05-05 03:44:53 AESTLOG: test message did not get through on > socket for statistics collector > 2016-05-05 03:44:53 AESTLOG: disabling statistics collector for lack > of working socket > 2016-05-05 03:44:53 AESTWARNING: autovacuum not started because of > misconfiguration > 2016-05-05 03:44:53 AESTHINT: Enable the "track_counts" option. > 2016-05-05 03:44:53 AESTLOG: database system was shut down at 2016-05- > 04 11:56:37 AEST > 2016-05-05 03:44:54 AESTLOG: MultiXact member wraparound protections > are now enabled > 2016-05-05 03:44:54 AESTLOG: database system is ready to accept > connections > 2016-05-05 03:58:29 AESTLOG: duration: 787.241 ms statement: select > count(*) from boiler_plate; > > > Relative section of conf file unchanged for months:- > > # - Query/Index Statistics Collector - > > #track_activities = on > track_counts = on > #track_io_timing = off > #track_functions = none # none, pl, all > #track_activity_query_size = 1024 # (change requires restart) > stats_temp_directory = 'pg_stat_tmp' > > > # - Statistics Monitoring - > > #log_parser_stats = off > #log_planner_stats = off > #log_executor_stats = off > #log_statement_stats = off > > > I can connect via psql and issue queries without any problems. Trying > to connect via JDBC fails. Trying to connect by an application fails. Are you using the same connection parameters? In particular are you using local for the psql connection and some form of host for the others? I ask because this looks somewhat similar to this thread: http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8taVHwSF68wM=VMY7V-A@mail.gmail.com where the solution: http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8taVHwSF68wM=VMY7V-A@mail.gmail.com was: "That was indeed the root cause. The /etc/hosts file on the server had incorrect permissions which caused localhost to not resolve." > > I use synaptic for package management. Yesterday I was running short on > space and removed some packages described as *obsolete* by synaptic. > My guess is that a dependency chain is incorrect and a certain package > is not in fact obsolete. > For some reason the synaptic history log only shows tzdata-java as > being removed but in fact several libraries and other packages were > permanently removed. The dpkg logs only show installation details. > I don't know why synaptic failed to record the removals in its history > logs as if it had, then I could trawl thru the logs and restore the > missing packages. > The apt logs do not list removals either. > > Versions:- > > Java JDK version 1.8.0_91 > Linux [ 4.5.0-1-amd64 ] > JDBC 9.4-1208 > Postgres psql (9.5.2) > libpq5_9.5.2-1_amd64.deb > > > Any assistance appreciated. > > Thanks, > Rob > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote: > > > I can connect via psql and issue queries without any problems. > > Trying > > to connect via JDBC fails. Trying to connect by an application > > fails. > Are you using the same connection parameters? > > In particular are you using local for the psql connection and some > form > of host for the others? > > I ask because this looks somewhat similar to this thread: > > http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8 > taVHwSF68wM=VMY7V-A@mail.gmail.com > > where the solution: > > http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8 > taVHwSF68wM=VMY7V-A@mail.gmail.com > > was: > > "That was indeed the root cause. The /etc/hosts file on the server > had > incorrect permissions which caused localhost to not resolve." > > /etc/hosts has a file date of Dec 19 2014. Everything worked fine yesterday. Absolutely nothing has been altered except some packages have been removed and none of the log files can give me a clue as to which ones. I'll just have to continue hunting around trying to figure out what I did and probably file a bug report against synaptic for losing the removal info from its history logs. Cheers, Rob
On 05/04/2016 04:38 PM, rob stone wrote: > On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote: >> >>> I can connect via psql and issue queries without any problems. >>> Trying >>> to connect via JDBC fails. Trying to connect by an application >>> fails. >> Are you using the same connection parameters? >> >> In particular are you using local for the psql connection and some >> form >> of host for the others? >> >> I ask because this looks somewhat similar to this thread: >> >> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8 >> taVHwSF68wM=VMY7V-A@mail.gmail.com >> >> where the solution: >> >> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8 >> taVHwSF68wM=VMY7V-A@mail.gmail.com >> >> was: >> >> "That was indeed the root cause. The /etc/hosts file on the server >> had >> incorrect permissions which caused localhost to not resolve." >> >> > > /etc/hosts has a file date of Dec 19 2014. Did you see Tom's post. > > Everything worked fine yesterday. Absolutely nothing has been altered > except some packages have been removed and none of the log files can > give me a clue as to which ones. Hmm, it is Ubuntu not Debian, but: sudo apt-get remove whois vi /var/log/dpkg.log 2016-05-04 16:42:39 status installed whois:amd64 5.1.1 2016-05-04 16:42:39 remove whois:amd64 5.1.1 <none> 2016-05-04 16:42:39 status half-configured whois:amd64 5.1.1 2016-05-04 16:42:39 status half-installed whois:amd64 5.1.1 2016-05-04 16:42:39 status triggers-pending man-db:amd64 2.6.7.1-1ubuntu1 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 2016-05-04 16:42:39 status not-installed whois:amd64 <none> vi /var/log/apt/history.log Start-Date: 2016-05-04 16:42:39 Commandline: apt-get remove whois Remove: whois:amd64 (5.1.1) End-Date: 2016-05-04 16:42:39 > > I'll just have to continue hunting around trying to figure out what I > did and probably file a bug report against synaptic for losing the > removal info from its history logs. > > Cheers, > Rob > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, 2016-05-04 at 16:50 -0700, Adrian Klaver wrote: > On 05/04/2016 04:38 PM, rob stone wrote: > > > > > Hmm, it is Ubuntu not Debian, but: > > sudo apt-get remove whois > > vi /var/log/dpkg.log > > 2016-05-04 16:42:39 status installed whois:amd64 5.1.1 > 2016-05-04 16:42:39 remove whois:amd64 5.1.1 <none> > 2016-05-04 16:42:39 status half-configured whois:amd64 5.1.1 > 2016-05-04 16:42:39 status half-installed whois:amd64 5.1.1 > 2016-05-04 16:42:39 status triggers-pending man-db:amd64 2.6.7.1- > 1ubuntu1 > 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 > 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 > 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 > 2016-05-04 16:42:39 status not-installed whois:amd64 <none> > > > vi /var/log/apt/history.log > > Start-Date: 2016-05-04 16:42:39 > Commandline: apt-get remove whois > Remove: whois:amd64 (5.1.1) > End-Date: 2016-05-04 16:42:39 > Hello Adrian, I read Tom's post. There is no firewall. Stand alone box. The apt history.log is overwritten each time. There is a term.log (full of Control-M's) which lists all the activity, including the removals, as follows:- liblept4 replaced by liblept5 and is used for image processing. liblwres90 replaced by liblwres141 used by BIND libpcrecpp0 replaced by libcrecpp0v5 used for Perl Regex libsodium13 replaced by libsodium18 NaCl library tzdata-java now gone for good, and libdbus2.0-cil removed from Mono -- not mono -- as in mono-runtime, etc. So, liblwres and libsodium are the two obvious culprits. I'll have to obtain the sources and diff the files to see if something contained in the earlier version is missing or altered in the latest version. I have also gone thru the major packages that have anything to do with ports, sockets, etc. such as network-manager, isc-dhcp-client, doing an "apt show" to list dependencies, then verifying that those dependencies are the correct versions. So far libreadline6 and libdns-export162 were earlier versions and libteam-utils was missing. So, I'll let you know what else I find. Thanks for the help. Cheers, Rob
On 05/04/2016 07:40 PM, rob stone wrote: > On Wed, 2016-05-04 at 16:50 -0700, Adrian Klaver wrote: >> On 05/04/2016 04:38 PM, rob stone wrote: >>> >>> >> Hmm, it is Ubuntu not Debian, but: >> >> sudo apt-get remove whois >> >> vi /var/log/dpkg.log >> >> 2016-05-04 16:42:39 status installed whois:amd64 5.1.1 >> 2016-05-04 16:42:39 remove whois:amd64 5.1.1 <none> >> 2016-05-04 16:42:39 status half-configured whois:amd64 5.1.1 >> 2016-05-04 16:42:39 status half-installed whois:amd64 5.1.1 >> 2016-05-04 16:42:39 status triggers-pending man-db:amd64 2.6.7.1- >> 1ubuntu1 >> 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 >> 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 >> 2016-05-04 16:42:39 status config-files whois:amd64 5.1.1 >> 2016-05-04 16:42:39 status not-installed whois:amd64 <none> >> >> >> vi /var/log/apt/history.log >> >> Start-Date: 2016-05-04 16:42:39 >> Commandline: apt-get remove whois >> Remove: whois:amd64 (5.1.1) >> End-Date: 2016-05-04 16:42:39 >> > > Hello Adrian, > > I read Tom's post. There is no firewall. Stand alone box. > > The apt history.log is overwritten each time. So Debian does not rotate the logs into history.log.<int>.gz? > There is a term.log (full of Control-M's) which lists all the activity, > including the removals, as follows:- > > liblept4 replaced by liblept5 and is used for image processing. > liblwres90 replaced by liblwres141 used by BIND > libpcrecpp0 replaced by libcrecpp0v5 used for Perl Regex > libsodium13 replaced by libsodium18 NaCl library > tzdata-java now gone for good, and > libdbus2.0-cil removed from Mono -- not mono -- as in mono-runtime, > etc. > > So, liblwres and libsodium are the two obvious culprits. I'll have to > obtain the sources and diff the files to see if something contained in > the earlier version is missing or altered in the latest version. > > I have also gone thru the major packages that have anything to do with > ports, sockets, etc. such as network-manager, isc-dhcp-client, doing an > "apt show" to list dependencies, then verifying that those dependencies > are the correct versions. So far libreadline6 and libdns-export162 were > earlier versions and libteam-utils was missing. > > So, I'll let you know what else I find. So what are the connection parameters that work for you with psql? > > Thanks for the help. > > Cheers, > Rob > -- Adrian Klaver adrian.klaver@aklaver.com
On 5/4/2016 1:55 PM, rob stone wrote:
I can connect via psql and issue queries without any problems. Trying to connect via JDBC fails. Trying to connect by an application fails.
one potential difference, psql will connect via a unix domain socket if you don't specify a -h hostname, while JDBC can only connect via a tcp socket. jdbc connecting to localhost will match `host` lines in the pg_hba.conf file, while psql connecting without a host specification will match `local` line(s).
so, please show us your jdbc connection string, and your psql command line, and also show us your pg_hba.conf file.
-- john r pierce, recycling bits in santa cruz
On 5/4/16, rob stone <floriparob@gmail.com> wrote: > On Wed, 2016-05-04 at 14:51 -0700, Adrian Klaver wrote: >> I ask because this looks somewhat similar to this thread: >> >> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8 >> taVHwSF68wM=VMY7V-A@mail.gmail.com >> >> where the solution: >> >> http://www.postgresql.org/message-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8 >> taVHwSF68wM=VMY7V-A@mail.gmail.com >> >> was: >> >> "That was indeed the root cause. The /etc/hosts file on the server >> had >> incorrect permissions which caused localhost to not resolve." > > /etc/hosts has a file date of Dec 19 2014. > > Everything worked fine yesterday. Absolutely nothing has been altered > except some packages have been removed and none of the log files can > give me a clue as to which ones. For reference Debian (and I assume Ubuntu) has a little package called etckeeper - I find it very handy in such situations.
Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote: > > So Debian does not rotate the logs into history.log.<int>.gz? > Yes, it does! Didn't realise it. You learn something every day. 23 packages removed and 31 purged. Going thru the list slowly. Thanks, Rob
On 05/05/2016 01:29 PM, rob stone wrote: > Hello Adrian,On Wed, 2016-05-04 at 21:08 -0700, Adrian Klaver wrote: >> >> So Debian does not rotate the logs into history.log.<int>.gz? >> > > Yes, it does! Didn't realise it. You learn something every day. > > 23 packages removed and 31 purged. > > Going thru the list slowly. Before you go all the way through, can you answer the question posed by myself and others: What connection parameters work(psql) versus those that do not(JDBC, application)? Also try using psql with the parameters that do not work and report back the error message if any. The results will probably help narrow the list of suspects. > > Thanks, > Rob > -- Adrian Klaver adrian.klaver@aklaver.com
Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote: > On 5/4/2016 1:55 PM, rob stone wrote: > > I can connect via psql and issue queries without any problems. > > Trying > > to connect via JDBC fails. Trying to connect by an application > > fails. > > one potential difference, psql will connect via a unix domain socket > if you don't specify a -h hostname, while JDBC can only connect via > a tcp socket. jdbc connecting to localhost will match `host` > lines in the pg_hba.conf file, while psql connecting without a host > specification will match `local` line(s). > > so, please show us your jdbc connection string, and your psql command > line, and also show us your pg_hba.conf file. > Yes, I'm aware of the difference. The pg_hba.conf file has a date of Feb 24, 2016. This problem occurred due to me deleting packages via synaptic a few days ago. I've also just discovered that the resolv.conf file was wacked. So it has to have something to do with the network stack. No clues in syslog, just the message in the Postgres log about not starting autovacuum when it is supposed to start that process and always has up until now. Cheers, rob
On 05/05/2016 01:40 PM, rob stone wrote: > Hello John,On Wed, 2016-05-04 at 21:43 -0700, John R Pierce wrote: >> On 5/4/2016 1:55 PM, rob stone wrote: >>> I can connect via psql and issue queries without any problems. >>> Trying >>> to connect via JDBC fails. Trying to connect by an application >>> fails. >> >> one potential difference, psql will connect via a unix domain socket >> if you don't specify a -h hostname, while JDBC can only connect via >> a tcp socket. jdbc connecting to localhost will match `host` >> lines in the pg_hba.conf file, while psql connecting without a host >> specification will match `local` line(s). >> >> so, please show us your jdbc connection string, and your psql command >> line, and also show us your pg_hba.conf file. >> > > Yes, I'm aware of the difference. > The pg_hba.conf file has a date of Feb 24, 2016. This problem occurred > due to me deleting packages via synaptic a few days ago. > I've also just discovered that the resolv.conf file was wacked. So it > has to have something to do with the network stack. No clues in syslog, > just the message in the Postgres log about not starting autovacuum when > it is supposed to start that process and always has up until now. Exactly. Showing the list the error you get when you cannot connect help may with solving that problem and save you a great of time. What have you got to lose? > > Cheers, > rob > > -- Adrian Klaver adrian.klaver@aklaver.com
Hello Adrian,On Thu, 2016-05-05 at 13:47 -0700, Adrian Klaver wrote: > > Exactly. Showing the list the error you get when you cannot connect > help > may with solving that problem and save you a great of time. What > have > you got to lose? > I have nothing to "lose". There is NO error, per se. The progress bars just keep churning and absolutely nothing happens. All you can do is cancel. Nothing in the log files. No exceptions thrown. A black hole. I've waited minutes to see if it can connect, but no. I tried running on the 9.4 cluster but the same thing. I appreciate all your suggestions. Tomorrow I'll talk to a friend of mine who is a Java guru to see if it is possible to force some kind of stack trace or something that will provide a clue as to what is happening. If you can't find the driver you'd expect a DriverManager exception to be thrown or maybe a ClassNotFound. Not even this. It's the lack of any error message anywhere that is frustating. Cheers, Rob
On 05/05/2016 07:29 PM, rob stone wrote: > Hello Adrian,On Thu, 2016-05-05 at 13:47 -0700, Adrian Klaver wrote: >> >> Exactly. Showing the list the error you get when you cannot connect >> help >> may with solving that problem and save you a great of time. What >> have >> you got to lose? >> > > I have nothing to "lose". > There is NO error, per se. The progress bars just keep churning and > absolutely nothing happens. All you can do is cancel. > Nothing in the log files. No exceptions thrown. A black hole. I've That would be the Postgres logs or something else? > waited minutes to see if it can connect, but no. I tried running on the > 9.4 cluster but the same thing. So there is more then one cluster on the machine? How where they installed? Leaving aside JDBC/application can you connect to each using psql? If you use psql with the parameters that fail for the application can it connect? > > I appreciate all your suggestions. > Tomorrow I'll talk to a friend of mine who is a Java guru to see if it > is possible to force some kind of stack trace or something that will > provide a clue as to what is happening. If you can't find the driver > you'd expect a DriverManager exception to be thrown or maybe a > ClassNotFound. Not even this. It's the lack of any error message > anywhere that is frustating. > > Cheers, > Rob > -- Adrian Klaver adrian.klaver@aklaver.com
W dniu 04.05.2016 o 22:55, rob stone pisze: [----------------------] > > I can connect via psql and issue queries without any problems. Trying > to connect via JDBC fails. Trying to connect by an application fails. > Since psql works, have you tried the basic tests to figure out the difference from ODBC connections (following suspected problems with TCP stack/acl as suggested by others). I mean: $ psql template1 # you said this works, so... $ psql -h localhost template1 # does this work? $ psql -h <your_odbc_hostmane> template1 # does that work? $ psql -h <your_odbc_IP_instead_of_hostname> template1 # does that work if the above doesnt? -R