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