I am trying to restore a table in my database from some db backups. The backup is created via pgAdmin 4 UI tool, select a table and next backup it. And here is the command that pgAdmin executes:
--file "C:\\Users\\cchie\\DOCUME~1\\DB_BAC~1\\CURREN~1" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --table "public.cosmetic_crawler_currency" "last_cosmetics"
The problem is that I have an AutoField in that table and when I restore a table, and next try to add new items into it - I have errors like this:
psycopg2.IntegrityError: duplicate key value violates unique constraint "cosmetic_crawler_product_pkey"
DETAIL: Key (product_id)=(27) already exists.
The above exception was the direct cause of the following exception:
django.db.utils.IntegrityError: duplicate key value violates unique constraint "cosmetic_crawler_product_pkey"
DETAIL: Key (product_id)=(27) already exists.
As I understand - that means that PostgreSQL server does not know that I have restored the AutoField counter and tries to count from 0? Is there a way to fix that and make it count from the last number?
I was told that I have to use this recipe. But when I run manage.py sqlsequencereset my_app_name - I see that that command runs:
BEGIN;
SELECT setval(pg_get_serial_sequence('"cosmetic_crawler_product"','product_id'), coalesce(max("product_id"), 1), max("product_id") IS NOT null) FROM "cosmetic_crawler_product";
SELECT setval(pg_get_serial_sequence('"cosmetic_crawler_sale"','sale_id'), coalesce(max("sale_id"), 1), max("sale_id") IS NOT null) FROM "cosmetic_crawler_sale";
SELECT setval(pg_get_serial_sequence('"cosmetic_crawler_promo"','promo_id'), coalesce(max("promo_id"), 1), max("promo_id") IS NOT null) FROM "cosmetic_crawler_promo";
SELECT setval(pg_get_serial_sequence('"cosmetic_crawler_brand_shops"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "cosmetic_crawler_brand_shops";
SELECT setval(pg_get_serial_sequence('"cosmetic_crawler_brand_categories"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "cosmetic_crawler_brand_categories";
SELECT setval(pg_get_serial_sequence('"cosmetic_crawler_brand"','brand_id'), coalesce(max("brand_id"), 1), max("brand_id") IS NOT null) FROM "cosmetic_crawler_brand";
COMMIT;
But that still has no effect - when I add new items - PostgreSQL keeps counting from 0 (more precise - from last number where it failed with IntegrityError earlier).
P.S. Restoring process is the same as backuping - manually in pgAdmin UI.
sqlsequencereset only prints the commands, it doesn't run them.
You have to run the commands, using pgAdmin or manage.py dbshell.