A single secure CockroachDB node, built by one idempotent script, is the fastest way to stage a migration before restoring it into a multi-region cloud cluster. This post shares the scripts, the settings, and four lessons that cut our view-creation time from about 9.5 days to under 2.
Why a single node
The target was a multi-region CockroachDB cluster on Azure and AWS, but the source was an embedded H2 database. Loading straight into a stretched cluster pays cross-region latency on every write, so we staged the migration on one on-prem node and moved it to the cloud as a native backup.
- Build a secure single node with
cockroach start-single-node(replication factor 1, auto-initialised). - Load the source data through JDBC (PostgreSQL wire protocol).
- Take a native
BACKUP DATABASE— a consistent, compressed, version-portable copy. - Push the backup to Azure Blob or S3, or write it there directly.
RESTORE DATABASEon the multi-region cluster.- Decommission the staging node.
Why not a 3-node on-prem cluster? For a throwaway staging box, replication only adds Raft overhead and doubles the disk. One node loads faster, and the backup is identical.
Environment and prerequisites
One RHEL server with 24 cores, 96 GB RAM and a 200 GB /var volume carried the whole load; the database ended at 2.7 GB on disk with about 70,000 tables. IP addresses and names below are placeholders — replace them with your own.
| Item | Value |
|---|---|
| OS | RHEL 8/9, x86_64 |
| CockroachDB | v26.2.0 (CCL binary) |
| Server IP | 10.10.10.20 (placeholder) |
| OS service user | cockroach, home /var/cockroach |
| Store / certs / logs | /var/cockroach/data, /certs, /logs |
Backup dir (nodelocal://1/) |
/var/cockroach/Backup |
| RPC port | 26257 |
| SQL port | 26258 |
| DB Console | 8080 (HTTPS) |
| App database / user | appdb / appuser (placeholders) |
Before you start:
- Root or sudo access, and
dnfrepositories reachable. - Internet access to
binaries.cockroachdb.com, or the.tgzcopied to/tmpbeforehand. - NTP running (the script enables
chronyd); CockroachDB refuses to run with large clock skew. - Free space of at least 1.5× the expected data size plus one backup.
Step 1 – The one-shot install script
One idempotent script does everything: OS tuning, the service user, the binary, TLS certificates, a systemd unit, cluster settings, and the application database. Re-running it is safe; each step skips work already done.
Run it as root, passing the server IP and the application password:
HOST_IP=10.10.10.20 APP_PASS='ChangeMe2026' bash 01_install_crdb_single_node.sh
#!/usr/bin/env bash
# CockroachDB single-node install for migration staging (RHEL 8/9)
set -euo pipefail
CRDB_VERSION="${CRDB_VERSION:-v26.2.0}"
HOST_IP="${HOST_IP:?export HOST_IP (this server's IP)}"
APP_PASS="${APP_PASS:?export APP_PASS (application user password)}"
RPC_PORT=26257; SQL_PORT=26258; HTTP_PORT=8080
CRDB_USER=cockroach
CRDB_HOME=/var/cockroach
DATA_DIR=$CRDB_HOME/data; CERTS_DIR=$CRDB_HOME/certs; CA_KEY_DIR=$CRDB_HOME/ca-key
LOG_DIR=$CRDB_HOME/logs; BACKUP_DIR=$CRDB_HOME/Backup; SCRIPTS_DIR=$CRDB_HOME/scripts
APP_DB=appdb; APP_USER=appuser
CACHE=.35; MAX_SQL_MEM=.35 # use .25 each if the loader runs on the same box
log() { echo; echo "[$(date '+%F %T')] ==> $*"; }
die() { echo "[ERROR] $*" >&2; exit 1; }
[[ $EUID -eq 0 ]] || die "Run as root"
ip -4 addr | grep -q "inet ${HOST_IP}/" || die "${HOST_IP} is not configured on this host"
log "OS packages and kernel settings"
dnf -y install tar gzip curl numactl chrony python3 >/dev/null
systemctl enable --now chronyd
cat >/etc/sysctl.d/99-cockroach.conf <<EOF
vm.swappiness = 1
vm.max_map_count = 262144
EOF
sysctl -q -p /etc/sysctl.d/99-cockroach.conf
log "Service user and directories"
id "$CRDB_USER" &>/dev/null || useradd -m -d "$CRDB_HOME" -s /bin/bash "$CRDB_USER"
mkdir -p "$DATA_DIR" "$CERTS_DIR" "$CA_KEY_DIR" "$LOG_DIR" "$BACKUP_DIR" "$SCRIPTS_DIR"
chown -R "$CRDB_USER:$CRDB_USER" "$CRDB_HOME"
chmod 700 "$DATA_DIR" "$CERTS_DIR" "$CA_KEY_DIR"
log "CockroachDB binary"
TGZ="cockroach-${CRDB_VERSION}.linux-amd64.tgz"
if ! /usr/local/bin/cockroach version 2>/dev/null | grep -q "$CRDB_VERSION"; then
[[ -f /tmp/$TGZ ]] || curl -fL -o "/tmp/$TGZ" "https://binaries.cockroachdb.com/$TGZ"
tar -xzf "/tmp/$TGZ" -C /tmp
install -m 0755 "/tmp/cockroach-${CRDB_VERSION}.linux-amd64/cockroach" /usr/local/bin/cockroach
fi
cockroach version | head -3
log "TLS certificates (secure mode is required for password logins)"
if [[ ! -f $CERTS_DIR/ca.crt ]]; then
cockroach cert create-ca --certs-dir="$CERTS_DIR" --ca-key="$CA_KEY_DIR/ca.key"
cockroach cert create-node "$HOST_IP" localhost 127.0.0.1 "$(hostname -s)" "$(hostname -f)" --certs-dir="$CERTS_DIR" --ca-key="$CA_KEY_DIR/ca.key"
cockroach cert create-client root --certs-dir="$CERTS_DIR" --ca-key="$CA_KEY_DIR/ca.key"
fi
chown -R "$CRDB_USER:$CRDB_USER" "$CERTS_DIR" "$CA_KEY_DIR"
chmod 600 "$CERTS_DIR"/*.key "$CA_KEY_DIR"/ca.key
log "systemd service (start-single-node = auto-init, replication factor 1)"
cat >/etc/systemd/system/cockroach.service <<EOF
[Unit]
Description=CockroachDB single node (migration staging)
Wants=network-online.target
After=network-online.target
[Service]
Type=notify
User=$CRDB_USER
Group=$CRDB_USER
ExecStart=/usr/local/bin/cockroach start-single-node --certs-dir=$CERTS_DIR --store=path=$DATA_DIR --listen-addr=$HOST_IP:$RPC_PORT --advertise-addr=$HOST_IP:$RPC_PORT --sql-addr=$HOST_IP:$SQL_PORT --http-addr=$HOST_IP:$HTTP_PORT --accept-sql-without-tls --external-io-dir=$BACKUP_DIR --cache=$CACHE --max-sql-memory=$MAX_SQL_MEM --log-dir=$LOG_DIR
Restart=on-failure
RestartSec=15
LimitNOFILE=1048576
TimeoutStartSec=600
TimeoutStopSec=300
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable --now cockroach
if systemctl is-active --quiet firewalld; then
for p in $RPC_PORT $SQL_PORT $HTTP_PORT; do firewall-cmd -q --permanent --add-port=${p}/tcp; done
firewall-cmd -q --reload
fi
crsql() { cockroach sql --certs-dir="$CERTS_DIR" --host="$HOST_IP:$SQL_PORT" "$@"; }
log "Waiting for SQL"
for i in $(seq 1 60); do crsql -e "SELECT 1" &>/dev/null && break; sleep 5; done
log "Cluster settings for bulk migration"
crsql <<EOF
SET CLUSTER SETTING kv.raft.command.max_size = '600MiB';
SET CLUSTER SETTING kv.bulk_io_write.concurrent_addsstable_requests = 8;
SET CLUSTER SETTING kv.transaction.write_pipelining.enabled = true;
SET CLUSTER SETTING kv.range_split.by_load.enabled = true;
SET CLUSTER SETTING sql.defaults.vectorize = 'on';
SET CLUSTER SETTING sql.stats.aggregation.interval = '15m';
SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true;
SET CLUSTER SETTING sql.schema.approx_max_object_count = 200000;
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false;
EOF
log "Application database and user"
crsql <<EOF
CREATE DATABASE IF NOT EXISTS $APP_DB;
CREATE USER IF NOT EXISTS $APP_USER WITH LOGIN PASSWORD '$APP_PASS';
GRANT admin TO $APP_USER;
ALTER DATABASE $APP_DB OWNER TO $APP_USER;
ALTER DATABASE $APP_DB SET default_transaction_isolation = 'read committed';
ALTER DATABASE $APP_DB SET serial_normalization = 'sql_sequence';
ALTER DATABASE $APP_DB SET default_int_size = 4;
EOF
log "Done. DB Console: https://$HOST_IP:$HTTP_PORT"
Three design choices matter here:
start-single-nodeinitialises the cluster itself and sets replication to 1, so there is no separatecockroach initstep and no Raft overhead.--accept-sql-without-tlskeeps the node secure (certificates, password auth) while letting JDBC clients connect withsslmode=disableduring the load.--external-io-dirmapsnodelocal://1/to a known folder, so backups land where you expect them.
Cluster settings applied and why
All nine settings were accepted by v26.2.0; the last one is temporary and must be switched back on after the load.
| Setting | Value | Why |
|---|---|---|
kv.raft.command.max_size |
600MiB | Large XML/JSON rows exceeded the 64 MiB default and failed writes |
kv.bulk_io_write.concurrent_addsstable_requests |
8 | More parallel ingestion for bulk loads and restores |
kv.transaction.write_pipelining.enabled |
true | Lower latency for multi-statement write transactions |
kv.range_split.by_load.enabled |
true | Splits hot ranges under load |
sql.defaults.vectorize |
on | Vectorised execution for scans and aggregations |
sql.stats.aggregation.interval |
15m | Finer SQL statistics while troubleshooting (default 1h) |
sql.txn.read_committed_isolation.enabled |
true | Lets the app database default to READ COMMITTED |
sql.schema.approx_max_object_count |
200000 | The schema has about 70,000 tables plus views |
sql.stats.automatic_collection.enabled |
false | Avoids stats jobs during the bulk load — re-enable afterwards |
Database-level session defaults set on appdb:
| Database setting | Value | Why |
|---|---|---|
default_transaction_isolation |
read committed | Matches the application's PostgreSQL behaviour |
serial_normalization |
sql_sequence | SERIAL gets a real sequence, not unique_rowid() (see Lessons) |
default_int_size |
4 | INT means 4 bytes, as in PostgreSQL |
Step 2 – Verify the node and the application login
A healthy install shows one live node, num_replicas = 1, appuser in the admin role, and READ COMMITTED for the application session.
sudo -iu cockroach
alias crsql='cockroach sql --certs-dir=/var/cockroach/certs --host=10.10.10.20:26258'
crsql -e "SELECT version();"
crsql -e "SHOW ZONE CONFIGURATION FROM RANGE default;" | grep num_replicas # num_replicas = 1
crsql -e "SHOW GRANTS ON ROLE admin;" # appuser | admin
cockroach node status --certs-dir=/var/cockroach/certs --host=10.10.10.20:26258
# application login over the SQL port, with a password
cockroach sql --url "postgresql://appuser:ChangeMe2026@10.10.10.20:26258/appdb?sslmode=require" \
-e "SELECT current_user, current_database(); SHOW TRANSACTION ISOLATION LEVEL;"
JDBC URL for the loader:
jdbc:postgresql://10.10.10.20:26258/appdb?sslmode=disable&reWriteBatchedInserts=true&tcpKeepAlive=true
The DB Console is at https://10.10.10.20:8080; log in as appuser and accept the self-signed certificate warning. The cockroach OS user has no password by design — reach it with sudo -iu cockroach.
Step 3 – Backup script for the cloud handoff
Use a logical BACKUP, not a tar of the store: it is consistent while the database is in use, compressed (our 2.7 GB store became an 890 MB backup in about 5 minutes), and restorable on any cluster running the same or a newer version. The script starts the job detached and polls it, so a dropped SSH session cannot kill it.
#!/usr/bin/env bash
# Usage: 02_crdb_backup.sh local | azure | s3 | verify [uri]
# Cloud credentials: /var/cockroach/scripts/cloud.env (chmod 600)
set -euo pipefail
HOST_IP="${HOST_IP:-10.10.10.20}"; SQL_PORT=26258
CERTS_DIR=/var/cockroach/certs
DB="${DB:-appdb}"; COLLECTION="${COLLECTION:-backup_${DB}}"
ENV_FILE=/var/cockroach/scripts/cloud.env
[[ -f $ENV_FILE ]] && source "$ENV_FILE"
crsql() { cockroach sql --certs-dir="$CERTS_DIR" --host="$HOST_IP:$SQL_PORT" "$@"; }
log() { echo "[$(date '+%F %T')] $*"; }
enc() { python3 -c 'import urllib.parse,sys;print(urllib.parse.quote(sys.argv[1],safe=""))' "$1"; }
run_backup() {
local uri="$1" job status frac
job=$(crsql --format=csv -e "BACKUP DATABASE $DB INTO '$uri' WITH detached;" | tail -1)
[[ $job =~ ^[0-9]+$ ]] || { echo "Could not start backup: $job"; exit 1; }
log "job_id=$job"
while true; do
read -r status frac < <(crsql --format=tsv -e "SELECT status, round(fraction_completed*100,1) FROM [SHOW JOB $job];" | tail -1)
log "status=$status progress=${frac}%"
case $status in
succeeded) break ;;
failed|canceled) crsql -e "SELECT error FROM [SHOW JOB $job];"; exit 1 ;;
esac
sleep 60
done
crsql -e "SHOW BACKUPS IN '$uri';"
}
case "${1:-}" in
local) run_backup "nodelocal://1/$COLLECTION"; du -sh "/var/cockroach/Backup/$COLLECTION" ;;
azure) run_backup "azure-blob://${AZURE_CONTAINER}/$COLLECTION?AZURE_ACCOUNT_NAME=${AZURE_ACCOUNT_NAME}&AZURE_ACCOUNT_KEY=$(enc "$AZURE_ACCOUNT_KEY")" ;;
s3) run_backup "s3://${AWS_BUCKET}/$COLLECTION?AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}&AWS_SECRET_ACCESS_KEY=$(enc "$AWS_SECRET_ACCESS_KEY")&AWS_REGION=${AWS_REGION}" ;;
verify) crsql -e "SHOW BACKUP FROM LATEST IN '${2:-nodelocal://1/$COLLECTION}' WITH check_files;" | tail -20 ;;
*) echo "usage: $0 local|azure|s3|verify [uri]"; exit 1 ;;
esac
cloud.env template — keep it chmod 600 and out of version control:
AZURE_ACCOUNT_NAME=<storage-account>
AZURE_ACCOUNT_KEY=<raw key, not URL-encoded>
AZURE_CONTAINER=<container>
AWS_BUCKET=<bucket>
AWS_ACCESS_KEY_ID=<key-id>
AWS_SECRET_ACCESS_KEY=<secret>
AWS_REGION=<region>
Run it under nohup as the cockroach user:
nohup /var/cockroach/scripts/02_crdb_backup.sh local > ~/backup_$(date +%F_%H%M).log 2>&1 &
Restore on the cloud cluster with RESTORE DATABASE appdb FROM LATEST IN '<uri>';. A database backup does not carry users or database-level settings, so create appuser and re-apply the three ALTER DATABASE ... SET statements on the target first.
Lessons learned
CockroachDB speaks the PostgreSQL protocol, but four behaviours differ enough to break or slow a PostgreSQL-targeted loader.
1. SERIAL is not PostgreSQL SERIAL
By default CockroachDB turns SERIAL into INT8 DEFAULT unique_rowid(), which produces 19-digit ids such as 1214937418697834497. Our loader read those ids into a 32-bit integer and crashed with a NullPointerException right after creating its control tables.
The fix is two database-level defaults, set before the loader creates any table:
ALTER DATABASE appdb SET serial_normalization = 'sql_sequence'; -- SERIAL -> nextval(sequence)
ALTER DATABASE appdb SET default_int_size = 4; -- INT -> INT4, as in PostgreSQL
Verify with a throwaway table: CREATE TABLE t (id SERIAL); SHOW CREATE TABLE t; must show INT4 ... DEFAULT nextval(...).
2. Catalog lookups by name scan every object
pg_class, pg_index and pg_attribute are virtual tables in CockroachDB, generated on each query. A lookup by oid is fast, but a lookup by relname builds the full catalog first. With about 70,000 tables, the loader's per-view primary-key query took 13.9 s, which capped view creation at about 4 per minute.
The query looked like this:
SELECT a.attname FROM pg_class t, pg_class i, pg_index ix, pg_attribute a
WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid
AND a.attnum = ANY (ix.indkey) AND t.relkind = 'r' AND t.relname = 'SOME_TABLE';
We could not change the loader's SQL, so we changed what pg_class resolves to. An unqualified catalog name resolves through search_path; if public comes before pg_catalog, public.pg_class wins. We built indexed snapshot tables once:
CREATE TABLE public.pg_class AS
SELECT oid, relname, relnamespace, relkind, reltype, relowner
FROM pg_catalog.pg_class WHERE relnamespace = 'public'::REGNAMESPACE;
CREATE TABLE public.pg_index AS
SELECT indexrelid, indrelid, indisprimary, indisunique,
indkey::INT2[] AS indkey -- INT2VECTOR cannot be stored in a table
FROM pg_catalog.pg_index
WHERE indrelid IN (SELECT oid FROM pg_catalog.pg_class WHERE relnamespace = 'public'::REGNAMESPACE);
-- key columns only: seconds instead of 15+ minutes for a full pg_attribute copy
SET allow_unsafe_internals = true;
CREATE TABLE public.pg_attribute AS
SELECT ic.descriptor_id::OID AS attrelid, ic.column_name AS attname, ic.column_id::INT2 AS attnum
FROM crdb_internal.index_columns ic
JOIN crdb_internal.tables t ON t.table_id = ic.descriptor_id
WHERE t.database_name = 'appdb' AND t.schema_name = 'public' AND ic.column_type = 'key';
CREATE INDEX ON public.pg_class (relname);
CREATE INDEX ON public.pg_class (oid);
CREATE INDEX ON public.pg_index (indrelid);
CREATE INDEX ON public.pg_index (indexrelid);
CREATE INDEX ON public.pg_attribute (attrelid);
Then only the loader's connection was pointed at them, through the JDBC URL:
jdbc:postgresql://10.10.10.20:26258/appdb?currentSchema=public,pg_catalog&...
The same query dropped from 13.9 s to 0.05 s, and a view built this way was byte-identical to one built the old way. Guard rails:
- Use it only for a schema-frozen phase (here: view creation, no new tables), because the snapshot does not refresh.
- Scope it to the bulk tool's connection; never set it on the role used by the application.
- Drop the three tables as soon as the phase ends, before the final backup.
3. A role cannot have a default database
ALTER ROLE appuser SET database = 'appdb' fails with ERROR: parameter "database" cannot be changed (55P02). Put the database in the connection string instead.
4. crdb_internal is locked in v26.2
Queries on crdb_internal and system now fail with SQLSTATE 42501 unless the session sets allow_unsafe_internals = true. Prefer the supported statements — SHOW JOBS, SHOW CLUSTER STATEMENTS, SHOW RANGES — and enable the flag only in ad-hoc DBA sessions.
Results
The catalog workaround made view creation about 7× faster and moved the finish from roughly 9.5 days to under 2.
| Metric | Before | After |
|---|---|---|
| Primary-key lookup per view | 13.9 s | 0.05 s |
| Views created per minute | ~4 | ~30 |
| Time for ~65,000 views (projected) | ~9.5 days | ~36–40 h |
CREATE VIEW DDL per view |
— | 0.33 s average |
| Backup of the loaded database | — | 2.7 GB store → 890 MB, ~5 min |
The remaining ~1.5 s per view sits inside the loader's single-threaded processing, not in CockroachDB, so no further database tuning would help.
Cleanup and next steps
When the load finishes, undo the temporary changes before the final backup:
DROP TABLE public.pg_class, public.pg_index, public.pg_attribute;
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true;
Then remove currentSchema=public,pg_catalog from the loader's JDBC URL, take the final backup with 02_crdb_backup.sh azure (or s3), and restore it on the multi-region cluster. Once the restore is verified, stop and disable the staging node:
sudo systemctl disable --now cockroach
Next in this series: restoring the backup into a multi-region CockroachDB cluster on Azure and AWS, built with Terraform.
