[GH-ISSUE #4342] Migrate database from SQLite to PostgreSQL - table schema error #8949

Closed
opened 2026-08-05 01:20:09 -04:00 by saavagebueno · 8 comments
Owner

Originally created by @mapolone on GitHub (Aug 14, 2025).
Original GitHub issue: https://github.com/netbirdio/netbird/issues/4342

Describe the problem

I'm following this guide to move from SQLite local database to a managed PostgreSQL (version 16.9), as I'm evaluating the self-hosted version on a Ubuntu 24.04 server instance.

To Reproduce

Run the following command, after creating a backup as described here:

pgloader --type sqlite backup/store.db "postgresql://<PG_USER>:<PG_PASSWORD>@<PG_HOST>:<PG_PORT>/<PG_DB_NAME>"

What I get is:

ERROR Database error 22P02: invalid input syntax for type numeric: "false"

and the following schema output:

QUERY: CREATE TABLE accounts
(
  id                                           text,
  created_by                                   text,
  created_at                                   timestamptz,
  domain                                       text,
  domain_category                              text,
  is_domain_primary_account                    numeric,
  network_identifier                           text,
  network_net                                  text,
  network_dns                                  text,
  network_serial                               bigint,
  dns_settings_disabled_management_groups      text,
  settings_peer_login_expiration_enabled       numeric,
  settings_peer_login_expiration               bigint,
  settings_peer_inactivity_expiration_enabled  numeric,
  settings_peer_inactivity_expiration          bigint,
  settings_regular_users_view_blocked          numeric,
  settings_groups_propagation_enabled          numeric,
  settings_jwt_groups_enabled                  numeric,
  settings_jwt_groups_claim_name               text,
  settings_jwt_allow_groups                    text,
  settings_routing_peer_dns_resolution_enabled numeric,
  settings_dns_domain                          text,
  settings_network_range                       text,
  settings_extra_peer_approval_enabled         numeric,
  settings_extra_integrated_validator          text,
  settings_extra_integrated_validator_groups   text,
  settings_lazy_connection_enabled             numeric default 'false'
);

as it is on the SQLite database.
Not sure why the _enabled fields should contain a numeric value instead of a boolean.

Expected behavior

Table and data migrated successfully.

Are you using NetBird Cloud?

No.

NetBird version

0.54.2

Have you tried these troubleshooting steps?

  • Reviewed client troubleshooting (if applicable)
  • Checked for newer NetBird versions
  • Searched for similar issues on GitHub (including closed ones)
  • Restarted the NetBird client
  • Disabled other VPN software
  • Checked firewall settings
Originally created by @mapolone on GitHub (Aug 14, 2025). Original GitHub issue: https://github.com/netbirdio/netbird/issues/4342 **Describe the problem** I'm following [this guide](https://docs.netbird.io/selfhosted/postgres-store) to move from SQLite local database to a managed PostgreSQL (version 16.9), as I'm evaluating the self-hosted version on a Ubuntu 24.04 server instance. **To Reproduce** Run the following command, after creating a backup as [described here](https://docs.netbird.io/selfhosted/selfhosted-guide#backup): ```shell pgloader --type sqlite backup/store.db "postgresql://<PG_USER>:<PG_PASSWORD>@<PG_HOST>:<PG_PORT>/<PG_DB_NAME>" ``` What I get is: ``` ERROR Database error 22P02: invalid input syntax for type numeric: "false" ``` and the following schema output: ``` QUERY: CREATE TABLE accounts ( id text, created_by text, created_at timestamptz, domain text, domain_category text, is_domain_primary_account numeric, network_identifier text, network_net text, network_dns text, network_serial bigint, dns_settings_disabled_management_groups text, settings_peer_login_expiration_enabled numeric, settings_peer_login_expiration bigint, settings_peer_inactivity_expiration_enabled numeric, settings_peer_inactivity_expiration bigint, settings_regular_users_view_blocked numeric, settings_groups_propagation_enabled numeric, settings_jwt_groups_enabled numeric, settings_jwt_groups_claim_name text, settings_jwt_allow_groups text, settings_routing_peer_dns_resolution_enabled numeric, settings_dns_domain text, settings_network_range text, settings_extra_peer_approval_enabled numeric, settings_extra_integrated_validator text, settings_extra_integrated_validator_groups text, settings_lazy_connection_enabled numeric default 'false' ); ``` as it is on the SQLite database. Not sure why the _enabled fields should contain a numeric value instead of a boolean. **Expected behavior** Table and data migrated successfully. **Are you using NetBird Cloud?** No. **NetBird version** 0.54.2 **Have you tried these troubleshooting steps?** - [X] Reviewed [client troubleshooting](https://docs.netbird.io/how-to/troubleshooting-client) (if applicable) - [X] Checked for newer NetBird versions - [X] Searched for similar issues on GitHub (including closed ones) - [X] Restarted the NetBird client - [X] Disabled other VPN software - [X] Checked firewall settings
saavagebueno added the triage-needed label 2026-08-05 01:20:09 -04:00
Author
Owner

@MichaelUray commented on GitHub (Aug 21, 2025):

I hat the same issue, that the default value in the SQlite DB for a numeric field settings_lazy_connection_enabled was false, which is not numeric.
settings_lazy_connection_enabled numeric default 'false'

There is no bool data type in SQLite and for that reason numeric got chosen.
I just did download the SQLite .db file and did change its default value from false to 0 by the Configure button.
Image

That default false value actually should get changed to 0 in the Netbird SQLite database initialisation code, not sure where to find it.

After that I experienced the problem, that it was not possible to change the Session Expiration time in Settings/Authentication

management/server/store/sql_store.go:2336: failed to save account settings to store: failed to encode args[15]: unable to encode true into binary format for numeric (OID 1700): cannot find encode plan.

That fixed it:
psql in Postgres docker container.

netbird_db=# BEGIN;

-- 1) remove old default
ALTER TABLE accounts
  ALTER COLUMN settings_lazy_connection_enabled DROP DEFAULT;

