Backup + restore (FK-aware)
Concept: Backup formats. This page is the operational walkthrough.
Take a backup
Section titled “Take a backup”⌘⇧B (or File → Backup database) opens the backup dialog
against the active session.
| Setting | Default | Notes |
|---|---|---|
| Output path | ~/Documents/Quay Backups/<connection>/<UTC stamp>.sql | Per-export overridable |
| Format | .sql (plaintext) | Switch to .qenc for encrypted (qenc concept) |
| Tables | All | Per-table opt-out checkboxes; a “select with prefix…” filter speeds bulk picks |
| Triggers / views / procedures / functions / events | Included | Per-object-type opt-out toggles |
| Engine clause / partitions | Preserved | Toggle off only if you’re moving to a target that doesn’t support them |
| AUTO_INCREMENT / sequence values | Preserved | Toggle to drop if the target should reset |
| Comments | Preserved | Toggle to drop |
| FK ordering | On | Off only as an override for diagnostic dumps |
The dialog shows a dependency graph preview before you run. Tables with red borders in the graph have unresolved FK references (usually a self-FK that needs the defer-FK pattern Quay applies automatically).
Click Run — progress bar shows tables emitted + bytes written. The dump finishes with a size summary + a “Reveal in Finder” button.
Restore preview
Section titled “Restore preview”Open any .sql file in Quay (drag-and-drop or File → Open) —
you don’t run it, you preview it. The restore preview parses the
file (handles DELIMITER //, dollar-quoted PG functions,
multi-statement triggers) and shows:
- Object inventory: 47 tables · 12 procedures · 1 trigger
- All FK targets resolved? ✅ / ❌ (with the unresolved list)
- DROP / TRUNCATE / DELETE statements highlighted in red
- Estimated time on this hardware
- Optional: row-count delta vs target if you point at a connection
To actually restore, drag the file onto a session in the rail or
click Restore here →. Quay opens a confirmation modal listing
exactly what will run, in what order, with the typed-name confirm
if the target is prod-tagged (Confirmation rules).
Conflict resolution on restore
Section titled “Conflict resolution on restore”If the target already has objects the dump declares, the restore modal shows per-object choices:
- ALTER target to match — schema diff merges
- Drop and recreate — the typical “wipe and reload”
- Skip — leave the existing object alone
- Rename incoming —
users→users_imported; useful for “compare and merge later”
A “remember for similar” checkbox applies the choice to other objects of the same kind in the same restore.
Streaming import for big dumps
Section titled “Streaming import for big dumps”Multi-GB .sql files: Quay’s importer streams 1 MB chunks. Memory
stays flat. The progress bar shows bytes parsed + statements
executed + skipped (with stop-on-error / collect-errors / per-batch-
checkpoint modes available in the import dialog).
For backups too big to fit on local disk: pipe directly from the
remote to a .qenc archive on a cloud backup destination — see
Cloud backup destinations.
Encrypted backups
Section titled “Encrypted backups”Toggle the format to .qenc. Quay suggests a strong random
passphrase; copy it before you forget. The output is a single
binary file that’s restoreable from any platform Quay supports —
see Encrypted exports concept.
Scheduling
Section titled “Scheduling”The same backup operation can be scheduled. Settings → Pro Plus → Scheduled backups sets up cron entries that fire while Quay is open; for 24/7 schedules install the Quay CLI as a launchd / systemd unit. See Scheduled backups.
Verifying the dump round-trips
Section titled “Verifying the dump round-trips”Optional opt-in: Verify after backup. Quay runs the dump back into a temporary in-memory SQLite (for SQLite source) or a fresh schema (for PG / MySQL) and compares table counts + a sample of rows. If anything mismatches, the backup is flagged + the “verify log” attached so you see exactly which row went wrong.
For most workflows this is overkill (Quay’s dump grammar has been exercised enough that round-trip failures are rare). Turn it on when:
- You’re producing the dump for an external partner
- This is the first dump you’re shipping for some new schema / edge case
- You’re chasing a heisenbug where you suspect the dumper itself
Quick reference: shortcuts
Section titled “Quick reference: shortcuts”| Shortcut | Action |
|---|---|
| ⌘⇧B | Backup current connection |
| ⌘⇧R | Open restore preview for an existing file |
| ⌘⇧S | ”Save buffer as backup” — emits the in-progress edits as a SQL diff before commit |