Error: constraint does not exist when restoring a backup on a fresh db

[MickaelBergem]

1. Backed up the database
2. Restored the database from the backup

=> Works fine :white_check_mark:

1. Back up the database
2. Wipe out the database
3. Do the initial django migrate
4. Restore the same backup as in the previous case

=> I get the following error :x:

ERROR:  constraint "socialaccount_socialapp_site_site_id_2579dee5_fk_django_site_id" of relation "socialaccount_socialapp_sites" does not exist

I guess the issue comes from constraint name not being constant (2579dee5 par above).

Why not use IF NOT EXIST (https://www.postgresql.org/docs/9.0/static/sql-altertable.html) ? Or simply wipe out the whole database before importing and remove all the DROP commands?

I guess I miss some historical data (reasons behind decisions), but for now I have a hard time figuring how to restore this dump without manual edit of the relevant lines.

[MickaelBergem]

I solved my issue by removing all the DROP lines, and replacing DROP SCHEMA public by

DROP SCHEMA IF EXISTS public CASCADE

but being able to load dumps in this case would be super cool :D

[ZuluPro]

Hello @MickaelBergem Thanks for this issue and the proposition.

We encountered this problem several time with PostgreSQL and the DROP statements. As solution, We plan to make Postgres binary (pg_dump/pg_restore) the default backup tool.

There's a PR about that here: #241 Does it answer to your problem ?

[MickaelBergem]

Thanks for the quick reply @ZuluPro I'll try to test it next week :)

[mikeschaekermann]

I also encounter this problem when I create a backup from my staging database and try to restore it into my local development database. Is there a recommended work-around for this problem?

Thanks!

[MickaelBergem]

@ZuluPro I just tried it with your branch:

$ pip install git+https://github.com/ZuluPro/django-dbbackup#pgrestore
$ python manage.py dbrestore -I backup.psql.gz -z
...
dbbackup.db.exceptions.CommandConnectorError: Error running:  psql ******* --host=postgres --port=**** --username=******  --set ON_ERROR_STOP=on --single-transaction                                
b'ERROR:  constraint "socialaccount_socialapp_site_site_id_2579dee5_fk_django_site_id" of relation "socialaccount_socialapp_sites" does not exist\n' 

It looks like this does not solve my problem :/ I am restoring against an existing database (ran migrate once + added a couple of fixtures) with a backup from a production server.

[ZuluPro]

@MickaelBergem thanks for you tests I see you are using the old connector, this PR comes with the new Postgres Binary one which uses pgrestore instead of psql. Could you test with this one ? Otherwise outside of dbbackup, what would be your workaround for this issue ?

[MickaelBergem]

@ZuluPro I'm kind of confused here, how do I use the new connector? Doesn't the pip install take everything needed from your branch?

For now, the manual steps I take to restore a dump from another host:

1. Remove all the lines that drop constraints, indexes, tables.
2. Just keep the DROP SCHEMA line and append CASCADE at the end.

That's all folks.

[ZuluPro]

@MickaelBergem, You must configure DBBACKUP_CONNECTORS with dbbackup.db.postgres.PgDumpBinaryConnector.

Something like:

DBBACKUP_CONNECTORS = {
    'default': {
        'USER': 'backupuser',
        'PASSWORD': 'backuppassword',
        'HOST': 'backuphost',
        'CONNECTOR': 'dbbackup.db.postgres.PgDumpBinaryConnector',
    }
}

[mikeschaekermann]

I tried to use the new connector both with django-dbbackup=3.1.3 and the pgrestore fork.

However, I got an error No module named postgres even after manually installing it with pip install postgres.

Full error message:

root@a468a0f5530f:/code# ./manage.py dbbackup
/usr/local/lib/python2.7/site-packages/django/core/management/base.py:265: RemovedInDjango110Warning: OptionParser usage for Django management commands is deprecated, use ArgumentParser instead
  RemovedInDjango110Warning)