-- 2) convert type (0/NULL -> false, ≠0 -> true)
ALTER TABLE accounts
  ALTER COLUMN settings_lazy_connection_enabled
  TYPE boolean
  USING (COALESCE(settings_lazy_connection_enabled, 0) <> 0);

-- 3) Nulls to false
UPDATE accounts
  SET settings_lazy_connection_enabled = false
  WHERE settings_lazy_connection_enabled IS NULL;

-- 4) new default + NOT NULL set
ALTER TABLE accounts
  ALTER COLUMN settings_lazy_connection_enabled SET DEFAULT false,
  ALTER COLUMN settings_lazy_connection_enabled SET NOT NULL;

COMMIT;
<!-- gh-comment-id:3209518956 --> @MichaelUray commented on GitHub (Aug 21, 2025): I hat the same issue, that the default value in the SQlite DB for a numeric field `settings_lazy_connection_enabled` was `false`, which is not numeric. ` settings_lazy_connection_enabled numeric default 'false'` There is no bool data type in SQLite and for that reason numeric got chosen. I just did download the SQLite .db file and did change its default value from `false` to `0` by the `Configure` button. <img width="587" height="582" alt="Image" src="https://github.com/user-attachments/assets/3a5a52aa-bdbb-40ef-84e0-08554c99e59d" /> That `default` `false` value actually should get changed to `0` in the Netbird SQLite database initialisation code, not sure where to find it. After that I experienced the problem, that it was not possible to change the `Session Expiration` time in `Settings/Authentication` ``` management/server/store/sql_store.go:2336: failed to save account settings to store: failed to encode args[15]: unable to encode true into binary format for numeric (OID 1700): cannot find encode plan. ``` That fixed it: psql in Postgres docker container. ``` netbird_db=# BEGIN; -- 1) remove old default ALTER TABLE accounts ALTER COLUMN settings_lazy_connection_enabled DROP DEFAULT; -- 2) convert type (0/NULL -> false, ≠0 -> true) ALTER TABLE accounts ALTER COLUMN settings_lazy_connection_enabled TYPE boolean USING (COALESCE(settings_lazy_connection_enabled, 0) <> 0); -- 3) Nulls to false UPDATE accounts SET settings_lazy_connection_enabled = false WHERE settings_lazy_connection_enabled IS NULL; -- 4) new default + NOT NULL set ALTER TABLE accounts ALTER COLUMN settings_lazy_connection_enabled SET DEFAULT false, ALTER COLUMN settings_lazy_connection_enabled SET NOT NULL; COMMIT; ```
Author
Owner

