Thread: unexpected EOF on client connection during pg_dumpall

unexpected EOF on client connection during pg_dumpall

From
"Arnold, Sandra L."
Date:

I am currently getting a “unexpected EOF on client connection” when running pg_dumpall from a bash shell script in cron.  I have looked online to see if anyone else is having or have had this problem without any luck.  I am hoping someone can point me in the right direction to determine what is happening.  When I run the command manually it works.  The command that I am running is as follows using a password file to get the password:

 

/app/PostgreSQL/9.1/bin/pg_dumpall –h hostname –p portnumber –f filename –U backup

 

I am currently running PostgreSQL 9.1.9 on Centos 5.9 where the server is a VM instance.  Below is the output from my log.  I have changed the actual hostname to hostname for security reasons.

 

 

2013-07-12 20:03:07.406 EDT,,,12145,"",51e0993b.2f71,1,"",2013-07-12 20:03:07 EDT,,0,LOG,00000,"connection received: host=hostname port=59753",,,,,,,,,""

2013-07-12 20:03:07.410 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,2,"authentication",2013-07-12 20:03:07 EDT,2/88775,0,LOG,00000,"connection authorized: user=backup database=postgres",,,,,,,,

,""

2013-07-12 20:03:07.414 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,3,"idle",2013-07-12 20:03:07 EDT,2/88776,0,LOG,00000,"statement: SET search_path = pg_catalog",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.414 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,4,"SET",2013-07-12 20:03:07 EDT,2/0,0,LOG,00000,"duration: 0.732 ms",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.424 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,5,"idle",2013-07-12 20:03:07 EDT,2/88777,0,LOG,00000,"statement: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, ro

lcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 2",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.430 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,6,"SELECT",2013-07-12 20:03:07 EDT,2/0,0,LOG,00000,"duration: 6.402 ms",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.430 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,7,"idle",2013-07-12 20:03:07 EDT,2/88778,0,LOG,00000,"statement: SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabas

e = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'arnoldsl')",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.431 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,8,"SELECT",2013-07-12 20:03:07 EDT,2/0,0,LOG,00000,"duration: 0.803 ms",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.432 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,9,"idle",2013-07-12 20:03:07 EDT,2/88779,0,LOG,00000,"statement: SELECT setconfig[2] FROM pg_db_role_setting WHERE setdatabas

e = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'arnoldsl')",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.432 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,10,"SELECT",2013-07-12 20:03:07 EDT,2/0,0,LOG,00000,"duration: 0.601 ms",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.433 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,11,"idle",2013-07-12 20:03:07 EDT,2/88780,0,LOG,00000,"statement: SELECT setconfig[1] FROM pg_db_role_setting WHERE setdataba

se = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'backup')",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.433 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,12,"SELECT",2013-07-12 20:03:07 EDT,2/0,0,LOG,00000,"duration: 0.555 ms",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.435 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,13,"idle",2013-07-12 20:03:07 EDT,2/0,0,LOG,08P01,"unexpected EOF on client connection",,,,,,,,,"pg_dumpall"

2013-07-12 20:03:07.435 EDT,"backup","postgres",12145,"hostname:59753",51e0993b.2f71,14,"idle",2013-07-12 20:03:07 EDT,,0,LOG,00000,"disconnection: session time: 0:00:00.029 user=backup database=postgres host=hostname port=59753",,,,,,,,,"pg_dumpall"

 

 

Please let me know if I need to provide any other information.

 

Thanks,

 

Sandra Arnold

Database/Data Warehouse Administrator

MartinFederal Consulting

Consultant to Oak Ridge National Laboratory

Computational Data Analytics Group

Data Architectures Team

Building 5100,  M/S 6173
OAK RIDGE TN 37831-6173

(865)241-1530

 

Re: unexpected EOF on client connection during pg_dumpall

From
Tom Lane
Date:
"Arnold, Sandra L." <arnoldsl@ornl.gov> writes:
> I am currently getting a "unexpected EOF on client connection" when
> running pg_dumpall from a bash shell script in cron.  I have looked
> online to see if anyone else is having or have had this problem
> without any luck.  I am hoping someone can point me in the right
> direction to determine what is happening.  When I run the command
> manually it works.

That's really odd.  The only theory that comes to mind is that there's
something different about the execution environment under cron, but it's
pretty hard to see how that would allow pg_dumpall to get as far as the
log shows and then crash.  For instance, if you had a LD_LIBRARY_PATH
setting in your manual environment that wasn't shared by cron, that
could lead to pg_dumpall picking up the wrong copy of libpq.so and then
crashing --- but you'd really expect such a failure to happen at or near
the start of execution.

