starred/headscale
1
0
Fork 0
mirror of https://github.com/juanfont/headscale.git synced 2026-01-13 04:40:29 +01:00
Code Issues 98 Packages Projects Releases 10 Wiki Activity

Migration eventually incomplete: last_successful_update part of 0.23.0-alpha3 SQLite dump, but not of newly initialised PostgreSQL nodes table schema #636

New Issue
Closed
opened 2025-12-29 02:21:26 +01:00 by adam · 4 comments
adam commented 2025-12-29 02:21:26 +01:00
Owner
Copy Link

Originally created by @almereyda on GitHub (Feb 13, 2024).

Bug description

When converting an SQLite dump of the current 0.23 alpha3 to be imported into a cleanly initialised PostgreSQL instance with Headschale schema, the psql client throws an error that there is one value too much in the line.

NOTICE:  relation "nodes" already exists, skipping                                                                                                                            
CREATE TABLE                                                                                                                                                                  
ERROR:  INSERT has more expressions than target columns

Manually examining the situation makes it appear as if the column last_successful_update is not needed anymore.

A migration is eventually missing to remove that column from existing databases.

Environment

  • Headscale v0.23.0-alpha3

To Reproduce

  1. Dump the SQLite database with a command similar to sqlite3 db.sqlite .dump > db.sql.
  2. Replace all back ticks with double quotes ".
  3. Add IF NOT EXISTS to all CREATE TABLE statements.
  4. Try to pipe the file into an authenticated PSQL client and see the error.
  5. Find the columns in the PostgreSQL database to be in a different order than in SQLite, plus missing last_successful_update.
Originally created by @almereyda on GitHub (Feb 13, 2024). ## Bug description When converting an SQLite dump of the current 0.23 alpha3 to be imported into a cleanly initialised PostgreSQL instance with Headschale schema, the `psql` client throws an error that there is one value too much in the line. ``` NOTICE: relation "nodes" already exists, skipping CREATE TABLE ERROR: INSERT has more expressions than target columns ``` Manually examining the situation makes it appear as if the column `last_successful_update` is not needed anymore. A migration is eventually missing to remove that column from existing databases. ## Environment - Headscale v0.23.0-alpha3 ## To Reproduce 1. Dump the SQLite database with a command similar to `sqlite3 db.sqlite .dump > db.sql`. 2. Replace all back ticks with double quotes `"`. 3. Add `IF NOT EXISTS` to all `CREATE TABLE` statements. 4. Try to pipe the file into an authenticated PSQL client and see the error. 5. Find the columns in the PostgreSQL database to be in a different order than in SQLite, plus missing `last_successful_update`.
adam added the bug label 2025-12-29 02:21:26 +01:00
adam closed this issue 2025-12-29 02:21:26 +01:00
adam commented 2025-12-29 02:21:27 +01:00
Author
Owner
Copy Link

@almereyda commented on GitHub (Feb 13, 2024):

The one node created after the upgrade from 0.22.3 also has the value NULL in the field.

@almereyda commented on GitHub (Feb 13, 2024): The one node created after the upgrade from 0.22.3 also has the value `NULL` in the field.
adam commented 2025-12-29 02:21:27 +01:00
Author
Owner
Copy Link

@kradalby commented on GitHub (Feb 15, 2024):

Hi @almereyda,

I have created #1754 to remove the column from the table. As for the migration from one database engine to another, this is not something we support or have a goal to support so if you are unable to make the two SQL dialects with each other after this, then that will likely require you to do manual intervention.

@kradalby commented on GitHub (Feb 15, 2024): Hi @almereyda, I have created #1754 to remove the column from the table. As for the migration from one database engine to another, this is not something we support or have a goal to support so if you are unable to make the two SQL dialects with each other after this, then that will likely require you to do manual intervention.
adam commented 2025-12-29 02:21:27 +01:00
Author
Owner
Copy Link

@almereyda commented on GitHub (Feb 15, 2024):

Thanks for the quick reaction and confirming the regression.

Yes, manual intervention is totally expected, esp. when converting SQL dialects.

