Postgresql – pg_restore fails with drop default on identity column

identitypg-restorepostgresql

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?

Best Answer

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.

Related Solutions

Sql-server – Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

I have found a solution though adding:

@identityrangemanagementoption = N'manual'

when I re-add the article using sp_addarticle, means that the automatically created sps used to replicate the data don't try and update the identity column and thus are created succesfully on the subscriber rather than raising the error.

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

--clean Clean (drop) database objects before recreating them

and

--create Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

--clean Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

--create Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.

Best Answer

Related Solutions

Sql-server – Dropping then adding a table to a subscription with Identity option Not for Replication causes errors

PostgreSQL – pg_restore Error: Schema ‘public’ Already Exists

Related Question