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@sss.pgh.pa.us> wrote:
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