Netbox on CockroachDB

[DavidSouther]

Yes, I know Netbox only officially supports Postgres :)

My SRE org will only support Cockroach DB.

In the hopes that it maybe helps someone, I am sharing here some notes on what I've done to get netbox running on crdb, and how I got an ISP's worth of DCIM/IPAM data out of our Postgres instance into our Cockroach cluster. Specifically, this migration was for Netbox version 3.2 on both sides, with some org internal plugins.

I hope this helps someone - there's a lot of dead ends & false starts I've hit over the past couple weeks on this, so please feel free to reach out w/ any questions & I'll see if I've come across it but not mentioned it here.

Patches to netbox upstream 3.2

• In netbox/netbox/settings.py
  • Modify the DATABASE settings:

 DATABASE.update({
-  'ENGINE': 'django_prometheus.db.backends.postgresql'
+ 'ENGINE': getattr(configuration, 'DB_ENGINE', 'django_cockroachdb')
 })

• Set DB_ENGINE=django_cockroachdb in the environment
• Add requirements-local.txt or similar with

psycopg2-binary==2.9.3
django-cockroachdb==4.0.*

• In netbox/dcim/fields.py
  • Change MACAddressField db_type from macaddr to text
    • Loses some safety in the Database column constraints, but these are captured in th Netbox model.
  • Change WWNField db_type from macaddr8 to text
• In netbox/ipam/fields.py
  • Change IPNetworkField db_type from cidr to inet
• For 3.2, in all migrations touched by 272d6e7, edit the 0001_initial and 0001_squashed files to use DateTimeField for Created columns from the start, and then remove the created alterations.

Migrating Data

Moving data from Postgres to Cockroach is... more difficult than I expected. pg_dump / IMPORT should work, but was running into a number of issues with integrity constraints. What I found worked better was exporting per-table to CSV, and then using IMPORT TABLE ... CSV DATA.

TABLES=(
  auth_group
  auth_group_permissions
  # auth_permission # auth_permission is filled in by mange.py migrate
  auth_user
  # ... Remaining tables from SHOW TABLES;
)

for table in ${TABLES[@]} ; do
  psql -c "COPY ${table} TO STDOUT WITH (FORMAT csv, HEADER, NULL 'NULL');" > crdb/${table}.csv
done

# mount ./crdb into cockroach, or otherwise get the CSVs somewhere the cockroach cluster can get them
for table in ${TABLES[@]} ; do
  HEADER=$(head -n 1 crdb/$table.csv)
  NODELOCAL="\"nodelocal://1/${table}.csv\""
  docker-compose exec crdb \
    cockroach sql --url=postgresql://root@crdb:26257/netbox_db?sslmode=disable \
    --execute="IMPORT INTO ${table} ($HEADER) CSV DATA ($NODELOCAL) WITH skip = '1', nullif = 'NULL' ;"
done

[shagel222222]

hi, thanks for the work, i used this to replicate your experience. But at the moment I'm get a data types error when starting netbox. did you have it? example error text from browser

``The` complete exception is provided below:

<class 'django.db.utils.DataError'>

unsupported comparison operator: <varchar> = `<int>`

[candlerb]

Tables are created (and amended) by the manage.py migrate installation step - which is done as part of upgrade.sh.

I think you are in for a difficult ride, and you will have to maintain your own fork of Netbox going forward. CockroachDB may use the Postgres wire protocol, and a SQL dialect which is similar, but it's a long way from being Postgres compatible semantically.

[DavidSouther]

This was our experience, as well. (Though I haven't been involved in the project since June of last year.) I don't know if we ever got past 3.2. It's doable, but you're in for a lot of extra work.

[shagel222222]

thanks for your answers, now we are at the testing stage, we did the export / import similarly to your version. it seems that almost all tabs open, except for dcim/interface. cockrachdb does not support the macaddr type, so the text type was chosen in the public.dcim_interface table in the mac_addres column. fields.py has been modified from the instructions above. This issue has not yet been worked around

[xkilian]

Our DB department also does not support Postgres. You are better off self supporting on this db type then translating a half supported implementation. There isn't that much db admin to do anyway. These taks are also well documented.
Anyway good luck, but you should reconsider.

[candlerb]

Or equivalently: if the company policy is "you can't run any application if it's not backed by CockroachDB", the correct response is "then we cannot run Netbox". Netbox is tightly bound to Postgres.