Sunday, 7 July 2019

django 56 migrate from sqlite3 to localhost postgres

#django settings

DATABASES = {
'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
    'postgresql': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'music',
        'USER': 'postgres',
        'PASSWORD': 'xxxxxx',
        'HOST': '127.0.0.1',
        'PORT': '',
    }
}

---------------------------------------
PS C:\Users\bob\django\project1> python manage.py migrate --noinput --run-syncdb --database=postgresql
Operations to perform:
  Synchronize unmigrated apps: corsheaders, crispy_forms, django_cleanup, django_filters, messages, rest_auth, rest_framework, staticfiles
  Apply all migrations: admin, auth, authtoken, contenttypes, music, sessions
Synchronizing apps without migrations:
  Creating tables…
    Running deferred SQL…
Running migrations:
  Applying contenttypes.0001_initial… OK
  Applying auth.0001_initial… OK
  Applying admin.0001_initial… OK
  Applying admin.0002_logentry_remove_auto_add… OK
  Applying admin.0003_logentry_add_action_flag_choices… OK
  Applying contenttypes.0002_remove_content_type_name… OK
  Applying auth.0002_alter_permission_name_max_length… OK
  Applying auth.0003_alter_user_email_max_length… OK
  Applying auth.0004_alter_user_username_opts… OK
  Applying auth.0005_alter_user_last_login_null… OK
  Applying auth.0006_require_contenttypes_0002… OK
  Applying auth.0007_alter_validators_add_error_messages… OK
  Applying auth.0008_alter_user_username_max_length… OK
  Applying auth.0009_alter_user_last_name_max_length… OK
  Applying auth.0010_alter_group_name_max_length… OK
  Applying auth.0011_update_proxy_permissions… OK
  Applying authtoken.0001_initial… OK
  Applying authtoken.0002_auto_20160226_1747… OK
  Applying music.0001_initial… OK
  Applying music.0002_song_is_favorite… OK
  Applying music.0003_auto_20190319_0700… OK
  Applying music.0004_album_date_posted… OK
  Applying music.0005_auto_20190402_2222… OK
  Applying music.0006_auto_20190417_0757… OK
  Applying sessions.0001_initial… OK

  PS C:\Users\bob\django\project1> python manage.py sqlflush --database=postgresql
BEGIN;
TRUNCATE "django_session", "music_song", "auth_user_user_permissions", "django_admin_log", "auth_permission", "music_album", "auth_group", "authtoken_token", "auth_user_groups", "django_content_type", "auth_user", "auth_group_permissions";
SELECT setval(pg_get_serial_sequence('"django_admin_log"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_permission"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_group"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_group_permissions"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_user"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_user_groups"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_user_user_permissions"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_content_type"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"music_album"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"music_song"','id'), 1, false);
COMMIT;

PS C:\Users\bob\django\project1> python manage.py dumpdata --all --natural-foreign --natural-primary --indent=4 > dbname
.json

--------------------------------------
#dbname.json

[
{
    "model": "contenttypes.contenttype",
    "fields": {
        "app_label": "admin",
        "model": "logentry"
    }
},
...
{
    "model": "sessions.session",
    "pk": "0ws5iaepo9kjkfcuqjrdd7gxd6cfkj3c",
    "fields": {
        "session_data": "ZWJiMWViOTFmMzM5ZTZhODBkODJiNDhmZjI0Y2VlNzMzMzc4NDMxZDp7Il9hdXRoX3VzZXJfaWQiOiIxIiwiX2F1dGhfdXNlcl9iYWNrZW5kIjoiZGphbmdvLmNvbnRyaWIuYXV0aC5iYWNrZW5kcy5Nb2RlbEJhY2tlbmQiLCJfYXV0aF91c2VyX2hhc2giOiI5ZjAxZTlkOTYzMDFkZGU2YTgzMjgxMTg2MjhlNzcyOTIyOTg4OTgyIn0=",
        "expire_date": "2019-04-06T00:27:27.744Z"
    }
},
...
{
    "model": "music.song",
    "pk": 19,
    "fields": {
        "album": 3,
        "file_type": "mp4",
        "song_title": "The Best You Had",
        "is_favorite": false
    }
},
---
{
    "model": "auth.permission",
    "fields": {
        "name": "Can add log entry",
        "content_type": [
            "admin",
            "logentry"
        ],
        "codename": "add_logentry"
    }
},
...
{
    "model": "auth.user",
    "fields": {
        "password": "pbkdf2_sha256$150000$S6IUNyhNk8mE$QN2pjok5Y8Wvnr9XOi5P5yDyVt0NzMSOJr4KZpUqZjA=",
        "last_login": "2019-07-06T16:12:33.786Z",
        "is_superuser": true,
        "username": "chuanshuo",
        "first_name": "",
        "last_name": "",
        "email": "chuanshuoge1@gmail.com",
        "is_staff": true,
        "is_active": true,
        "date_joined": "2019-03-03T01:45:00Z",
        "groups": [],
        "user_permissions": []
    }
},
...
{
    "model": "music.album",
    "pk": 3,
    "fields": {
        "artist": "Hilltop Hoods",
        "album_title": "The Great Expanse",
        "genre": "Australian hip hop",
        "album_logo": "music.jpg",
        "date_posted": "2019-04-03T03:24:26.446Z",
        "author": [
            "chuanshuo"
        ]
    }
},
...
{
    "model": "authtoken.token",
    "pk": "48a363cf27cd93328dc20a35b8996bbc2610deb6",
    "fields": {
        "user": [
            "bob"
        ],
        "created": "2019-05-24T02:51:54.918Z"
    }
},
...
{
    "model": "admin.logentry",
    "pk": 1,
    "fields": {
        "action_time": "2019-03-03T01:49:44.074Z",
        "user": [
            "chuanshuo"
        ],
        "content_type": [
            "music",
            "album"
        ],
        "object_id": "2",
        "object_repr": "Middle School - Myth",
        "action_flag": 2,
        "change_message": "[{\"changed\": {\"fields\": [\"album_title\"]}}]"
    }
},
...
]

python manage.py loaddata dbname.json --database=postgresql
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xff in position 0: invalid start byte

python manage.py shell

(InteractiveConsole)
>>> from django.contrib.contenttypes.models import ContentType
>>> ContentType.objects.all().delete()
(45, {'auth.Group_permissions': 0, 'auth.User_user_permissions': 0, 'auth.Permission': 36, 'contenttypes.ContentType': 9})
>>> quit()

reference:
https://medium.com/agatha-codes/painless-postgresql-django-d4f03364989
https://stackoverflow.com/questions/5394331/how-to-set-up-a-postgresql-database-in-django

migrate from mysql to postgres
https://www.calazan.com/migrating-django-app-from-mysql-to-postgresql/
https://stackoverflow.com/questions/28444614/django-manage-py-unknown-command-syncdb
https://gist.github.com/sirodoht/f598d14e9644e2d3909629a41e3522ad

No comments:

Post a Comment