Management fail to upgrade database from v0.35.2 to v0.36.1 with postgresql

[MrBE4R]

Describe the problem

Upgrading from v0.35.2 to v0.36.1 fail to upgrade schema with postgresql database.

management-1  | 2025-01-16T22:02:08Z INFO [context: SYSTEM] management/server/migration/migration.go:326: Column enabled does not exist in table network_resources, adding it
management-1  | Error: failed creating Store: /var/lib/netbird/: migrate: failed to find rows with empty enabled: ERROR: invalid input syntax for type boolean: "" (SQLSTATE 22P02)
management-1  | 2025-01-16T22:18:41Z INFO [context: SYSTEM] management/server/migration/migration.go:326: Column enabled does not exist in table network_routers, adding it
management-1  | Error: failed creating Store: /var/lib/netbird/: migrate: failed to find rows with empty enabled: ERROR: invalid input syntax for type boolean: "" (SQLSTATE 22P02)

To Reproduce

• Deploy netbird v0.35.2 with postgresql
• Upgrade to v0.36.1

Expected behavior

A clear and concise description of what you expected to happen.

Additional context

Manually adding the missing column allow the management to start.

alter table network_routers add column "enabled" text ;
alter table network_resources add column "enabled" text ;

[bcmmbaga]

Hi @MrBE4R , Thank you for reporting this issue.

Could you check the type of the enabled column in the network_routers and network_resources tables after starting the management service?

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name IN ('network_routers', 'network_resources') AND column_name = 'enabled';

If the enabled column is already of type BOOLEAN, no further action is needed. However, if it’s still TEXT, you may need to update it to BOOLEAN to prevent any issue(s) when creating/updating network resources and routers.

ALTER TABLE network_routers ALTER COLUMN "enabled" TYPE BOOLEAN USING ("enabled"::BOOLEAN);
ALTER TABLE network_resources ALTER COLUMN "enabled" TYPE BOOLEAN USING ("enabled"::BOOLEAN);

[MrBE4R]

Hi @bcmmbaga ,

If I drop the column from the tables, management create them as a boolean (as expected) but won't start due to the check that you have corrected in #3198 .

I run two instances of netbird self-hosted, one with legacy network routes and one that have been migrated to the new networks resources.

On the one using legacy routes, manually adding the column as text allow me to start the management service and I can alter the column back to boolean afterward.

On the one using network resources, adding the column as text allow management to pass the check if column is null or empty but fail on setting the correct value due to mismatching type (as expected).

[bcmmbaga]

For the instance using legacy routes, it's expected to work fine since it doesn't involve the new network concept. For the instance using network resources, you'll need to wait for the upcoming release with the fix. Once the fix is applied, the management service should work as expected