What I'd try next is (1) adding an "env" command to the cron shell
script so you can compare that environment to your regular shell,
and (2) adding "ulimit -c unlimited" to the shell script in hopes
of collecting a core dump you could get a stack trace from.

            regards, tom lane


Re: unexpected EOF on client connection during pg_dumpall

From
Kevin Grittner
Date:
"Arnold, Sandra L." <arnoldsl@ornl.gov> wrote:

> I am currently getting a “unexpected EOF on client connection”
> when running pg_dumpall from a bash shell script in cron.  I have
> looked online to see if anyone else is having or have had this
> problem without any luck.  I am hoping someone can point me in
> the right direction to determine what is happening.  When I run
> the command manually it works.

> Please let me know if I need to provide any other information.

What does pg_dumpall write to stdout and stderr?  I have only seen
the server-side error you show when the pg_dumpall client had an
error writing, due to permissions problems or disk space
exhaustion, and the reason needs to be found on the "client" side,
not in the server log.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: unexpected EOF on client connection during pg_dumpall

From
"Arnold, Sandra L."
Date:
It does not write anything to stdout and stderr.  The only thing I see is the error in the server's csv file.  However,
Igot this to work by adding the following to the command line "su - postgres -c " and having the pg_dumpall command in
doublequotes.  I was trying to run this as root but for some reason it did not like it.
 

Sandra Arnold
Database/Data Warehouse Administrator
MartinFederal Consulting
Consultant to Oak Ridge National Laboratory
Computational Data Analytics Group
Data Architectures Team

-----Original Message-----
From: Kevin Grittner [mailto:kgrittn@ymail.com] 
Sent: Thursday, August 01, 2013 2:24 PM
To: Arnold, Sandra L.; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] unexpected EOF on client connection during pg_dumpall

"Arnold, Sandra L." <arnoldsl@ornl.gov> wrote:

> I am currently getting a “unexpected EOF on client connection”
> when running pg_dumpall from a bash shell script in cron.  I have 
> looked online to see if anyone else is having or have had this problem 
> without any luck.  I am hoping someone can point me in the right 
> direction to determine what is happening.  When I run the command 
> manually it works.

> Please let me know if I need to provide any other information.

What does pg_dumpall write to stdout and stderr?  I have only seen the server-side error you show when the pg_dumpall
clienthad an error writing, due to permissions problems or disk space exhaustion, and the reason needs to be found on
the"client" side, not in the server log.
 

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: unexpected EOF on client connection during pg_dumpall

From
"Arnold, Sandra L."
Date:
Ok.  I thought I had fixed this problem but now it is back.  I separated my PostgreSQL backup job from my MySQL backup
joband am now executing it as follows:
 

su - postgres -c "/app/PostgreSQL/9.1/bin/pg_dumpall -h hostname -p 5432 -f
/DBbackups/postgres/postgres.bkp_$backupdate-U backup"
 

backupdate is being set prior to executing the command to the current date and time.

It is working for a while and does backup some of the database until I get the following:

2013-08-02 14:04:38:426 EDT,"backup","dbname",21315,"hostname:40356",51fbf3a2.5343.401,"idle in
transaction","2013-08-0214:00:02 EDT,3/299,0,LOG,08P01,"unexpected EOF on client connection",,,,,,,,,"pg_dump"
 
2013-08-02 14:04:38.426 EDT,"backup","dbname",21315,"hostname:40356",51fbf3a2.5343,402,"idle in
transaction","2013-08-0214:00:02 EDT,,0,LOG,00000,"disconnection:  session time:  0:04:36.044 user=backup
database=dbnamehost=hostname port=40356",,,,,,,,,"pg_dump"
 
2013-08-02 14:04:40.981 EDT,"backup","postgres",21309,"hostname:40353",51fbf3a1.533d,47,"idle",2013-08-02 14:00:01
EDT,2/0,0,LOG,08P01,"unexpectedEOF on client connection",,,,,,,,,"pg_dumpall"
 
2013-08-02 14:04:40.981 EDT,"backup","postgres",21309"hostname:40353",51fbf3a1.533d,48,"idle",2013-08-02 14:00:01
EDT,,0,LOG,00000,"disconnection:session time: 0:04:39.039 user=backup database=postgres host=hostname
port=40353",,,,,,,,,"pg_dumpall"

I have changed the hostname and the database name that was not the postgres database for security reasons.

It looks like it completed the COPY statement for the table that it was backing up right before the error message
occurred. I also do not see anything in the backup file that indicates what is happening.
 

