Re: pg_restore fails to restore sequences

I believe our BDR build was from before May so that further explains the
issue. Sounds like this will not be a problem in the future. Thanks for the
help.

On Mon, Sep 28, 2015 at 4:49 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Tom Lane wrote:
> > Thom Brown <thom(at)linux(dot)com> writes:
> > > On 28 September 2015 at 22:21, Spencer Gardner <
> spencergardner(at)gmail(dot)com> wrote:
> > >> Actually, yes. That's the reason for backing up. We had been playing
> with
> > >> BDR on a custom build but have reverted to the stock Ubuntu build for
> the
> > >> time being. So it sounds like the issue is caused by dumping from our
> custom
> > >> BDR build. It's not really a big issue - I've already rebuilt the
> affected
> > >> sequences.
> >
> > > Have you tried dumping the database using the stock pg_dump
> > > executable? The BDR branch isn't compatible with regular PostgreSQL,
> > > at least not yet.
> >
> > Seems like it would be a good idea if BDR's pg_dump were to suppress
> > "USING local" clauses, and only output USING if it's not default, so as
> > not to create gratuitous incompatibilities like this one.
>
> Looking at the BDR commit history, it has been doing that since May.
>
> commit 1592812131d84de56ba258c333f936e5e19647e2
> Author: Craig Ringer <craig(at)2ndquadrant(dot)com>
> AuthorDate: Tue May 26 10:18:10 2015 +0800
> CommitDate: Tue May 26 10:22:56 2015 +0800
>
> Only dump non-default sequence access methods
>
> To prevent issues with UDR and with restoring BDR dumps to non-BDR
> databases, don't emit a USING clause unless the pg_seqam catalog is
> present and the dumped sequence uses a non-default sequence access
> method.
>
> The dump should be restored with default_seqam = 'local' to ensure
> that local sequences aren't transformed into 'bdr' sequences during
> restore.
>
> --
> Álvaro Herrera http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>