I have a single CockroachDB cluster with multiple databases (staging and production) and separate S3 buckets for backups. It seems that external connections and backup schedules are cluster-scoped, not database-scoped, since running SHOW SCHEDULES from any database lists all schedules.
When running BACKUP INTO, does it back up the entire cluster, or do I need BACKUP DATABASE <database_name> INTO for database-specific backups? My current approach is to let CockroachDB handle daily cluster backups while adding more granular database-level backups (e.g., hourly for production). Is this a common strategy, or am I overcomplicating it? Additionally, what's the best way to decouple backup schedules for staging and production, given that production is more critical?
Your understanding is correct. BACKUP INTO at the cluster level indeed backs up the entire cluster, including all databases. If your goal is to have backups scoped specifically to a particular database (like staging or production), you need to explicitly specify BACKUP DATABASE <database_name> INTO. This ensures granular, targeted backups that isolate the specific data you care about, making restoration simpler and faster when you need to recover just one database.
Your current approach, daily cluster-wide backups plus hourly granular database-level backups for production is not uncommon and can actually be quite effective. This strategy provides both broad coverage (with daily backups to protect against large-scale failures) and rapid recovery (hourly backups) specifically for your more critical production database.
To further improve your strategy and effectively decouple staging and production backups, you can establish distinct backup schedules targeting separate storage locations (e.g., separate S3 buckets or different prefixes within a bucket). Having isolated schedules and destinations means your production backups remain unaffected by staging issues and vice versa. Clearly documenting and monitoring these schedules helps ensure you have reliable restoration points for each database environment, aligning backups with the business-criticality and recovery requirements of each database.