Skip to content

Migration of store.db from SQLite to PostgreSQL through pgloader does not work as described #402

@jr-timme

Description

@jr-timme

The process described here:

2. Import Sqlite data to Postgres
For migrating the Sqlite data we rely on the [pgloader](https://github.com/dimitri/pgloader) tool. You can install it by running
`sudo apt-get install pgloader` on debian or `brew install pgloader` on MacOS.
```bash
pgloader --type sqlite backup/store.db "postgresql://<PG_USER>:<PG_PASSWORD>@<PG_HOST>:<PG_PORT>/<PG_DB_NAME>"
```
currently does not work as the column settings_lazy_connection_enabled inside the table accounts is of type numeric with a default value of false. This is probably because SQLite does not have a native Boolean Type. The default value of false then leads to this error when running pgloader:

ERROR Database error 22P02: invalid input syntax for type numeric: "false"
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_extra_peer_approval_enabled         numeric,
  settings_extra_integrated_validator_groups   text,
  settings_lazy_connection_enabled             numeric default 'false'
);
FATAL Failed to create the schema, see above.
LOG report summary reset

The default value should probably be set to 0 instead, then the pgloader command can actually load the data but there is an additional step necessary afterwards because netbird still expects true or false for this column and this generates an error when you try to change the setting in the web ui, this psql query worked for me:

alter table accounts alter column settings_lazy_connection_enabled drop default,
  alter column settings_lazy_connection_enabled  set data type boolean using case settings_lazy_connection_enabled when 1 then true else false end,
  alter column settings_lazy_connection_enabled set default false;

as my postgresql knowledge is severely limited I assume there may also be a way better way to achieve this.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions