Unable to Restore after Dump and Export Also not working

[camrossi]

I tried this with a pre-existing installation as well as a clean one.

Even if the Dump operation succeed seem restore is not working:

2024-04-17T13:58:17.122334346Z: WARNING ▶ db/Restore 1f1 Could not reset id sequence for saved_filters_id_seq: pq: column "id" does not exist
2024-04-17T13:58:17.122809215Z: INFO    ▶ dump/Restore 1f2 Restored table saved_filters
2024-04-17T13:58:17.526027193Z: WARNING ▶ db/Restore 231 Could not reset id sequence for typesense_sync_id_seq: pq: column "id" does not exist
2024-04-17T13:58:17.526152911Z: INFO    ▶ dump/Restore 232 Restored table typesense_sync
2024-04-17T13:58:17.883932905Z: WARNING ▶ db/Restore 270 Could not reset id sequence for files_id_seq: pq: column "id" does not exist
2024-04-17T13:58:17.884109959Z: INFO    ▶ dump/Restore 271 Restored table files
2024-04-17T13:58:18.217199043Z: WARNING ▶ db/Restore 2af Could not reset id sequence for labels_id_seq: pq: column "id" does not exist
2024-04-17T13:58:18.217324178Z: INFO    ▶ dump/Restore 2b0 Restored table labels
2024-04-17T13:58:18.60937212Z: WARNING  ▶ db/Restore 2ee Could not reset id sequence for team_members_id_seq: pq: column "id" does not exist
2024-04-17T13:58:18.60974198Z: INFO     ▶ dump/Restore 2ef Restored table team_members
2024-04-17T13:58:19.030629971Z: WARNING ▶ db/Restore 32e Could not reset id sequence for task_comments_id_seq: pq: column "id" does not exist
2024-04-17T13:58:19.030812276Z: INFO    ▶ dump/Restore 32f Restored table task_comments
2024-04-17T13:58:19.364512027Z: WARNING ▶ db/Restore 36d Could not reset id sequence for api_tokens_id_seq: pq: column "id" does not exist
2024-04-17T13:58:19.36457474Z: INFO     ▶ dump/Restore 36e Restored table api_tokens
2024-04-17T13:58:19.731473613Z: WARNING ▶ db/Restore 3ac Could not reset id sequence for notifications_id_seq: pq: column "id" does not exist
2024-04-17T13:58:19.731895685Z: INFO    ▶ dump/Restore 3ad Restored table notifications
2024-04-17T13:58:20.087395965Z: CRITICAL        ▶ cmd/func12 3eb could not restore table data for table users: pq: invalid input syntax for type json

The database logs shows this: (The Token is a long string that seems some sort of secret so I have replaced it with just to be on the safe side of things)

