Обсуждение: unexpected EOF on client connection during pg_dumpall
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
"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
"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
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
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
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.
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?