Re: failure with pg_dump

On Tue, 2005-03-22 at 10:36, Vivek Khera wrote:
> This morning I came in to discover that my nightly pg_dump backup to a
> remote server had failed.  Both the server and the client are have
> postgres 8.0.1 installed.  Figuring it was just a fluke I ran the dump
> again by hand and got the same error:
>
> % pg_dump -h d01-prv -Fc -f mm.22-Mar-2005.dump vkmlm
> pg_dump: socket not open
> pg_dump: SQL command to dump the contents of table "user_list" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: socket not open
> pg_dump: The command was: COPY public.user_list ( ... list elided for
> privacy concerns ... )
>
> the postgres log on the server has this to say:
>
> Mar 22 10:35:00 d01 postgres[35190]: [8-1] LOG:  could not receive data
> from client: Operation timed out
> Mar 22 10:35:00 d01 postgres[35190]: [9-1] LOG:  unexpected EOF on
> client connection
>
>
> the overnight dump had this error:
>
> pg_dump: socket not open
> pg_dump: SQL command to dump the contents of table "user_list" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: socket not open
> pg_dump: The command was: COPY public.user_list ( ... )
>
>
> Mar 22 03:42:10 d01 postgres[33589]: [6-1] LOG:  could not send data to
> client: Broken pipe
> Mar 22 03:47:10 d01 postgres[33589]: [7-1] LOG:  duration: 473083.960
> ms  statement:
>   [ the COPY query ]
> Mar 22 03:47:10 d01 postgres[33589]: [8-1] LOG:  could not receive data
> from client: Operation timed out
> Mar 22 03:47:10 d01 postgres[33589]: [9-1] LOG:  unexpected EOF on
> client connection
>
>
> While poking through the logs for these errors, I'm finding a *lot* of
> broken pipe/unexpected EOF errors for this server but for connections
> from other hosts as well, running reports.  those hosts still have the
> 7.4 client libraries.
>
> This is a brand new box (rushed into production after minimal testing
> since one other died, so I'm not 100% certain it is stable) running
> FreeBSD 5.4-PRERELEASE amd64 on a dual Opteron with 4GB ram and
> MegaRAID RAID.
>
> The same config on the old box with Pg 7.4.7 worked flawlessly for
> running reports and dumps.  Another issue is that the 8.0 server is
> noticeably slower than the 7.4 with identically (translated to 8.0
> style) configs.

IS there a difference in the infrastructure for this server?  Like
firewalls and routing?

Also, is it vacuum / analyzed often?  Poor stats will cause the server
to run slower.

Are you getting any output from the postgresql logs that would point to
backends dieing or anything like that?  This sounds like a networking /
client problem to me.