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

Hi tom,

After playing with this a bit more, it seems something was wrong with a particular database I was trying to restore into.
When I created a fresh database, the restore worked, including after deleting the public schema and trying again. When I did a db dump of new and old database, the outputs were the same, nothing special in the old DB DDL. After deleting destination_db and creating it from scratch, the restore started working so it's not like its name is cursed or something.
Overall, it seems there was some kind corruption of the old database that causes this restore to fail.
Very strange and, I suspect, impossible to debug (especially given I no longer have the old database).
Thank you for your help.
Regards,
Panta

On Oct 18 2019, at 11:13 am, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> =?utf-8?Q?Ivan_Panti=C4=87?= <ivanpantic82(at)gmail(dot)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