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.

[kradalby]

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

Yes, you can read about it here https://tailscale.com/blog/database-for-2022
SQLite is an excellent piece of software.

The Headscale documentation strongly suggests using/migrating to SQLite.

This is correct, and that is intentional.

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

I believe this is something that came from some people doing some sort of performance testing and finding that various versions of headscale would be able to handle 10%~ more nodes with Postgres at the time. I do not think maintainers ever recommended it, but it might have happened.

This was before we did some obvious changes, like enabling WAL on SQLite. The reality is that headscale isnt really bottlenecked by the database, which I believe I have written up a couple of times on various issues.

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.

This is a good example of why I am strongly trying to get everyone onto one database type, it is really hard for us to test all of the possible combinations and permutations of databases and database states.

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.

If it was up to me, we would drop it, but it is likely too engrained and has too many users. I would say that postgres is in "maintenance mode". If it would ever be dropped, we would announce it far in advance and we would work on a official migration path, but I/we have no plans to actually do that.

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

This is one of the points too, I am not sure why everyone thinks SQLite is not suitable for production or doesnt scale. It works great.