@laweschan commented on GitHub (Aug 25, 2025):

Hi @MichaelUray ,

thanks for point out and I am able to migrate store_config from sqlite to postgres now, however during viewing logs I found an Error said primary key issue, and I assign id text primary key to solve it, may I ask are you also under this error ?

also I am also migrate event log to postgres but no luck, https://docs.netbird.io/selfhosted/activity-postgres-store

I have go through the step but a strange error "management/server/event.go:77: received an error while storing an activity event, error: ERROR: null value in column "id" of relation "events" violates not-null constraint (SQLSTATE 23502)" , do you have any idea ?

thanks

Regards,
Lawes

<!-- gh-comment-id:3219199765 --> @laweschan commented on GitHub (Aug 25, 2025): Hi @MichaelUray , thanks for point out and I am able to migrate store_config from sqlite to postgres now, however during viewing logs I found an Error said primary key issue, and I assign id text primary key to solve it, may I ask are you also under this error ? also I am also migrate event log to postgres but no luck, https://docs.netbird.io/selfhosted/activity-postgres-store I have go through the step but a strange error "management/server/event.go:77: received an error while storing an activity event, error: ERROR: null value in column "id" of relation "events" violates not-null constraint (SQLSTATE 23502)" , do you have any idea ? thanks Regards, Lawes
Author
Owner

@MichaelUray commented on GitHub (Aug 25, 2025):

during viewing logs I found an Error said primary key issue, and I assign id text primary key to solve it, may I ask are you also under this error ?

I don't see an error like this in my current logs.

I have go through the step but a strange error "management/server/event.go:77: received an error while storing an activity event, error: ERROR: null value in column "id" of relation "events" violates not-null constraint (SQLSTATE 23502)" , do you have any idea ?

I can't recall that I had problems importing it.

<!-- gh-comment-id:3221503236 --> @MichaelUray commented on GitHub (Aug 25, 2025): > during viewing logs I found an Error said primary key issue, and I assign id text primary key to solve it, may I ask are you also under this error ? I don't see an error like this in my current logs. > I have go through the step but a strange error "management/server/event.go:77: received an error while storing an activity event, error: ERROR: null value in column "id" of relation "events" violates not-null constraint (SQLSTATE 23502)" , do you have any idea ? I can't recall that I had problems importing it.
Author
Owner

@mapolone commented on GitHub (Sep 15, 2025):

I ended up recreating the installation with PostgreSQL. Thanks anyway for your suggestions.

<!-- gh-comment-id:3290782124 --> @mapolone commented on GitHub (Sep 15, 2025): I ended up recreating the installation with PostgreSQL. Thanks anyway for your suggestions.
Author
Owner

@laweschan commented on GitHub (Sep 15, 2025):

@mapolone may I ask are you using docker or bare metal method for netbird deployment ?
as I found the quick-start script for docker deployment method seems not take the postgres deployment even I use NETBIRD_STORE_CONFIG_ENGINE=postgres from initialize setup.

Thx

<!-- gh-comment-id:3291141521 --> @laweschan commented on GitHub (Sep 15, 2025): @mapolone may I ask are you using docker or bare metal method for netbird deployment ? as I found the quick-start script for docker deployment method seems not take the postgres deployment even I use NETBIRD_STORE_CONFIG_ENGINE=postgres from initialize setup. Thx
Author
Owner

@mapolone commented on GitHub (Sep 15, 2025):

@laweschan I'm using Docker for Netbird deployment. Don't forget to change the StoreConfig option in management.json file as mentioned by the installation guide:

"StoreConfig": {
      "Engine": "postgres"
}
<!-- gh-comment-id:3293864649 --> @mapolone commented on GitHub (Sep 15, 2025): @laweschan I'm using Docker for Netbird deployment. Don't forget to change the StoreConfig option in management.json file as mentioned by the [installation guide](https://docs.netbird.io/selfhosted/postgres-store): ``` "StoreConfig": { "Engine": "postgres" } ```
Author
Owner

@wbarnard81 commented on GitHub (Dec 3, 2025):

I had the same issue and I would just like to share how I fixed it, in case someone else also struggles.

I installed sqlite3. Then you open the DB:
sqlite3 backup/store.db

Then you create a new table:
CREATE TABLE `accounts_new` (`id` text,`created_by` text,`created_at` datetime,`domain` text,`domain_category` text,`is_domain_primary_account` numeric,`network_identifier` text,`network_net` text,`network_dns` text,`network_serial` integer,`dns_settings_disabled_management_groups` text,`settings_peer_login_expiration_enabled` numeric,`settings_peer_login_expiration` integer,`settings_peer_inactivity_expiration_enabled` numeric,`settings_peer_inactivity_expiration` integer,`settings_regular_users_view_blocked` numeric,`settings_groups_propagation_enabled` numeric,`settings_jwt_groups_enabled` numeric,`settings_jwt_groups_claim_name` text,`settings_jwt_allow_groups` text,`settings_extra_peer_approval_enabled` numeric,`settings_extra_integrated_validator_groups` text, `settings_routing_peer_dns_resolution_enabled` numeric, `settings_dns_domain` text, `settings_lazy_connection_enabled` numeric DEFAULT 0, `settings_network_range` text, `settings_extra_user_approval_required` numeric, `settings_extra_integrated_validator` text,PRIMARY KEY (`id`));

Then you clone the data:
INSERT INTO accounts_new SELECT * FROM accounts;

Then you drop the "old" table:
DROP TABLE accounts;

And then you rename the table:
ALTER TABLE accounts_new RENAME TO accounts;

After this, I was able to load the data into postgres, no issues.

<!-- gh-comment-id:3608410406 --> @wbarnard81 commented on GitHub (Dec 3, 2025): I had the same issue and I would just like to share how I fixed it, in case someone else also struggles. I installed sqlite3. Then you open the DB: ```sqlite3 backup/store.db``` Then you create a new table: ```CREATE TABLE `accounts_new` (`id` text,`created_by` text,`created_at` datetime,`domain` text,`domain_category` text,`is_domain_primary_account` numeric,`network_identifier` text,`network_net` text,`network_dns` text,`network_serial` integer,`dns_settings_disabled_management_groups` text,`settings_peer_login_expiration_enabled` numeric,`settings_peer_login_expiration` integer,`settings_peer_inactivity_expiration_enabled` numeric,`settings_peer_inactivity_expiration` integer,`settings_regular_users_view_blocked` numeric,`settings_groups_propagation_enabled` numeric,`settings_jwt_groups_enabled` numeric,`settings_jwt_groups_claim_name` text,`settings_jwt_allow_groups` text,`settings_extra_peer_approval_enabled` numeric,`settings_extra_integrated_validator_groups` text, `settings_routing_peer_dns_resolution_enabled` numeric, `settings_dns_domain` text, `settings_lazy_connection_enabled` numeric DEFAULT 0, `settings_network_range` text, `settings_extra_user_approval_required` numeric, `settings_extra_integrated_validator` text,PRIMARY KEY (`id`));``` Then you clone the data: ```INSERT INTO accounts_new SELECT * FROM accounts;``` Then you drop the "old" table: ```DROP TABLE accounts;``` And then you rename the table: ```ALTER TABLE accounts_new RENAME TO accounts;``` After this, I was able to load the data into postgres, no issues.
Author
Owner

@gwirn commented on GitHub (Jul 16, 2026):

@wbarnard81 's solution works but there are new columns by now with (at the moment) two more that need numeric DEFAULT 0 to be added. All column names can be listed with PRAGMA table_info(accounts); in sqlite3.

<!-- gh-comment-id:4992104777 --> @gwirn commented on GitHub (Jul 16, 2026): @wbarnard81 's solution works but there are new columns by now with (at the moment) two more that need `numeric DEFAULT 0` to be added. All column names can be listed with `PRAGMA table_info(accounts);` in sqlite3.
Sign in to join this conversation.
No Label triage-needed
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: DYNR/netbird#8949