IntegrityError after table restore from backup, PostgreSQL + Django

Question

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` just prints the commands. It doesn't run them. You have to run the commands. — Alasdair, May 02 '18 at 15:35
Since you're using pgAdmin you could use that, or simply use `manage.py dbshell`. — Alasdair, May 02 '18 at 15:37
ah, sorry, I am quite new here, and don't know exactly how to use that features :) will try your recipe, thank you — Chiefir, May 02 '18 at 15:39

score 1 · Accepted Answer · answered May 02 '18 at 16:10

1

sqlsequencereset only prints the commands, it doesn't run them.

You have to run the commands, using pgAdmin or manage.py dbshell.

answered May 02 '18 at 16:10

Alasdair

298,606
55
578
516

Paolo Melchiorre · Answer 2 · 2018-05-12T09:10:46.313

As suggested by "Alasdair" you can get the commands to solve your problem running sqlsequencereset.

or

You can execute the SQL query generated by sqlsequencereset from within python in this way (using the default database):

from django.core.management.color import no_style
from django.db import connection

from myapps.models import MyModel1, MyModel2


sequence_sql = connection.ops.sequence_reset_sql(no_style(), [MyModel1, MyModel2])
with connection.cursor() as cursor:
    for sql in sequence_sql:
        cursor.execute(sql)

I tested this code with Python3.6, Django 2.0 and PostgreSQL 10.

IntegrityError after table restore from backup, PostgreSQL + Django

2 Answers2