0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.