Thread: pg_restore fails to restore sequences

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.

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

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

On 28 September 2015 at 22:21, Spencer Gardner <spencergardner@gmail.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.

Thom

Thom Brown <thom@linux.com> writes:
> On 28 September 2015 at 22:21, Spencer Gardner <spencergardner@gmail.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.

            regards, tom lane

Tom Lane wrote:
> Thom Brown <thom@linux.com> writes:
> > On 28 September 2015 at 22:21, Spencer Gardner <spencergardner@gmail.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@2ndquadrant.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