Re: pg_restore: [custom archiver] unexpected end of file on Postgres 9.1.1

Ok, I think I've narrowed down the problem. Doing a pg_dump with --verbose
and watching it myself (it was in a cron before), I now see:

pg_dump: dumping contents of table external_users
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: canceling statement due to
conflict with recovery
DETAIL: User query might have needed to see row versions that must be
removed.
pg_dump: The command was: COPY public.external_users (id, user_id,
external_id, type) TO stdout;
pg_dump: *** aborted because of error

The pg_dump is being run from a slave set on hot-standby mode. By looking
around this appears to be a fairly common issue with streaming replication.

I have found references to this manual page:

http://www.postgresql.org/docs/9.0/static/hot-standby.html

In my case "external_users" is a pretty "hot" table, so I think it
satisfies this note: "Users should be clear that tables that are regularly
and heavily updated on the primary server will quickly cause cancellation
of longer running queries on the standby"

In my case I have:

max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s

I dont know if adjusting one of the above parameters would help. From the
docs it sounds that increasing "vacuum_defer_cleanup_age" to some larger
value might also do the trick.

Any guidance would be appreciated.

/Cody

On Mon, Nov 7, 2011 at 4:11 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On Monday, November 07, 2011 11:27:05 am Cody Caughlan wrote:
> > I am trying to restore a dump created with pg_dump, both source and
> > destination are Postgres 9.1.1 albeit different machines (source is
> Linux,
> > destination is OS X).
> >
> > $ pg_restore -U postgres -Fc -d batch_api_production
> > 2011110708_batch_api_production.dump.sql
> > pg_restore: [custom archiver] unexpected end of file
> >
> > pg_restore does seem like its doing something, as it doesnt error out
> until
> > 10-12 seconds in.
> >
> > I can dump the TOC just fine with pg_restore -l, which I've collected
> here:
> >
> > https://gist.github.com/951e417e7098fdf987d4
> >
> > If I access the DB it appears that all the tables and sequences exist,
> but
> > none of the data or indexes & constraints.
> >
> > Any help would be appreciated.
>
> What do the database logs show when you do the restore?
>
> >
> > /Cody Caughlan
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>