Re: pg_restore creates duplicate records when used with --role parameter

=?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