[Tutorial] Run your own satellite (part 6) - Database migration

[kocoten1992]

Hi there,

Sorry for the delay, section 11.6 on Glossary of Storj Network Terms - Storj Docs really send me off track, if you wish to know what stripe is – please, only read that section, don’t swallow the whole paper..

Today, we will set up database using satellite-modular to set up schema to latest version.

---

Create User and Databases (main database AND metabase)

To login into your database, you can use my script in [Tutorial] Run your own satellite (part 2) - Set up cockroachdb, option 5) cockroach sql shell.

Then, create user and database using these commands (it is acceptable to manually do this since you only do this once):

create role my_role with login createdb password '123456'; # change your pass

set role my_role;

create database my_database;

create database my_metainfo_database;

(Note that, in postgres, there are no user, create user is really just a sugar syntax for create role with login, there are no group either, everything is a role - specifically mentioning this because someone come from mysql will confused – like myself).

---

Fix incompatibility between CRDB versions

StorJ run test with crdb v23, but as of now, crdb latest version is v26, some options changed it default value, if you try to run migration, you will run into these errors:

Error creating tables for master database on satellite: migrate: v227: migrate: ERROR: this schema change is disallowed because table "bucket_bandwidth_rollups" is locked and this operation cannot automatically unlock the table (SQLSTATE 57000)

Error creating tables for master database on satellite: migrate: v227: migrate: ERROR: relation "bucket_bandwidth_rollups" (261): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction (SQLSTATE 0A000)

I’m crafting a PR to fix this and it’s on the way, but in the mean time, you could set two settings in database to false:

SET CLUSTER SETTING sql.defaults.create_table_with_schema_locked = false;
ALTER ROLE ALL SET autocommit_before_ddl = false;

UPDATE (Jul 28, 2026):

Or, you could patch two SQL in v227 and v244 (and keep your default cluster setting)

---

Database migration

What is migrate? It is a subcommand in satellite-modular. If you never work in software industry, the specific term to research is database migration, basically it a tool to help keep your database schema synchronized with changes in your application code over time, have a look at this.

To run migration, use this command:

./satellite-modular migrate \
--database-options.url "cockroach://my_role:123456@rendezvous.example.com:26257/my_database?sslmode=verify-full&sslrootcert=ca.crt" \
--metainfo.database-url "cockroach://my_role:123456@rendezvous.example.com:26257/my_metainfo_database?sslmode=verify-full&sslrootcert=ca.crt"

Of course, you need to change some of the info in the command to suite your need, if you done it correctly, the migration should run without a hiccup :).

One thing I notice, StorJ is meticulous at writing these migrations, they always ensure compatibility between next version schema with the current version of satellite. What do I mean by that?

For example: your satellite is running binary v112, and v113 just release, you want to upgrade your satellite to v113. How do you do that?

Because they write these migration meticulously, you can do this: first you use binary version v113 to run ./satellite-modular migrate, it will bring schema to v113 while satellite binary still on v112, then gradually replace v112 binary with v113. Database migration without downtime. Yay!

---

And…That’s it for today :), I’ll see you in part 7.

[alpharabbit]

@kocoten1992

StorJ run test with crdb v23, but as of now, crdb latest version is v26

CRDB has changed license. Latest version is throttled if you don’t register so there is reasons for using older versions.

[kocoten1992]

No, this is start-single-node, it doesn’t subject to license or throttle Licensing FAQs.

With COCKROACH_SKIP_ENABLING_DIAGNOSTIC_REPORTING=true, it won’t send telemetry, I think this work for both demo and start-single-node, see this link.

[alpharabbit]

@kocoten1992

No, this is start-single-node, it doesn’t subject to license or throttle Licensing FAQs.

I am aware of this but single node is only useful for development and testing in my opinion. Is your goal a production ready satellite or another storj-sim?

[kocoten1992]

Misunderstood, I thought you are talking about storj testing.

For a real cluster, you can use Enterprise Free license, register a license via their cloud (this action have to do yearly). Remember to check Find out if my company qualifies for an Enterprise Free license.

Then

The other db option (TiDB) is not available atm, stick with crdb for now. Later, we can use Change Data Capture feature to port data to another database.

[alpharabbit]

How about YugabyteDB? This seems to be the number one alternative for people who don’t like the new crdb license? I have not testet it but maybe it works out of the box?

[kocoten1992]

At best I could only give you whether migration would run or not, it need to pass test their jenkins CI, and metabase performance testing, that storj job, you would be in constant fear (from future feature breaking) if you use it without support from storj..

[Alexey]

It was rejected at the research stage because it didn’t meet the minimum required features. It’s either insufficient throughput, or there are no reliable transactions without table locking, or something else, I don’t have the details, unfortunately. Perhaps this research will be published as a technical blog here: Storj Engineering Blog | Storj Engineering Blog

Or maybe it will be fine for @kocoten1992

[elek]

Thanks, great overview. Just some more colors.

There are two main databases:

