pg_restore fails with drop default on identity column

Question

Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below:

pg_restore --verbose --format=custom --dbname=bosh --clean --if-exists --single-transaction --exit-on-error

pg_restore: dropping TABLE DATA cpi_configs
pg_restore: dropping TABLE DATA configs
pg_restore: dropping TABLE DATA compiled_packages
pg_restore: dropping TABLE DATA cloud_configs
pg_restore: dropping TABLE DATA blobs
pg_restore: dropping TABLE DATA agent_dns_versions
pg_restore: dropping DEFAULT vms id
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2493; 2604 16496 DEFAULT vms id postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column "id" of relation "vms" is an identity column
HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.
    Command was: ALTER TABLE IF EXISTS public.vms ALTER COLUMN id DROP DEFAULT;

I'm running this as part of an automation utility where it's not trivial to change the command. Best I can think of to workaround this is to use -C to drop/create the database with pg_restore, but is this a bug to file or something we can do differently with pg_dump?

Answer 1

That looks like an unfortunate coincidence of --clean and an existing table.

The dump contains a column default definition, so --clean tries to remove the default before restoring it. This does not work for identity columns.

It would be interesting to know how the corresponding column in the database you dumped is defined. Does it have a DEFAULT value?

One could argue that “dropping the default” should include getting rid of identity columns; if you feel that way, you could take up that complaint with the PostgreSQL hackers mailing list. It should be possible to modify pg_restore accordingly, but I wonder if it is worth the effort.

Answer 2

I'm seeing this for the following, which aligns with the original post:

1. Install postgres 9.4, create tables with SEQUENCEs for id columns
2. Upgrade to postgres 10.4, run pg_upgrade
3. pg_dump and pg_restore against 10.4 is okay
4. Upgrade to postgres 10.9, run pg_upgrade successfully
5. pg_dump and pg_restore as in original post. Same kinds of errors.

In fact, the database in question is the https://github.com/cloudfoundry/bosh database. Users of bosh are seeing this problem when following the above upgrade path. It would also seem that from OP the problem starts in 10.6.