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.

|improve this answer|||||
0

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.

|improve this answer|||||
  • you don't need to run pg_upgrade for an upgrade from 10.4 to 10.9 in fact you don't need to do anything except for restarting the service with a minor version upgrade – a_horse_with_no_name Dec 5 '19 at 21:35
  • @a_horse_with_no_name I agree. This issue occurs anyways, which is likely less to do with pg_upgrade being run. We run pg_upgrade automatically and expect a no-op/non-destructive action or an upgrade. – h4xnoodle Dec 7 '19 at 5:32

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.