Migrating from SQLite to Postgres issue

[tanjyeyee]

I run python manage.py dbbackup to dump my SQLite database.

Switch to Postgres database in settings.py, migrate to ensure schema is the same.

Run python manage.py dbrestore and I get this error:

Traceback (most recent call last):
  File "manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/home/zlappo/venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/home/zlappo/venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/zlappo/venv/lib/python3.7/site-packages/django/core/management/base.py", line 316, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/zlappo/venv/lib/python3.7/site-packages/django/core/management/base.py", line 353, in execute
    output = self.handle(*args, **options)
  File "/home/zlappo/venv/lib/python3.7/site-packages/dbbackup/management/commands/dbrestore.py", line 53, in handle
    self._restore_backup()
  File "/home/zlappo/venv/lib/python3.7/site-packages/dbbackup/management/commands/dbrestore.py", line 94, in _restore_backup
    self.connector.restore_dump(input_file)
  File "/home/zlappo/venv/lib/python3.7/site-packages/dbbackup/db/base.py", line 92, in restore_dump
    result = self._restore_dump(dump)
  File "/home/zlappo/venv/lib/python3.7/site-packages/dbbackup/db/postgresql.py", line 56, in _restore_dump
    stdout, stderr = self.run_command(cmd, stdin=dump, env=self.restore_env)
  File "/home/zlappo/venv/lib/python3.7/site-packages/dbbackup/db/postgresql.py", line 21, in run_command
    return super(PgDumpConnector, self).run_command(*args, **kwargs)
  File "/home/zlappo/venv/lib/python3.7/site-packages/dbbackup/db/base.py", line 151, in run_command
    "Error running: {}\n{}".format(command, stderr.read().decode('utf-8')))
dbbackup.db.exceptions.CommandConnectorError: Error running:  psql  --host=localhost --port=5432 --username=u_zlappo --no-password --set ON_ERROR_STOP=on --single-transaction zlappo

NOTICE:  relation "app_category" already exists, skipping
ERROR:  column "updated" is of type timestamp with time zone but expression is of type integer
LINE 1: ...-09 16:03:47.736210','2019-10-09 16:03:47.736249',18,'VdLHti...
                                                             ^
HINT:  You will need to rewrite or cast the expression.

Any tips?

[voiddragon]

You should be using django's dumpdata and loaddata instead which convert the data to a standard format

https://docs.djangoproject.com/en/3.0/ref/django-admin/#dumpdata

[jonathan-s]

Let me know @tanjyeyee if the issue isn't resolved.

[jedie]

Still exists with current v3.3.0: SQlite dump is not compatible with postgres importer :(

e.g.:

ERROR:  syntax error at or near "AUTOINCREMENT"
LINE 1: ...S "auth_group" ("id" integer NOT NULL PRIMARY KEY AUTOINCREM...

or:

NOTICE:  relation "auth_group" already exists, skipping
ERROR:  relation "auth_group_permissions_group_id_permission_id_0cd325b0_uniq" already exists

or:

ERROR:  invalid input syntax for integer: "add_logentry"
LINE 1: INSERT INTO "auth_permission" VALUES(1,1,'add_logentry','Can...
                                                 ^

[jonathan-s]

@jedie Did you try using the dumpdata approach that was described above?

Given that Sqlite and Postgres are two different databases I'm not surprised that the datadump is not compatible. Heck, the datadump between different versions of postgres can be incompatible. So the best approach is still probably going to be the datadump that voiddragon mentioned.

You might also be interested in trying pgloader -> https://pgloader.readthedocs.io/en/latest/index.html

Either way it'll never be the responsibility of dbbackup to fix this compatibility.

[jedie]

What's about a "plugin" in django-dbbackup to use pgloader as importer?

[Archmonger]

@jedie This issue would be resolved by the new project direction I've proposed in #423.