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

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.klaver@gmail.com

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.klaver@gmail.com>wrote: