SVI/netbird
1
0
Fork 0
mirror of https://github.com/netbirdio/netbird.git synced 2026-05-17 13:42:11 -04:00
Code Issues 3.0k Packages Projects Releases 10 Wiki Activity

Migrate database from SQLite to PostgreSQL - table schema error #2184

New Issue
Closed
opened 2025-11-20 07:05:26 -05:00 by saavagebueno · 6 comments
saavagebueno commented 2025-11-20 07:05:26 -05:00
Owner
Copy Link

Originally created by @mapolone on GitHub (Aug 14, 2025).

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). **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 2025-11-20 07:05:26 -05:00
saavagebueno commented 2025-11-20 07:05:28 -05:00
Author
Owner
Copy Link

@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;
@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; ```
saavagebueno commented 2025-11-20 07:05:28 -05:00
Author
Owner
Copy Link

@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

@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
saavagebueno commented 2025-11-20 07:05:28 -05:00
Author
Owner
Copy Link

@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.

@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.
saavagebueno commented 2025-11-20 07:05:28 -05:00
Author
Owner
Copy Link

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

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

@mapolone commented on GitHub (Sep 15, 2025): I ended up recreating the installation with PostgreSQL. Thanks anyway for your suggestions.
saavagebueno commented 2025-11-20 07:05:28 -05:00
Author
Owner
Copy Link

@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

@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
saavagebueno commented 2025-11-20 07:05:29 -05:00
Author
Owner
Copy Link

@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"
}
@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" } ```
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
2021 Q4
2022 Q1
2022 Q1
accessibility
acl
agent
agent
Android
Android
api
authentik
automation
azure
battery-usage
bug
cache
client
client-ui
cloud
cloud-only
cloudflare
community
compatibility
config-idp
config-issue
connection
contribution
coturn
cross-vpn
dashboard
data-usage
distribution
dns
docker
documentation
duplicate
enhancement
enhancement
event-stream
feature-request
freebsd
getting-started
go
good first issue
gui
help wanted
home-assistant
idp
inconsistency
integration
integrations
ios
ipv6
jwt
k8s
keycloak
linux
login
macos
management-service
missing-docs
mobile
moved-internal
needs-review
netbird-ui
networking
new-platform
nginx
notification
okta
openwrt
packaging
peer-management
peer-management
peer-management
performance
postgres
posture-checks
psk
pull-request
Mirrored from GitHub Pull Request
 question
refactor
relay
release
rfc
routes
security
security-related
self-hosting
server
signal
sleep-issue
ssh
ssl
status
store
synology
system-compatibility-issue
test-suite
third-party-integration
triage
triage-needed
troubleshooting
UX
waiting-feedback
windows
wontfix
zitadel
No Label triage-needed
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
saavagebueno
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: SVI/netbird#2184
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?