1. satellitedb: for all user/project management, accounting, … Looks like a normal database
2. metainfo: storage layer support (objects/segments). Looks more like a transactional KV store, as indexes and foreign keys are limited due to performance reasons.

Both of these have migration scripts, and having dedicated migration table which includes the current version of the schema.

Today we support 4 databases:

1. postgres, the original one
2. cockroach which was served us for years
3. Google Spanner the most up-to-date and production tested
4. TiDB (WIP)

You can choose any of these. The difference matters only when you start scaling up. (It’s not obvious to make ranged loop fast and maintain high level insert/read TPS)

The configuration also supports defining different database for different (satellitedb) tables. You can use cockroach but postgres (for example) for the repairqueue tables.

In fact this is what we do, as the priority queue like usage is not the strength of the distributed databases (especially which are based on Raft). But again: this can be a problem only if you scale up.

One final generic note, just for the background: scaling up databases is not easy. For this reason we can find storage systems with both approaches: storing metadata in database (what we do) or manage metadata by own (for example what Apache Ozone do, which stores data in local rocksdb and replicate it by Apache Ratis RAFT protocol implementation). Apache HDFS did the same thing (custom replication of metadata), and it was forked to use database instead, under the name HopsFS. Which probably shows that each approach has pro and cons, and the metadata storage is crucial part of the storage systems…

[kocoten1992]

Thank you so much, just got a glimpse of how complex this could be. Let me explain what you said for the broader community.

If you look at satellite/satellitedb/database.go, you’ll see this code:

var safelyPartitionableDBs = map[string]bool{
	// WARNING: only list additional db names here after they have been
	// validated to be safely partitionable and that they do not do
	// cross-db queries.
	"repairqueue":   true,
	"nodeevents":    true,
	"verifyqueue":   true,
	"reverifyqueue": true,
	"overlaycache":  true, // tables: nodes, node_tags
}

I’ve so many question when first see it, let go through one by one.

What are thoses? What do they do? Is it safe to put it out of distributed database? How much load these cause on database anyway? What will happen if these databases went down? If I use postgres – do I also need pg_pool? And how to upgrade postgres while satellite still running? What is self-healing strategy here? Oh, and what does he mean in One final generic note?

What are thoses? What do they do?

They are a list of named data that could be access independently, no expensive cross reference. If you think in term of database/table/join, you would be not entirely correct.

For example, repairqueue, originally, I think they put it on distributed database, but because it incompatible with the nature of distributed database, they split it to dedicated database/table, the past relic is still there storj/shared/dbutil/mapping.go at 3b0ca56bb3f514af505165dbb98e684ff3b4da76 · storj/storj · GitHub.

But not even postgres could keep up, now it live in a service call jobq, a in-memory priority queue, extremely fast. This only possible because of the nature type of this data, it is safe to lost this data as it will be regenerated with a service called ranged-loop.

As for the rest named data, let’s learn it later.

Is it safe to put it out of distributed database?

It’s depend on of what kind of data, if it self regenerated, then it is safe.

What will happen if these databases went down?

I don’t know yet, the question should be: how the satellite react when it happen, what should we do in the event it happen, if infra self-healing - does it fix itself – the answer is: don’t know yet, let run and learn it later.

How much load these cause on database anyway?

TODO – will add when have more data.

If I use postgres – do I also need pg_pool?

No, built-in pool connection included under the hood.

And how to upgrade postgres while satellite still running?

At first, that what I thought - but maybe we don’t need it? Let learn more and see.

One final generic note meaning

Currently, we store metadata in metainfo database (called metabase). But, that not the only option, we could also store metadata directly on a distributed filesystem.

My thinking is that: because we own our metadata, we could design a filesystem/database work specifically just for our metadata, take advange of every trick in the book?

And also, for broader community, there are interesting section 6.2 in https://static.storj.io/storjv3.pdf - Improving user experience around metadata (no need to read entire paper).

---

This quickly become not a toy territory, if anyone have question, please feel free to ask, I’ll answer if I can. Thanks!

[kocoten1992]

So, maybe we need to actively monitor this safelyPartitionableDBs, it’ll give us time to plan/react how to migrate data in advance. I’ve a script for you:

#!/bin/bash

expected="nodeevents
overlaycache
repairqueue
reverifyqueue
verifyqueue"

actual=$(awk '/^var safelyPartitionableDBs/,/^\}/' satellite/satellitedb/database.go \
  | grep -oP '"[^"]+"(?=:\s*true)' \
  | tr -d '"' \
  | sort)

if [ "$actual" != "$expected" ]; then
  # do something with it
fi

Looking at when they add these commits:

repairqueue
nodeevents
verifyqueue
reverifyqueue
overlaycache

Look like they also plan ahead to give time to their infra engineer.

[kocoten1992]

A sudden thought flash my mind:

@alpharabbit

How about YugabyteDB

@kocoten1992

you would be in constant fear (from future feature breaking) if you use it without support from storj

Now that I could mod pg driver and have test infra, could also mod it to support YugabyteDB if the need arise, now we could really use YugabyteDB instead of afraid it will break in the future..