The Postgresql version is 9.1.9 and is running on CENTOS 5.9.

Any help is appreciated.

Thanks,

Sandra Arnold
Database/Data Warehouse Administrator
MartinFederal Consulting
Consultant to Oak Ridge National Laboratory
Computational Data Analytics Group
Data Architectures Team


-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Arnold, Sandra L.
Sent: Thursday, August 01, 2013 2:29 PM
To: Kevin Grittner; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] unexpected EOF on client connection during pg_dumpall

It does not write anything to stdout and stderr.  The only thing I see is the error in the server's csv file.  However,
Igot this to work by adding the following to the command line "su - postgres -c " and having the pg_dumpall command in
doublequotes.  I was trying to run this as root but for some reason it did not like it.
 

Sandra Arnold
Database/Data Warehouse Administrator
MartinFederal Consulting
Consultant to Oak Ridge National Laboratory Computational Data Analytics Group Data Architectures Team

-----Original Message-----
From: Kevin Grittner [mailto:kgrittn@ymail.com]
Sent: Thursday, August 01, 2013 2:24 PM
To: Arnold, Sandra L.; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] unexpected EOF on client connection during pg_dumpall
"Arnold, Sandra L." <arnoldsl@ornl.gov> wrote:

> I am currently getting a “unexpected EOF on client connection”
> when running pg_dumpall from a bash shell script in cron.  I have 
> looked online to see if anyone else is having or have had this problem 
> without any luck.  I am hoping someone can point me in the right 
> direction to determine what is happening.  When I run the command 
> manually it works.

> Please let me know if I need to provide any other information.

What does pg_dumpall write to stdout and stderr?  I have only seen the server-side error you show when the pg_dumpall
clienthad an error writing, due to permissions problems or disk space exhaustion, and the reason needs to be found on
the"client" side, not in the server log.
 

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: unexpected EOF on client connection during pg_dumpall

From
bricklen
Date:
On Fri, Aug 2, 2013 at 11:33 AM, Arnold, Sandra L. <arnoldsl@ornl.gov> wrote:
2013-08-02 14:04:40.981 EDT,"backup","postgres",21309"hostname:40353",51fbf3a1.533d,48,"idle",2013-08-02 14:00:01 EDT,,0,LOG,00000,"disconnection: session time: 0:04:39.039 user=backup database=postgres host=hostname port=40353",,,,,,,,,"pg_dumpall"

It looks like it completed the COPY statement for the table that it was backing up right before the error message occurred.  I also do not see anything in the backup file that indicates what is happening.

Is the entire process occurring on the same host, or is across a network? If the latter, could it be due to timeouts from ssh, or tunnels, or internal load balancers/connection poolers?

Re: unexpected EOF on client connection during pg_dumpall

From
"Arnold, Sandra L."
Date:

The script is running on the same server as the database.  However I am using hostname to connect to the database.  Could I be having problems with tcp_keepalives parameter settings?  These are currently set to use the OS defaults which would be CentOS settings. 

 

Sandra Arnold

Database/Data Warehouse Administrator

MartinFederal Consulting

Consultant to Oak Ridge National Laboratory

Computational Data Analytics Group

Data Architectures Team

 

From: bricklen [mailto:bricklen@gmail.com]
Sent: Friday, August 02, 2013 4:57 PM
To: Arnold, Sandra L.
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] unexpected EOF on client connection during pg_dumpall

 

On Fri, Aug 2, 2013 at 11:33 AM, Arnold, Sandra L. <arnoldsl@ornl.gov> wrote:

2013-08-02 14:04:40.981 EDT,"backup","postgres",21309"hostname:40353",51fbf3a1.533d,48,"idle",2013-08-02 14:00:01 EDT,,0,LOG,00000,"disconnection: session time: 0:04:39.039 user=backup database=postgres host=hostname port=40353",,,,,,,,,"pg_dumpall"

It looks like it completed the COPY statement for the table that it was backing up right before the error message occurred.  I also do not see anything in the backup file that indicates what is happening.

 

Is the entire process occurring on the same host, or is across a network? If the latter, could it be due to timeouts from ssh, or tunnels, or internal load balancers/connection poolers?

Re: unexpected EOF on client connection during pg_dumpall

From
Robert Burgholzer
Date:
FWIW - if you are looking at a backup approach with the dumpall, I have found that using a rsync to just mirror the file system is waaaaaaaay faster, and hence less prone to interuption.  This is how it is recommended with a warm standby development, and works great, and also is far less consumptive of system resources. The big limitation is that if you are duplicating for an upgrade, it might be problematic since the file system dupes are PG Version specific I think.

regards,
r.b.