Re: pg_restore fails to restore sequences

On 28 September 2015 at 21:47, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Spencer Gardner <spencergardner@gmail.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