Re: pg_restore fails to restore sequences

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.

On Mon, Sep 28, 2015 at 4:08 PM, Thom Brown <thom(at)linux(dot)com> wrote:

> On 28 September 2015 at 21:47, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Spencer Gardner <spencergardner(at)gmail(dot)com> writes:
> >> I'm transferring all of the databases on my old postgres server to a new
> >> server. To do this I'm using pg_dump and then pg_restore:
> >
> >> pg_dump --host localhost --port 5432 --username "postgres" --format
> custom
> >> --blobs --file ~/backups/census.backup census
> >> --and then--
> >> pg_restore -Cv -h localhost -p 5432 -U postgres -d postgres
> ./census.backup
> >
> >> The pg_restore gives me a series of errors about sequences not existing.
> >> The database is restored with all data intact, but the sequences are not
> >> recreated.
> >
> >> [ apparently due to ]
> >
> >> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> >> pg_restore: [archiver (db)] Error from TOC entry 193; 1259 27415
> SEQUENCE
> >> block_pop_hu_2010_pk_uid_seq gis
> >> pg_restore: [archiver (db)] could not execute query: ERROR: syntax
> error
> >> at or near "USING"
> >> LINE 7: USING local;
> >> ^
> >> Command was: CREATE SEQUENCE block_pop_hu_2010_pk_uid_seq
> >> START WITH 1
> >> INCREMENT BY 1
> >> NO MINVALUE
> >> NO MAXVALUE
> >> CACHE 1...
> >
> > Seemingly, it's failing to recreate the sequences because of a syntax
> > problem, but I do not see how a clause involving USING could have got
> > into the CREATE SEQUENCE command.
>
> Yes, this weirdly looks like it's been built with support for the
> as-yet-incomplete sequence AM, which supports "USING local". I don't
> suppose this was dumped from a custom build to work with BDR?
>
> Thom
>