postgresql 13:52:37.51 INFO  ==> ** Starting PostgreSQL **
2024-04-17 13:52:37.578 GMT [1] LOG:  pgaudit extension initialized
2024-04-17 13:52:37.623 GMT [1] LOG:  starting PostgreSQL 16.2 on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-04-17 13:52:37.624 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-04-17 13:52:37.625 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2024-04-17 13:52:37.657 GMT [1] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-04-17 13:52:37.725 GMT [154] LOG:  database system was shut down at 2024-04-17 13:52:37 GMT
2024-04-17 13:52:37.797 GMT [1] LOG:  database system is ready to accept connections
2024-04-17 13:53:57.709 GMT [254] ERROR:  column "id" does not exist at character 50
2024-04-17 13:53:57.709 GMT [254] STATEMENT:  SELECT setval('label_tasks_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:53:58.133 GMT [254] ERROR:  column "id" does not exist at character 52
2024-04-17 13:53:58.133 GMT [254] STATEMENT:  SELECT setval('subscriptions_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:53:58.532 GMT [254] ERROR:  column "id" does not exist at character 53
2024-04-17 13:53:58.532 GMT [254] STATEMENT:  SELECT setval('task_assignees_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:53:58.922 GMT [254] ERROR:  column "id" does not exist at character 52
2024-04-17 13:53:58.922 GMT [254] STATEMENT:  SELECT setval('saved_filters_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:53:59.280 GMT [254] ERROR:  column "id" does not exist at character 53
2024-04-17 13:53:59.280 GMT [254] STATEMENT:  SELECT setval('task_relations_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:53:59.664 GMT [254] ERROR:  column "id" does not exist at character 55
2024-04-17 13:53:59.664 GMT [254] STATEMENT:  SELECT setval('task_attachments_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:54:00.094 GMT [254] ERROR:  column "id" does not exist at character 55
2024-04-17 13:54:00.094 GMT [254] STATEMENT:  SELECT setval('migration_status_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:54:00.525 GMT [254] ERROR:  column "id" does not exist at character 48
2024-04-17 13:54:00.525 GMT [254] STATEMENT:  SELECT setval('favorites_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:54:00.959 GMT [254] ERROR:  invalid input syntax for type json
2024-04-17 13:54:00.959 GMT [254] DETAIL:  Token "<String>" is invalid.
2024-04-17 13:54:00.959 GMT [254] CONTEXT:  JSON data, line 1: ...ydWUsInF1aWNrX2FkZF9tYWdpY19tb2RlIjoidmlrdW5qYSJ9
        unnamed portal parameter $12 = '...'
2024-04-17 13:54:00.959 GMT [254] STATEMENT:  INSERT INTO "users" ("avatar_file_id","avatar_provider","created","default_project_id","deletion_last_reminder_sent","deletion_scheduled_at","discoverable_by_email","discoverable_by_name","email","email_reminders_enabled","export_file_id","frontend_settings","id","issuer","language","name","overdue_tasks_reminders_enabled","overdue_tasks_reminders_time","password","status","subject","timezone","updated","username","week_start") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)
2024-04-17 13:57:37.825 GMT [152] LOG:  checkpoint starting: time
2024-04-17 13:58:13.394 GMT [152] LOG:  checkpoint complete: wrote 338 buffers (2.1%); 1 WAL file(s) added, 0 removed, 0 recycled; write=34.569 s, sync=0.228 s, total=35.570 s; sync files=474, longest=0.038 s, average=0.001 s; distance=2367 kB, estimate=2367 kB; lsn=0/1C34AD8, redo lsn=0/1B638A8
2024-04-17 13:58:14.275 GMT [671] ERROR:  relation "notifications" does not exist at character 95
2024-04-17 13:58:14.275 GMT [671] STATEMENT:  SELECT "id", "notifiable_id", "notification", "name", "subject_id", "read_at", "created" FROM "notifications" WHERE (notifiable_id = $1) ORDER BY id DESC LIMIT 50
2024-04-17 13:58:17.121 GMT [655] ERROR:  column "id" does not exist at character 52
2024-04-17 13:58:17.121 GMT [655] STATEMENT:  SELECT setval('saved_filters_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:17.525 GMT [655] ERROR:  column "id" does not exist at character 53
2024-04-17 13:58:17.525 GMT [655] STATEMENT:  SELECT setval('typesense_sync_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:17.883 GMT [655] ERROR:  column "id" does not exist at character 44
2024-04-17 13:58:17.883 GMT [655] STATEMENT:  SELECT setval('files_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:18.216 GMT [655] ERROR:  column "id" does not exist at character 45
2024-04-17 13:58:18.216 GMT [655] STATEMENT:  SELECT setval('labels_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:18.608 GMT [655] ERROR:  column "id" does not exist at character 51
2024-04-17 13:58:18.608 GMT [655] STATEMENT:  SELECT setval('team_members_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:19.030 GMT [655] ERROR:  column "id" does not exist at character 52
2024-04-17 13:58:19.030 GMT [655] STATEMENT:  SELECT setval('task_comments_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:19.364 GMT [655] ERROR:  column "id" does not exist at character 49
2024-04-17 13:58:19.364 GMT [655] STATEMENT:  SELECT setval('api_tokens_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:19.731 GMT [655] ERROR:  column "id" does not exist at character 52
2024-04-17 13:58:19.731 GMT [655] STATEMENT:  SELECT setval('notifications_id_seq', COALESCE(MAX(id), 1) )
2024-04-17 13:58:20.087 GMT [655] ERROR:  invalid input syntax for type json
2024-04-17 13:58:20.087 GMT [655] DETAIL:  Token "<String>" is invalid.
2024-04-17 13:58:20.087 GMT [655] CONTEXT:  JSON data, line 1: ...<String>
        unnamed portal parameter $12 = '...'
2024-04-17 13:58:20.087 GMT [655] STATEMENT:  INSERT INTO "users" ("avatar_file_id","avatar_provider","created","default_project_id","deletion_last_reminder_sent","deletion_scheduled_at","discoverable_by_email","discoverable_by_name","email","email_reminders_enabled","export_file_id","frontend_settings","id","issuer","language","name","overdue_tasks_reminders_enabled","overdue_tasks_reminders_time","password","status","subject","timezone","updated","username","week_start") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25)

This is particularly annoying as also export seems not to work correctly and only a subset of the tasks are exported. After testing the unstable release I re-installed 0.23, restored my export and half my data is gone

[kolaente]

@camrossi

Even if the Dump operation succeed seem restore is not working:

What does the users.json file look like in the dump?

@camrossi

This is particularly annoying as also export seems not to work correctly and only a subset of the tasks are exported. After testing the unstable release I re-installed 0.23, restored my export and half my data is gone

Did you import a dump made from unstable? Which half of the data is gone?

[camrossi]

Thanks for the quick turnaround:

I am running Vikunja on a K8s cluster with authentik so there is no users.json as all authentication is via SSO.
In my dump I have data.json, filters.json and VERSION

SSO is configured like this:

auth:
  local:
    enabled: false
  openid:
    enabled: true
    redirecturl: "https://vikunja.domain.123/auth/openid/"
    providers:
      - name: authentik
        authurl: "https://authentik.domain.123/application/o/vikunja/"
        logouturl: "https://authentik.domain.123/application/o/vikunja/end-session/"
        clientid: "XXX"
        clientsecret: "XXX"

The Token "<String>" is invalid String is not not the clientid nor the clientsecret

For the export thing, I am trying to figure out what is the trigger if I import my old export when I create a new project and export my data again the tasks list is set to Null in data.json but I can’t trigger this on a clean installation… let me mess around a bit more.

[camrossi]

Ok I managed to re-create the export issue…
That took a moment so apparently this completed Task breaks things:

            {
                "id": 8,
                "title": "Move turingpi",
                "description": "\u003cp\u003e\u003c/p\u003e",
                "done": true,
                "done_at": "2024-03-30T05:39:26Z",
                "due_date": "2024-04-04T22:00:00Z",
                "reminders": null,
                "project_id": 4,
                "repeat_after": 0,
                "repeat_mode": 0,
                "priority": 0,
                "start_date": "0001-01-01T00:00:00Z",
                "end_date": "0001-01-01T00:00:00Z",
                "assignees": null,
                "labels": null,
                "hex_color": "",
                "percent_done": 0,
                "identifier": "#2",
                "index": 2,
                "related_tasks": {},
                "attachments": null,
                "cover_image_attachment_id": 0,
                "is_favorite": false,
                "created": "2024-04-17T13:30:34Z",
                "updated": "2024-04-17T13:30:34Z",
                "bucket_id": 5,
                "position": 196608,
                "kanban_position": 196608,
                "created_by": {
                    "id": 1,
                    "name": "Camillo",
                    "username": "akadmin",
                    "created": "2024-04-17T13:29:57Z",
                    "updated": "2024-04-17T13:33:47Z"
                },
                "comments": null
            },

If I delete it export works fine and all tasks are exported successfully.
Being 100% honest I am not sure if when I tested unstable last week I didn’t end up moving from 0.23 to unstable and back… Let me know if that code above tells you anything…

[kolaente]

@camrossi

In my dump I have data.json, filters.json and VERSION

Are you trying to import a user data export created via the web ui with the vikunja restore command? That will never work, you can only import dumps created from vikunja dump with the cli command and the export created via the web ui via the “import from other services” functionality in the web ui.

[camrossi]

Sorry I did use the dump file to restore but for reason that shall never be known I sent you the data from the export…

user.json:

[
    {
        "avatar_file_id": 0,
        "avatar_provider": "initials",
        "created": "2024-04-17 13:29:57",
        "default_project_id": 14,
        "deletion_last_reminder_sent": "",
        "deletion_scheduled_at": "",
        "discoverable_by_email": false,
        "discoverable_by_name": false,
        "email": "xxxxx@gmail.com",
        "email_reminders_enabled": false,
        "export_file_id": 2,
        "frontend_settings": "eyJjb2xvcl9zY2hlbWEiOiJhdXRvIiwicGxheV9zb3VuZF93aGVuX2RvbmUiOnRydWUsInF1aWNrX2FkZF9tYWdpY19tb2RlIjoidmlrdW5qYSJ9",
        "id": 1,
        "issuer": "https://authentik.domain.123/application/o/vikunja/",
        "language": "en",
        "name": "Camillo",
        "overdue_tasks_reminders_enabled": true,
        "overdue_tasks_reminders_time": "9:00",
        "password": "",
        "status": 0,
        "subject": "1ed78066fb9381330a4d426557868b764788672ee3ad57b77c0cec2adb707526",
        "timezone": "GMT",
        "updated": "2024-04-17 13:36:21",
        "username": "akadmin",
        "week_start": 0
    }
]

user_tokens.json is just empty:

[]

[kolaente]

I’ve pushed a potential fix here: 0962aa4262 - please try with the next unstable build if restoring works.