Backup and restore
View as MarkdownMaterialize keeps its durable state in two places, both set in the instance’s backend secret:
- The metadata database (PostgreSQL,
metadata_backend_url). Itsconsensusschema records which blobs make up each shard’s current state, and itstsoracleschema holds the timestamp oracle. - The blob store (for example, S3,
persist_backend_url), which holds the data as immutable blobs.
Materialize finds its catalog shard from the instance’s environment ID, and the catalog records every other shard. The environment ID comes from your license key, so an instance finds its data only with the same license key and both URLs as the original. With a different environment ID, it starts a new, empty environment in the same database and bucket.
A backup is a copy of the metadata database. Restoring it rolls consensus back
to a state that references blobs Materialize has since compacted away. Object
versioning keeps those deleted blobs as noncurrent versions, and
persistcli admin restore-blob brings them back. Everything written after the
backup is lost. See Data loss and upstream systems.
Prerequisites
Blob store versioning
Turn on object versioning for the persist bucket before Materialize first starts. Versioning only protects blobs deleted after you turn it on. Then set these lifecycle rules:
| Rule | Recommendation | Why |
|---|---|---|
| Expire noncurrent versions | Backup retention plus at least 7 days, for example 14 days for 7 days of backups. | A restore needs every blob its backup references. The margin covers the time to notice a problem and carry out the restore. |
| Remove expired delete markers | Enable (ExpiredObjectDeleteMarker on S3). |
Once the noncurrent versions expire, the leftover delete markers only slow down listings. |
| Abort incomplete multipart uploads | After 1 day. | Interrupted uploads otherwise keep their parts forever. |
| Expire current versions | Never. | Materialize deletes its own blobs. Expiring a current version deletes live data. |
| Transition to colder storage classes | Don’t. | Materialize reads every live blob on restart, so colder classes only add retrieval fees and latency. |
Noncurrent versions cost storage in proportion to how much data Materialize compacts away per day. Watch their size, and size the retention window with that cost in mind.
The AWS storage module of the Materialize Terraform modules
turns on versioning by default. It expires noncurrent versions after 30 days,
which covers the database module’s default of 7 days of backups. If you
raise the backup retention past about 23 days, raise the storage module’s
noncurrent_version_expiration_days with it.
Versioning doesn’t protect against losing the bucket or the account. For that, replicate the bucket to another region or account.
Metadata database backups
Any PostgreSQL backup method that produces a consistent copy works, and you don’t need to stop Materialize. We recommend:
- Continuous backups with point-in-time recovery, kept 7 to 14 days. On
RDS, this is
backup_retention_periodin the Terraformdatabasemodule (default 7 days). - An on-demand backup before every upgrade or other planned change, such as
an RDS snapshot or a
pg_dump.
Without point-in-time recovery, take an hourly pg_dump and keep the dumps for
7 to 14 days. The database holds only metadata, so dumps are small:
pg_dump --format=custom --file=materialize-$(date -u +%Y%m%dT%H%M%SZ).dump \
"$METADATA_BACKEND_URL"
Use pg_dump and pg_restore from the server’s major version. A newer
pg_restore can emit settings that an older server rejects, for example
SET transaction_timeout against PostgreSQL 16 or older.
Record which Materialize version was running when each backup was taken, for example in the dump’s file name or the snapshot’s tags. For point-in-time recovery, keep a log of when each upgrade finished. A restore must run at the backup’s version (see step 3), not at the version running when you restore. Don’t pick a restore point during an upgrade.
Deployment configuration
A redeploy comes back as the same environment, on the same data, only if it points at that data exactly as before. Keep the following outside the Kubernetes cluster, for example in a secret manager:
- The backend secret:
metadata_backend_url,persist_backend_url, andlicense_key. The license key sets the environment ID. The path inpersist_backend_urlis part of the location: the same bucket under another path holds no data. - The Materialize version (
spec.environmentdImageRef) at the time of each backup. Restoring a backup starts Materialize at that version.
spec.environmentId, and set it on the restored instance.
Restore role
restore-blob restores an S3 blob by deleting its delete marker. That needs
s3:DeleteObjectVersion, which can permanently delete data. Don’t give it to
Materialize’s role. Instead, give it to a separate service account in its own
namespace.
Create an IAM role with this policy. <prefix> is the path of
persist_backend_url; in the Terraform examples, it is
system:serviceaccount:<namespace>:<instance name>.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:DeleteObjectVersion"],
"Resource": ["arn:aws:s3:::<bucket>/<prefix>/*"]
},
{
"Effect": "Allow",
"Action": ["s3:ListBucket", "s3:ListBucketVersions"],
"Resource": ["arn:aws:s3:::<bucket>"],
"Condition": {"StringLike": {"s3:prefix": ["<prefix>/*"]}}
}
]
}
Let the cluster’s OIDC provider assume the role for the subject
system:serviceaccount:persist-restore:persist-restore, then create the
namespace and service account:
kubectl create namespace persist-restore
kubectl label namespace persist-restore pod-security.kubernetes.io/enforce=restricted
kubectl -n persist-restore create serviceaccount persist-restore
kubectl -n persist-restore annotate serviceaccount persist-restore \
eks.amazonaws.com/role-arn=<restore role ARN>
Restore procedure
Finish each step before starting the next. In particular, Materialize must not run between restoring the database and restoring the blobs.
MZ_NAMESPACE=materialize-environment # the Materialize instance's namespace
MZ_NAME=main # the Materialize resource's name
Step 1. Stop Materialize
-
Save the
Materializeresource, and note its image, backend secret name, and active generation:kubectl -n $MZ_NAMESPACE get materialize $MZ_NAME -o yaml > materialize.yaml kubectl -n $MZ_NAMESPACE get materialize $MZ_NAME \ -o jsonpath='{.spec.environmentdImageRef}{"\n"}{.spec.backendSecretName}{"\n"}{.status.activeGeneration}{"\n"}'environmentdImageRefis the version running now. If you upgraded after the backup, it is newer than the backup’s version. You need the active generation in step 4. -
Delete the
Materializeresource. This stops environmentd, every cluster replica, balancerd, and the console, and leaves the backend secret and the data alone:kubectl -n $MZ_NAMESPACE delete materialize $MZ_NAME --wait=trueWith Terraform, destroy only the
Materializeresource:terraform destroy \ -target='module.materialize_instance.kubectl_manifest.materialize_instance'Don’t remove the whole module (for example, with
count = 0). That also deletes the backend secret, the network policies, and possibly the namespace. -
Wait until
kubectl -n $MZ_NAMESPACE get podsshows no environmentd or clusterd pods. Cluster replicas also write to the blob store and consensus.
Step 2. Restore Metadata database
Managed backups restore a whole server, and except for a Cloud SQL backup
restored in place, they restore into a new server. That works when the server
holds only Materialize’s database, as the Terraform database modules create
it. If the server is shared with other databases, restore into a new server,
pg_dump Materialize’s database from it, and load the dump into the original
server as on the pg_dump tab.
From another database on the same server (for example, postgres), recreate
Materialize’s database:
DROP DATABASE materialize WITH (FORCE);
CREATE DATABASE materialize OWNER materialize;
Then load the dump as the user that Materialize connects as:
pg_restore --no-owner --dbname="$METADATA_BACKEND_URL" materialize-<timestamp>.dump
List the point-in-time restore window and the snapshots:
aws rds describe-db-instance-automated-backups --db-instance-identifier <instance> \
--query 'DBInstanceAutomatedBackups[].RestoreWindow'
aws rds describe-db-snapshots --db-instance-identifier <instance> \
--query 'DBSnapshots[].[DBSnapshotIdentifier,SnapshotCreateTime,SnapshotType]' \
--output table
Restore into a new instance, to a point in time or from a snapshot. Pass the source’s subnet group, security groups, and parameter group; otherwise RDS uses the defaults, and Materialize may not be able to reach the new instance.
aws rds restore-db-instance-to-point-in-time \
--source-db-instance-identifier <instance> \
--target-db-instance-identifier <new instance> \
--restore-time <YYYY-MM-DDTHH:MM:SSZ> \
--db-subnet-group-name <subnet group> \
--vpc-security-group-ids <security group> \
--db-parameter-group-name <parameter group>
aws rds restore-db-instance-from-db-snapshot \
--db-snapshot-identifier <snapshot> \
--db-instance-identifier <new instance> \
--db-subnet-group-name <subnet group> \
--vpc-security-group-ids <security group> \
--db-parameter-group-name <parameter group>
aws rds wait db-instance-available --db-instance-identifier <new instance>
aws rds describe-db-instances --db-instance-identifier <new instance> \
--query 'DBInstances[0].Endpoint.Address' --output text
List the backups, and the latest point you can restore to:
gcloud sql backups list --instance=<instance>
gcloud sql instances get-latest-recovery-time <instance>
Restore to a point in time by cloning into a new instance:
gcloud sql instances clone <instance> <new instance> \
--point-in-time=<YYYY-MM-DDTHH:MM:SS.000Z>
gcloud sql instances describe <new instance> --format='value(ipAddresses[0].ipAddress)'
Or restore a backup over the existing instance. This replaces every database
on it, and keeps its address, so metadata_backend_url doesn’t change:
gcloud sql backups restore <backup ID> --restore-instance=<instance>
List the backups, and the earliest point you can restore to:
az postgres flexible-server backup list --resource-group <group> --server-name <server> \
--output table
az postgres flexible-server show --resource-group <group> --name <server> \
--query backup.earliestRestoreDate
Restore to a point in time into a new server. For a server with private
access, pass --subnet and --private-dns-zone to place the new server where
Materialize can reach it.
az postgres flexible-server restore --resource-group <group> \
--name <new server> --source-server <server> \
--restore-time <YYYY-MM-DDTHH:MM:SSZ>
az postgres flexible-server show --resource-group <group> --name <new server> \
--query fullyQualifiedDomainName --output tsv
If the database moved to a new server, point metadata_backend_url in the
backend secret at it before step 3. The user and password are the same as on
the source.
With Terraform, the new server has a different name from the one in your
configuration. On RDS, you can rename the instances so that the restored one
takes the original’s name and endpoint, and metadata_backend_url doesn’t
change. Then swap it into Terraform’s state:
aws rds modify-db-instance --db-instance-identifier <instance> \
--new-db-instance-identifier <instance>-old --apply-immediately
aws rds wait db-instance-available --db-instance-identifier <instance>-old
aws rds modify-db-instance --db-instance-identifier <new instance> \
--new-db-instance-identifier <instance> --apply-immediately
aws rds wait db-instance-available --db-instance-identifier <instance>
terraform state rm 'module.database.module.db.module.db_instance.aws_db_instance.this[0]'
terraform import 'module.database.module.db.module.db_instance.aws_db_instance.this[0]' <instance>
terraform plan # Check that the plan doesn't replace the instance.
Delete <instance>-old once you’ve verified the restore. Otherwise, update the
module inputs that build metadata_backend_url, and run only
terraform apply -target='module.materialize_instance.kubernetes_secret.materialize_backend'.
A full terraform apply would start Materialize too early.
The user in metadata_backend_url must own the restored tables. If another
user ran the restore, run REASSIGN OWNED BY <restoring user> TO <Materialize's user>; in the restored database.
Step 3. Restore Blobs
restore-blob brings back every blob that consensus references. It ships in
the materialize/jobs image.
materialize/jobs version must exactly match the Materialize version that
was running when the backup was taken. That isn’t necessarily the version in
environmentdImageRef now. An older version can’t read the restored data.
-
Build the consensus URI from
metadata_backend_url: replace anyoptionsparameter withoptions=--search_path=consensus. For example:postgres://materialize:<password>@<host>:5432/materialize?sslmode=require&options=--search_path=consensus -
Store it and
persist_backend_urlin a secret. To keep the password out of your shell history, copy both values from the backend secret into files, and edit the consensus URI in its file:umask 077 kubectl -n $MZ_NAMESPACE get secret <backend secret> \ -o jsonpath='{.data.persist_backend_url}' | base64 -d > blob-uri kubectl -n $MZ_NAMESPACE get secret <backend secret> \ -o jsonpath='{.data.metadata_backend_url}' | base64 -d > consensus-uri $EDITOR consensus-uri kubectl -n persist-restore create secret generic persist-restore \ --from-file=CONSENSUS_URI=consensus-uri --from-file=BLOB_URI=blob-uri rm blob-uri consensus-uri -
Save this Job as
persist-restore.yaml, with<version>set to the backup’s version. For example, if the backup was taken whilematerialize/environmentd:v26.1.0was running, usematerialize/jobs:v26.1.0:apiVersion: batch/v1 kind: Job metadata: generateName: persist-restore- namespace: persist-restore spec: backoffLimit: 0 activeDeadlineSeconds: 3600 ttlSecondsAfterFinished: 86400 template: spec: serviceAccountName: persist-restore restartPolicy: Never securityContext: runAsNonRoot: true runAsUser: 65534 seccompProfile: type: RuntimeDefault containers: - name: persist-restore image: materialize/jobs:<version> # The image's entrypoint is persistcli. `--commit` belongs to # `admin`, so it goes before the subcommand. args: - admin - --commit - restore-blob - --blob-uri=$(BLOB_URI) - --consensus-uri=$(CONSENSUS_URI) envFrom: - secretRef: name: persist-restore resources: requests: cpu: "1" memory: 4Gi limits: memory: 4Gi securityContext: allowPrivilegeEscalation: false capabilities: drop: ["ALL"]Keep
--commit. Without it,restore-blobfails withfetching just-restored rollupat the first missing blob, so it doesn’t work as a dry run. -
Run the Job and follow its logs:
JOB=$(kubectl create -f persist-restore.yaml -o name) kubectl -n persist-restore wait --for=condition=ready --timeout=10m \ pod -l batch.kubernetes.io/job-name=${JOB#job.batch/} kubectl -n persist-restore logs -f $JOB kubectl -n persist-restore get $JOB # COMPLETIONS 1/1 means it succeededIf it fails with
referenced blobs were not restored, the backup is probably older than the bucket’s noncurrent-version expiry, and those blobs are gone. Restore a newer backup instead. -
Delete the secret:
kubectl -n persist-restore delete secret persist-restore.
Step 4. Start Materialize
Edit the saved materialize.yaml:
- Remove
status, and themetadatafields that Kubernetes fills in:uid,resourceVersion,creationTimestamp,generation,managedFields,finalizers, and thekubectl.kubernetes.io/last-applied-configurationannotation. - Set
spec.environmentdImageRefto the backup’s version. - Add the annotation
materialize.cloud/last-known-active-generation, set to the active generation from step 1. Without it, the operator deploys generation 1, which the restored catalog has already fenced out, and environmentd exits withFence(DeployGeneration { ... })on every start.
metadata:
annotations:
materialize.cloud/last-known-active-generation: "<active generation>"
Then create it:
kubectl create -f materialize.yaml
The operator reads the annotation only when it creates the resource’s status, so it must be in the manifest. Adding it afterward has no effect.
With Terraform, set the materialize-instance module’s annotations and
environmentd_version instead, then run terraform apply:
module "materialize_instance" {
# ...
environmentd_version = "<backup's version>"
annotations = {
"materialize.cloud/last-known-active-generation" = "<active generation>"
}
}
You can leave the annotation set afterward; the operator ignores it once the resource exists.
Upgrade again, if you need to, only after you verify the restore.
The operator prefixes pod and service names with a random ID that it generates for each new resource, so the services come back under new names. Update anything that refers to them by name, such as an Ingress, a load balancer target, or a port-forward.
Step 5. Verification
Once environmentd is ready, check that the data from the time of the backup is there, and that sources are making progress:
SELECT count(*) FROM <a table you know>;
SELECT * FROM mz_internal.mz_source_statuses;
Data loss and upstream systems
- Writes after the backup are lost, including table rows and DDL. Rerun any DDL you still want.
- Sources resume from their position at the time of the backup. A source whose upstream still has that position, such as a Kafka topic that still holds those offsets, catches up on its own. Others must be recreated, for example a PostgreSQL source whose replication slot has moved past it.
- Sinks may emit some records again, since they resume from their restored progress.