Debian and Postgres

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8taVHwSF68wM=VMY7V-A@mail.gmail.com

where the solution:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
taVHwSF68wM=VMY7V-A@mail.gmail.com

where the solution:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
taVHwSF68wM=VMY7V-A@mail.gmail.com

where the solution:

/messages/by-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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

/messages/by-id/CAM3xazWDA6asEDDYHcKF_5oSFP4SZj8
taVHwSF68wM=VMY7V-A@mail.gmail.com

where the solution:

/messages/by-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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general