Management Postgresql store

Using Postgres for fresh installations

As of version 0.26.0, the default configuration for fresh installations is SQLite storage. However, users have the option to leverage the benefits of Postgres for new instances beginning from version 0.27.8. To enable Postgres, add to your setup.env the following variable:

NETBIRD_STORE_CONFIG_ENGINE=postgres

This will result in a configuration similar to the following in your management.json file:

    "StoreConfig": {
        "Engine": "postgres"
    }

You can switch back to sqlite storage by setting the NETBIRD_STORE_CONFIG_ENGINE variable to sqlite.

Migrating from SQLite store to Postgres store

This migration process allows users to seamlessly transition between storage options while maintaining data integrity.

1. Backup your data store (store.db in datadir - default /var/lib/netbird/)

mkdir backup
docker compose cp -a management:/var/lib/netbird/. backup/

2. Import Sqlite data to Postgres

For migrating the Sqlite data we rely on the pgloader tool. You can install it by running sudo apt-get install pgloader on debian or brew install pgloader on MacOS.

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

3. Resolve any compatibility issues that may arise due to the migration process.

To address this, you can execute the following SQL commands in your PostgreSQL database:

DELETE FROM policy_rules WHERE policy_rules.policy_id NOT IN (SELECT id FROM policies);

DELETE FROM routes WHERE routes.account_id NOT IN (SELECT id FROM accounts);

DELETE FROM name_server_groups WHERE name_server_groups.account_id NOT IN (SELECT id FROM accounts);

DROP TABLE IF EXISTS rules;

4. Enable Postgres by updating the management.json file and setting the Engine field to postgres as the following example:

"StoreConfig": {
    "Engine": "postgres"
}

5. Create .env file with the following content:

NETBIRD_STORE_ENGINE_POSTGRES_DSN="host=<PG_HOST> user=<PG_USER> password=<PG_PASSWORD> dbname=<PG_DB_NAME> port=<PG_PORT>"

6. Update docker-compose.yml file to pass the postgres configuration to the management container

environment:
  - NETBIRD_STORE_ENGINE_POSTGRES_DSN=${NETBIRD_STORE_ENGINE_POSTGRES_DSN}
env_file:
  - .env

7. Restart the management service

docker compose restart management

8. Check logs to confirm the store switch:

docker compose logs management

You should see an entry similar to:

2024-05-10T15:09:34Z INFO management/server/store.go:109: using Postgres store engine

Rollback to Sqlite store

To rollback to the Sqlite store, follow these steps:

1. Restore store.db backup

docker compose cp backup/. management:/var/lib/netbird/

2. Enable SQLite by updating the management.json file and setting the Engine field to sqlite as the following example:

"StoreConfig": {
    "Engine": "sqlite"
}

3. Restart the Management service.

docker compose restart management

4. Check logs to confirm the store switch:

docker compose logs management

You should see an entry similar to:

2024-05-10T15:09:34Z INFO management/server/store.go:109: using SQLite file store engine

Optional Rollback Postgres data to Sqlite

This is optional and should be used only if you want to rollback the data from Postgres to SQLite while running the same NetBird version. For migrating the Postgres data, we rely on the pg_dump, sed, and sqlite3 tools. Make sure these are installed before proceeding

1. Export Postgres data

pg_dump --data-only --column-inserts "postgresql://<PG_USER>:<PG_PASSWORD>@<PG_HOST>:<PG_PORT>/<PG_DB_NAME>" > data.sql

2. Convert exported Postgres data sql to Sqlite format

sed \
-e 's/\\\\:/\:/g' \
-e 's/\\\\//g' \
-e 's/\\\\;/;/g' \
-e '/^SET /d' \
-e '/setval/d' \
-e "s/'true'/1/g" \
-e "s/'false'/0/g" \
-e 's/public\.//' \
-e '/^[[:space:]]*SELECT/d' data.sql > data.sql

3. Generate database schema from Sqlite backup

sqlite3 backup/store.db '.schema' > schema.sql

4. Create Sqlite database with Postgres exported data

sqlite3 store.db '.read schema.sql' && sqlite3 store.db '.read data.sql'

5. Copy db to the management container

docker compose cp store.db management:/var/lib/netbird/store.db

6. Enable SQLite by updating the management.json file and setting the Engine field to sqlite as the following example:

"StoreConfig": {
    "Engine": "sqlite"
}

7. Restart the Management service.

docker compose restart management

8. Check logs to confirm the store switch:

docker compose logs management

You should see an entry similar to:

2024-05-10T15:09:34Z INFO management/server/store.go:109: using SQLite file store engine