So to confirm, migrating from one store to the other is totally possible. One only needs to take care of the serialisation format, by converting single tick marks into double quotes, remove the PRAGMA statement, make sure the order of columns is the same and cast some ::bool and ::bytea types for some fields.

@almereyda commented on GitHub (Feb 15, 2024): Thanks for the quick reaction and confirming the regression. Yes, manual intervention is totally expected, esp. when converting SQL dialects. So to confirm, migrating from one store to the other is totally possible. One only needs to take care of the serialisation format, by converting single tick marks into double quotes, remove the `PRAGMA` statement, make sure the order of columns is the same and cast some `::bool` and `::bytea` types for some fields.
adam commented 2025-12-29 02:21:27 +01:00
Author
Owner
Copy Link

@almereyda commented on GitHub (Feb 19, 2024):

As much as I understand that the SQLite migration path is currently not supported officially, it may as well be valid to consider that it will happen in the field at some point or the other.

To complete the instructions that we find here, it is also to note that we had to restart the sequence counters from their actual value, as this was not part of the SQLite dump.

select nextval('api_keys_id_seq');
select nextval('nodes_id_seq');
select nextval('pre_auth_key_acl_tags_id_seq');
select nextval('pre_auth_keys_id_seq');
select nextval('routes_id_seq');
select nextval('users_id_seq');

This showed 1 everywhere, which led new user and node registrations to fail, as the ID would already have been taken. One would need to set the next value after the current maximum ID in a given table.

select max(id) from api_keys;
select max(id) from nodes;
select max(id) from pre_auth_key_acl_tags;
select max(id) from pre_auth_keys;
select max(id) from routes;
select max(id) from users;

ALTER SEQUENCE api_keys_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE nodes_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE pre_auth_key_acl_tags_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE pre_auth_keys_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE routes_id_seq RESTART WITH <output from above + 1>
ALTER SEQUENCE users_id_seq RESTART WITH <output from above + 1>

After restarting the counters, the API returned to nominal operation.

This is left here for the interested search engine user running into similar side-effects when importing an SQLite dump into an empty PostgreSQL database freshly initialised by Headscale.

@almereyda commented on GitHub (Feb 19, 2024): As much as I understand that the SQLite migration path is currently not supported officially, it may as well be valid to consider that it will happen in the field at some point or the other. To complete the instructions that we find here, it is also to note that we had to restart the sequence counters from their actual value, as this was not part of the SQLite dump. ```sql select nextval('api_keys_id_seq'); select nextval('nodes_id_seq'); select nextval('pre_auth_key_acl_tags_id_seq'); select nextval('pre_auth_keys_id_seq'); select nextval('routes_id_seq'); select nextval('users_id_seq'); ``` This showed `1` everywhere, which led new user and node registrations to fail, as the ID would already have been taken. One would need to set the next value after the current maximum ID in a given table. ```sql select max(id) from api_keys; select max(id) from nodes; select max(id) from pre_auth_key_acl_tags; select max(id) from pre_auth_keys; select max(id) from routes; select max(id) from users; ``` ```sql ALTER SEQUENCE api_keys_id_seq RESTART WITH <output from above + 1> ALTER SEQUENCE nodes_id_seq RESTART WITH <output from above + 1> ALTER SEQUENCE pre_auth_key_acl_tags_id_seq RESTART WITH <output from above + 1> ALTER SEQUENCE pre_auth_keys_id_seq RESTART WITH <output from above + 1> ALTER SEQUENCE routes_id_seq RESTART WITH <output from above + 1> ALTER SEQUENCE users_id_seq RESTART WITH <output from above + 1> ``` After restarting the counters, the API returned to nominal operation. This is left here for the interested search engine user running into similar side-effects when importing an SQLite dump into an empty PostgreSQL database freshly initialised by Headscale.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
CLI
DERP
DNS
Nix
OIDC
SSH
bug
database
documentation
duplicate
enhancement
faq
good first issue
grants
help wanted
might-come
needs design doc
needs investigation
no-stale-bot
out of scope
performance
policy 📝
pull-request
Mirrored from GitHub Pull Request
 question
regression
routes
stale
tags
tailscale-feature-gap
well described ❤️
wontfix
No Label bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/headscale#636
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?