Re: pg_restore fails to restore sequences

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