Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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.klaver@xxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux