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:
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@xxxxxxxxx> wrote:
What do the database logs show when you do the restore?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.
>
> /Cody Caughlan
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
