Migration path from PostgreSQL to SQLite

[nicka101]

Use case

As of the 0.23.0 release, the default configuration for the database includes the following note:

# Please note that using Postgres is highly discouraged as it is only supported for legacy reasons.
# All new development, testing and optimisations are done with SQLite in mind.

With this in mind, what is the intended migration path from PostgreSQL to SQLite?

Description

Currently, there doesn't seem to be a migration strategy in place to move users away from the "highly discouraged" Postgres. As the new config format allows configuring both sqlite and postgres options, is there any intention to have a migration tool to alleviate the process, or perhaps documentation covering the necessary steps for it?

Contribution

• [ ] I can write the design doc for this feature
• [ ] I can contribute this feature

How can it be implemented?

Possible solutions:

1. Migration tool
2. Documentation covering manually migrating the database from Postgres
3. An explicit note/documentation stating that migration between the database engines is not supported

[kradalby]

Hi, good question, we do not support migrating between, added a label to get that documented.

I did see an interesting tool from a individual to help with this, we should mention that in our page for tools in our "ecosystem" https://github.com/bigbozza/headscalebacktosqlite .

[badsmoke]

where does the decision to only support sqlite come from?

i am a fan of using an external database, what if in the future more and more data is written to the database, will postgres be used again?

a built-in database is nicer at first, because it simplifies the installation, but I don't think it has a promising future

[kradalby]

We will not remove postgres, the statement is merly implying that if we reach a crossroad where we can do an optimalisation, and it:

A. Only applies to SQLite B. Improves SQLite performance, but sacrifices Postgres

Then we will do that.

SQLite will scale fine for all of headscale's usecases, Tailscale SaaS runs fine on SQLite. So it is not a matter of scaling, if anything, having to support both is holding us back.

The other reason is that we are a very small maintainer team, and while I have more time than other Open Source projects are lucky to have, the hurdle of supporting multiple databases is a large overhead.

[coolrazor007]

Really? Tailscale's backend is on SQLite? I'm really surprised to hear that. I too favor external DBs for safer backup/replication.

The Headscale documentation strongly suggests using/migrating to SQLite.

[mfld-pub]

We got caught up in this, too. Back then it was said "PostgreSQL for prod". Thus set up 3 instances with PostgreSQL backend.

When 0.23 came along, upgrading from 0.22x to 0.23 PostgreSQL <-> PostgresSQL failed claiming some keys were missing from some tables. Discord gossip suggests it may be some glitch when there are nodes that have not been online since the last headscale restart.

At the time I had not heard of or seen https://github.com/bigbozza/headscalebacktosqlite so I recreated parallel instances from scratch with SQLite in line with the new docu suggesting that we will eventually lose PostgreSQL support entirely. While this was not what I wanted to see it does make sense for the project to avoid fragmentation and ditch technical debt where possible.

2 instances are dev with 12 nodes but the big one has 328 nodes :) For this I made ansible groups matching the headscale "users" and pushed a tailscale login with preauth keys. Lots of manual work for non-Linux clients.

I am glad we now have clarity on the fate of PostgreSQL support. FWIW The 328 node instance performs well with SQlite.