Re: pg_restore fails to restore sequences

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.

Could you try extracting plain-text output from the dump file, ie

pg_restore -Cv ./census.backup >census.txt

and then having a look at what's in the output file in and after the
above-quoted CREATE SEQUENCE command?

regards, tom lane