Обсуждение: "could not receive data from client" && "incomplete startup packet"
Hi:
I'm experiencing what appears to be an intermittent db connection problem of some sort (although I may be wrong about that). I have a v9.3.0 PG DB on a linux box as the DB server. The client is a virtual machine (also linux) running the same version...
server side:
sqf=# select version();
version
-----------------------------------------------------------------------------------
PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.1, 64-bit
(1 row)
client side:
sqf=> select version();
version
-----------------------------------------------------------------------------------
PostgreSQL 9.3.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.1, 64-bit
(1 row)
My app is a perl script that I wrote using DBI and the PG DBD. Looks like these are the versions...
/tool/pandora64/.package/perl-5.18.2-gcc481/lib/site_perl/5.18.2/x86_64-linux-thread-multi/Bundle/DBI.pm
/tool/pandora64/.package/perl-5.18.2-gcc481/lib/site_perl/5.18.2/x86_64-linux-thread-multi/DBD/Pg.pm
I put print statements around the DB connection code to get a sense of what's happening there. Sometimes it connects right away, sometimes there's a delay of a few seconds, sometimes it times out with an error. If it does connect, sometimes it appears the connection will be dropped. Where in the code it is dropped can vary. It's not malformed sql code or anything like that. The error trapping (printing $dbh->err() ) returns nothing. (I tested this code by giving it a malconstructed query using a different script and it gives me plenty !). So I think the error trapping code is OK
I shut down and restarted the DB instance. No difference.
The DB log file back on the server side is replete with...
"could not receive data from client: Connection reset by peer "
followed by
"incomplete startup package"
messages....
LOG: database system was shut down at 2018-02-03 22:46:39 EST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
If it makes a difference, this problem seemed to begin when I created a second DB on the instance.
I don't seem to have a problem connecting to the DB from the client side using good-ole psql. It's always fast and stays
The last clue I can think of is if the initial DB connection times out, a quick re-invocation of the script connects immediately (as if the first primed it in some way).
Again, the client is a virtual machine, but the server is on metal.
Any help would be appreciated!
Thanks
Re: "could not receive data from client" && "incomplete startup packet"
От
"David G. Johnston"
Дата:
I have a v9.3.0 PG DB on a linux box as the DB server.
[...]
Any help would be appreciated!
Upgrade to 9.3.20 and ensure you are using a current version of Perl and DB modules.
David J.
Hi David, thanks for the response.
I upgraded to 9.3.2 and recreated the DB there. So far, so good. But nights are usually the worse time, so we'll see later.
There is a more current version of the perl code, but that's not the project default at this time. If the PG upgrade fixes this, I'll be happy with that, else I'll point to the more recent perl versions.
Thanks Again
On Wed, Feb 7, 2018 at 9:47 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
I have a v9.3.0 PG DB on a linux box as the DB server.[...]Any help would be appreciated!Upgrade to 9.3.20 and ensure you are using a current version of Perl and DB modules.David J.
David Gauthier wrote: > Hi David, thanks for the response. > > I upgraded to 9.3.2 and recreated the DB there. So far, so good. I sincerely hope you mean 9.3.20 and not 9.3.2. There are critical bugs that can eat your data in the 9.3 series prior to 9.3.11, so by all means do upgrade to the latest version if you're on 9.3.2. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
I think I found out what's going on.
In the perl script, I'm "forking" a parallel process. The DB connection gets messed up as a consequence (sorry, I can't articulate better because I don't fully understand). But the answer (at least what worked for me), is to formally disconnect and then reconnect back in the main thread. Fortunately, I don't have any outstanding transactions at that time so I don't lose anything as far as that's concerned.
my $pid = fork()
if($pid == 0) {
<do whatever the forked proc needs to do>
}
$dbh->disconnect();
$dbh->connect(...)
proceed with the rest of the perl script.
On Wed, Feb 7, 2018 at 11:59 AM, David Gauthier <davegauthierpg@gmail.com> wrote:
Hi David, thanks for the response.I upgraded to 9.3.2 and recreated the DB there. So far, so good. But nights are usually the worse time, so we'll see later.There is a more current version of the perl code, but that's not the project default at this time. If the PG upgrade fixes this, I'll be happy with that, else I'll point to the more recent perl versions.Thanks AgainOn Wed, Feb 7, 2018 at 9:47 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:I have a v9.3.0 PG DB on a linux box as the DB server.[...]Any help would be appreciated!Upgrade to 9.3.20 and ensure you are using a current version of Perl and DB modules.David J.
I thin kit's 9.3.2 :-(
% psql -V
psql (PostgreSQL) 9.3.2
These are my options...
ls -ld /tool/pandora64/.package/postgresql*
drwxr-xr-x 8 pandora pandora 4096 Sep 2 2016 /tool/pandora64/.package/postgresql-8.2.3
drwxr-xr-x 8 pandora pandora 4096 Sep 16 2013 /tool/pandora64/.package/postgresql-8.3.0
drwxr-xr-x 6 pandora pandora 4096 Aug 31 2015 /tool/pandora64/.package/postgresql-9.0.2
drwxr-xr-x 6 pandora pandora 4096 Oct 11 2013 /tool/pandora64/.package/postgresql-9.3.0
drwxr-xr-x 6 pandora pandora 4096 May 30 2014 /tool/pandora64/.package/postgresql-9.3.2
drwxr-xr-x 6 pandora pandora 4096 Oct 19 2016 /tool/pandora64/.package/postgresql-9.6.0
What about 9.6.0 ?
On Wed, Feb 7, 2018 at 4:47 PM, David Gauthier <davegauthierpg@gmail.com> wrote:
I think I found out what's going on.In the perl script, I'm "forking" a parallel process. The DB connection gets messed up as a consequence (sorry, I can't articulate better because I don't fully understand). But the answer (at least what worked for me), is to formally disconnect and then reconnect back in the main thread. Fortunately, I don't have any outstanding transactions at that time so I don't lose anything as far as that's concerned.my $pid = fork()if($pid == 0) {<do whatever the forked proc needs to do>}$dbh->disconnect();$dbh->connect(...)proceed with the rest of the perl script.On Wed, Feb 7, 2018 at 11:59 AM, David Gauthier <davegauthierpg@gmail.com> wrote:Hi David, thanks for the response.I upgraded to 9.3.2 and recreated the DB there. So far, so good. But nights are usually the worse time, so we'll see later.There is a more current version of the perl code, but that's not the project default at this time. If the PG upgrade fixes this, I'll be happy with that, else I'll point to the more recent perl versions.Thanks AgainOn Wed, Feb 7, 2018 at 9:47 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:I have a v9.3.0 PG DB on a linux box as the DB server.[...]Any help would be appreciated!Upgrade to 9.3.20 and ensure you are using a current version of Perl and DB modules.David J.
David Gauthier wrote: > I thin kit's 9.3.2 :-( > > % psql -V > psql (PostgreSQL) 9.3.2 Ah, you're screwed then. My condolences. > These are my options... > ls -ld /tool/pandora64/.package/postgresql* > drwxr-xr-x 8 pandora pandora 4096 Sep 2 2016 /tool/pandora64/.package/postgresql-8.2.3 > drwxr-xr-x 8 pandora pandora 4096 Sep 16 2013 /tool/pandora64/.package/postgresql-8.3.0 > drwxr-xr-x 6 pandora pandora 4096 Aug 31 2015 /tool/pandora64/.package/postgresql-9.0.2 > drwxr-xr-x 6 pandora pandora 4096 Oct 11 2013 /tool/pandora64/.package/postgresql-9.3.0 > drwxr-xr-x 6 pandora pandora 4096 May 30 2014 /tool/pandora64/.package/postgresql-9.3.2 > drwxr-xr-x 6 pandora pandora 4096 Oct 19 2016 /tool/pandora64/.package/postgresql-9.6.0 > > What about 9.6.0 ? These are all terrible choices. I suggest you change to some other packaging tool, if this pandora64 thing (whatever it is) only offers these particular Postgres versions. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi Alvaro:
It's a corporate "public" posting, so I can't simply install stuff there. However, I can request that new version(s) be put there. What version would you recommend ?
-dave
On Wed, Feb 7, 2018 at 6:24 PM, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
David Gauthier wrote:
> I thin kit's 9.3.2 :-(
>
> % psql -V
> psql (PostgreSQL) 9.3.2
Ah, you're screwed then. My condolences.
> These are my options...
> ls -ld /tool/pandora64/.package/postgresql*
> drwxr-xr-x 8 pandora pandora 4096 Sep 2 2016 /tool/pandora64/.package/postgresql-8.2.3
> drwxr-xr-x 8 pandora pandora 4096 Sep 16 2013 /tool/pandora64/.package/postgresql-8.3.0
> drwxr-xr-x 6 pandora pandora 4096 Aug 31 2015 /tool/pandora64/.package/postgresql-9.0.2
> drwxr-xr-x 6 pandora pandora 4096 Oct 11 2013 /tool/pandora64/.package/postgresql-9.3.0
> drwxr-xr-x 6 pandora pandora 4096 May 30 2014 /tool/pandora64/.package/postgresql-9.3.2
> drwxr-xr-x 6 pandora pandora 4096 Oct 19 2016 /tool/pandora64/.package/postgresql-9.6.0
>
> What about 9.6.0 ?
These are all terrible choices. I suggest you change to some other
packaging tool, if this pandora64 thing (whatever it is) only offers
these particular Postgres versions.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
David Gauthier <davegauthierpg@gmail.com> writes: > It's a corporate "public" posting, so I can't simply install stuff there. > However, I can request that new version(s) be put there. What version > would you recommend ? The important point is that you should be running the latest, or nearly latest, minor release in whichever PG release series you've chosen. The reason this list seems like such a fail: >>> These are my options... >>> ls -ld /tool/pandora64/.package/postgresql* >>> drwxr-xr-x 8 pandora pandora 4096 Sep 2 2016 /tool/pandora64/.package/postgresql-8.2.3 >>> drwxr-xr-x 8 pandora pandora 4096 Sep 16 2013 /tool/pandora64/.package/postgresql-8.3.0 >>> drwxr-xr-x 6 pandora pandora 4096 Aug 31 2015 /tool/pandora64/.package/postgresql-9.0.2 >>> drwxr-xr-x 6 pandora pandora 4096 Oct 11 2013 /tool/pandora64/.package/postgresql-9.3.0 >>> drwxr-xr-x 6 pandora pandora 4096 May 30 2014 /tool/pandora64/.package/postgresql-9.3.2 >>> drwxr-xr-x 6 pandora pandora 4096 Oct 19 2016 /tool/pandora64/.package/postgresql-9.6.0 is that whoever is producing the packages seems to be content to wrap one early release in each branch (or some branches) and then never follow up. Those early releases are, almost by definition, not as stable as their branches will be in a year or two. They also contain known security issues of assorted severities. For reference, the current latest minor releases are 10.2, 9.6.7, 9.5.11, 9.4.16, 9.3.21 (all newly minted today, as it happens). The 9.0.x and 8.x.x release branches have been out of support for years. regards, tom lane