Обсуждение: pg_restore creates duplicate records when used with --roleparameter

=?utf-8?Q?Ivan_Panti=C4=87?= <ivanpantic82@gmail.com> writes:
> I've created a backup using pg_restore like this:
> x sudo -u postgres pg_dump source_db --format=custom' > /tmp/source_db.db
> I then copied the backup file to the destination server and tried to restore it like this:
> sudo -u postgres psql destination_db -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public AUTHORIZATION
destination_user;'
> sudo -u postgres pg_restore -d destination_db --no-owner --role=destination_user -n public /tmp/source_db.db

> I've recieved this error:
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 2276; 2606 42862 CONSTRAINT price_history price_history_pkey
destination_db
> pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index "price_history_pkey"
> DETAIL: Key (id)=(64) is duplicated.
> Command was: ALTER TABLE ONLY public.price_history
> ADD CONSTRAINT price_history_pkey PRIMARY KEY (id);

FWIW, I could not duplicate this report in several tries.

Usually, the expectation when restoring a dump is that you're restoring
into a totally empty database.  Clearing out just the public schema
isn't that, so I wonder if you left behind something that could affect
this table.  It's not very clear what that could be though :-(.

Anyway, if you can provide a self-contained reproduction case for this,
we'd surely look further.

            regards, tom lane

Ivan Pantić <ivanpantic82@gmail.com> writes:

I've created a backup using pg_restore like this:

x sudo -u postgres pg_dump source_db --format=custom' > /tmp/source_db.db

I then copied the backup file to the destination server and tried to restore it like this:

sudo -u postgres psql destination_db -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public AUTHORIZATION destination_user;'

sudo -u postgres pg_restore -d destination_db --no-owner --role=destination_user -n public /tmp/source_db.db

I've recieved this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 2276; 2606 42862 CONSTRAINT price_history price_history_pkey destination_db

pg_restore: [archiver (db)] could not execute query: ERROR: could not create unique index "price_history_pkey"

DETAIL: Key (id)=(64) is duplicated.

Command was: ALTER TABLE ONLY public.price_history

ADD CONSTRAINT price_history_pkey PRIMARY KEY (id);

FWIW, I could not duplicate this report in several tries.

Usually, the expectation when restoring a dump is that you're restoring

into a totally empty database. Clearing out just the public schema

isn't that, so I wonder if you left behind something that could affect

this table. It's not very clear what that could be though :-(.

Anyway, if you can provide a self-contained reproduction case for this,

we'd surely look further.

regards, tom lane