ImportError: No module named postgres
  File "/usr/local/lib/python2.7/site-packages/dbbackup/utils.py", line 111, in wrapper
    func(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/dbbackup/management/commands/dbbackup.py", line 58, in handle
    self.connector = get_connector(database_key)
  File "/usr/local/lib/python2.7/site-packages/dbbackup/db/base.py", line 41, in get_connector
    module = import_module(connector_module_path)
  File "/usr/local/lib/python2.7/importlib/__init__.py", line 37, in import_module
    __import__(name)

Traceback (most recent call last):
  File "./manage.py", line 12, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 345, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 348, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python2.7/site-packages/django/core/management/base.py", line 399, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/site-packages/dbbackup/utils.py", line 111, in wrapper
    func(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/dbbackup/management/commands/dbbackup.py", line 58, in handle
    self.connector = get_connector(database_key)
  File "/usr/local/lib/python2.7/site-packages/dbbackup/db/base.py", line 41, in get_connector
    module = import_module(connector_module_path)
  File "/usr/local/lib/python2.7/importlib/__init__.py", line 37, in import_module
    __import__(name)
ImportError: No module named postgres

Any ideas @ZuluPro ?

[ZuluPro]

@mikeschaekermann Did you set any conf ? If you are in dev env, Could you try to delete *.pyc files ?

[mikeschaekermann]

I set the following in settings.py with the user, password and host of my default database:

DBBACKUP_CONNECTORS = {
    'default': {
        'USER': 'backupuser',
        'PASSWORD': 'backuppassword',
        'HOST': 'backuphost',
        'CONNECTOR': 'dbbackup.db.postgres.PgDumpBinaryConnector',
    }
}

In which directory should I delete *.pyc files? I tried to delete them in the directory with settings.py in it, but that didn't change anything about the error message.

[thimma11]

So is it already possible to restore a backup on a fresh database and how would I do it? Do I just need to add the Connector in settings.py or is it also necessary to add parameters to the restore command? I'm kind of confused by this thread :/ I hope you can help me :)

[MaZZly]

The problem with ImportError: No module named postgres Is due to a typo in the connector settings..

'CONNECTOR': 'dbbackup.db.postgres.PgDumpBinaryConnector', should be: 'CONNECTOR': 'dbbackup.db.postgresql.PgDumpBinaryConnector',

@mikeschaekermann

@thimma11 you should just install dbbackup==3.2.0 and then set the connector settings as above, then dbbackup/dbrestore should work.

[zxwild]

All settings specified above doesn't work for my installation, so I suppose the built-in dbrestore doesn't work at all for postgres. (Django==1.11.9, django-dbbackup==3.2.0)

But I was able to restore database by psql directly, actually it's not so hard.

su postgres
psql -d dbname -f ./backup-file.psql

Media restore works ok, so I'm satisfied :)

[MaZZly]

@zxwild Yeah I've found that to make the initial restoration, I can't use the dbrestore command, but using the psql command works fine

[ZuluPro]

Hello all, Could you check your backup size ? And check if it could be contained in your tmp directory?

[zxwild]

@ZuluPro it was an initial database with about 30 records Size was about 1 Mb, a new installation of debian 9, 500Gb HDD / 2Gb RAM.

[MaZZly]

Yep same here.. Think it was something like 1.6Mb on a fresh site where I wanted to replicate some data from prod to dev.

[bartmika]

Major +1

[rodolfomartinez]

+1

[iosifnicolae2]

+1

[jonathan-s]

+1

[abdhx]

still relevant after 3 years...

[eeintech]

Still relevant but @zxwild's workaround works just fine :smile:

[moshfrid]

+1

[TheAbhijeet]

Still relevant for Django 3.2 and Python 3.10

Unfortunetly 'dbbackup.db.postgresql.PgDumpBinaryConnector' did not make any difference.

[TheAbhijeet]

As stated before the media restore works flawlessly but in case you are using dockerized Postgres and wanna migrate/copy/clone database following commands can help.

This will create a SQL file with data only

docker exec -t postgres_container pg_dumpall --data-only -U postgres > prod_dump_clean.sql

Before restoring you must run the migrations on the new host so that the new database has the tables in place before restoring.

cat prod_dump_clean.sql | docker exec -i postgres_container psql -U postgres 

[skyl]

I reckon I may have just hit this with Django 3.2 and python 3.10 and django-dbbackup==4.0.2. But, I haven't tried any DBBACKUP_CONNECTORS. It seems in my case I will work around by running migrate on an empty DB to create the schema and then restore works fine.

[adamKenneweg]

have the same thing, have to run it twice

[hkhanna]

+1

[sergeydubak]

Solved this by adding to settings.py:

DBBACKUP_CONNECTORS = {
    'default': {
        'RESTORE_SUFFIX': '--if-exists',
    },
}

[Archmonger]

@sergeydubak Are you interested in either documenting this, or PRing this behavior as the default for the postgres connector?

[Letme]

@sergeydubak you saved my ass after 1 week of trying to figure out how to solve this (to run migrations, to use the postgres command, etc). This is great tip, and completely easy to add to the settings. I hope you can open a PR for at least the documentation upgrade, although I have a feeling this could have just been the default option.