feat(backup): add File > Backup Dump… and Restore Dump… for PostgreSQL (pg_dump / pg_restore)

[samirmhsnv]

Summary

Two new File menu items for PostgreSQL and Redshift connections:

• Backup Dump… — pick a database on the current connection, choose a destination, runs pg_dump -Fc (custom archive format) with live byte progress and cancel.
• Restore Dump… — pick a .dump file via the open panel, pick a target database on the current connection, runs pg_restore --no-owner --no-acl with indeterminate progress and cancel.

Both flows reuse the connection's active SSH tunnel via session.effectiveConnection, so tunneled connections work without spinning up a second port forward. Both honor user-set binary paths under Settings > Terminal > CLI Paths for pg_dump and pg_restore; if unset, paths are auto-detected from PATH and common Homebrew locations.

Notes for reviewers

• DatabaseSwitcherSheet gains a Mode enum with three cases: .switch (default), .backup, .restore. The existing switch-database call site stays on the default and is unchanged. In the two handoff modes the schemas tab and create/drop affordances are hidden, the primary button reads "Backup Dump…" or "Restore Dump…", and the callback does not auto-dismiss so the host flow can transition to the next step.
• DatabaseSwitcherViewModel now accepts an optional initialMode override. The two handoff modes force .database so PostgreSQL doesn't open the picker in schema mode (pg_dump / pg_restore operate on whole databases, not individual schemas).
• BackupProgressSheet and BackupResultSheet are shared between both flows via a Kind enum. Cancel alerts and titles switch per kind. The cancelled-restore result warns about partial database state.
• Both flows transition to the running progress sheet the moment the user clicks submit, before the service finishes locating the binary and spawning the process. Failures fall through to the failed state with the install/error message.
• ActiveSheet gains .backupDatabase and .restoreDatabase(fileURL: URL) (the latter carries the chosen file so the sheet can open directly on the database picker).
• MainContentCommandActions.backupDatabase() and .restoreDatabase() are the menu entry points. The restore action opens NSOpenPanel first as a sheet on the main window, then sets the active sheet — this keeps file selection out of the SwiftUI sheet stack.
• PostgresBackupService polls file size every 250ms for live byte progress. PostgresRestoreService is indeterminate (pg_restore has no usable progress hook for arbitrary archives). Cancel sends SIGTERM in both. Stderr is captured (capped at 64KB) and surfaced as the failure message.
• SSL mode raw values are mapped to libpq's PGSSLMODE values (require, verify-ca, verify-full, etc.) instead of being passed through verbatim. --no-password is passed to both tools so they fail fast with an auth error rather than hanging on a TTY prompt when PGPASSWORD is missing.
• pg_restore is invoked with --no-owner --no-acl to avoid spurious failures restoring as a different role from the dump owner. --clean is intentionally not used; restoring into an existing database with conflicting objects will produce stderr errors and a non-zero exit, surfaced in the result sheet.
• Headless xcodebuild from CLI fails on a SwiftLint plugin sandbox issue in CodeEditTextView/CodeEditSourceEditor (unrelated to this change). SwiftLint --strict is clean on every changed file. Verify the build from Xcode.

Test plan

Backup Dump

• Postgres connection without SSH: open Backup Dump…, pick a DB, save, verify the .dump file is a valid custom-format archive (pg_restore -l file.dump).
• Picker shows the database list (not schemas) and the title reads "Backup Dump".
• Progress sheet appears immediately after clicking Backup Dump…, before pg_dump byte count starts ticking.
• Postgres connection with SSH tunnel: same flow, verify it uses the existing tunnel (no second port forward in lsof).
• Cancel mid-backup: confirm the partial file is removed and the sheet shows "Backup Dump Cancelled".
• Custom pg_dump path: set under Settings > Terminal > CLI Paths > pg_dump, verify it is used.
• No pg_dump on system: backup fails with the install hint.
• Redshift connection: backup works (Redshift shares the PostgreSQL plugin).

Restore Dump

• Round-trip: dump a database, drop a row, restore the same file into the same database, verify the row is back.
• Restore into a fresh database: create an empty target DB, restore, verify objects appear.
• Picker shows the database list (not schemas) and the title reads "Restore Dump".
• Progress sheet appears immediately after clicking Restore Dump….
• SSH-tunneled connection: restore uses the existing tunnel.
• Cancel mid-restore: confirm the result sheet shows "Restore Dump Cancelled" with the partial-state warning.
• Custom pg_restore path: set under Settings > Terminal > CLI Paths > pg_restore, verify it is used.
• No pg_restore on system: restore fails with the install hint.
• Read-only safe mode: Restore Dump… menu item is disabled.

Both

• Non-Postgres connection (MySQL, etc.): both menu items are disabled.
• Disconnected: both menu items are disabled.
• Result detail line is center-aligned with the title in the success/failure sheets.

[samirmhsnv]

hey @datlechin please take a look when you available. thx

[datlechin]

Hello @samirmhsnv, thank you for contributing. This PR look great, could you add some screenshots about this feature for me preview first?

[samirmhsnv]

Hello @samirmhsnv, thank you for contributing. This PR look great, could you add some screenshots about this feature for me preview first?

Sure! Take a look screenshots below:

1. Two new options: Backup Dump and Restore Dump

2. Backup dump window:

3. Save Location popup

4. Progress bar & Complete message

5. Restore window to choose dump

6. Restore window to choose DB, with current one auto choosed

7. If any errors on restore:

[datlechin]

Thanks @samirmhsnv, few things blocking this:

• PostgresBackupService sits in Core/Database/ and hardcodes a check for postgres/redshift. That belongs in the postgres plugin. Add a backup method to PluginDatabaseDriver and move both files into Plugins/PostgreSQLDriverPlugin/.
• Backup and restore services are basically the same file twice. Same state shape, same error shape, same stderr cap, same SSL switch, same termination handler. Fold each pair into one type with a Kind, like you already did for the progress and result sheets.
• No tests. Both services are state machines, easy to test with an injected runner.
• Cancel alert button reads "Continue". Doesn't tell me what continues. Use "Keep Backing Up" / "Keep Restoring".
• NSOpenPanel for restore has no allowedContentTypes. Save panel filters to .dump, open panel should too. Also you say directory format is supported but canChooseDirectories = false.
• The two file panels live in different places. Restore shows NSOpenPanel before opening the sheet, backup shows NSSavePanel from inside the sheet. Same flow should feel the same way, pick one.
• Bug: connection.host.isEmpty ? "127.0.0.1" : effective.host checks the wrong side. Should be effective.host.isEmpty. Both services have it.
• Backup progress bar is indeterminate even though you have a live byte count. If you can pull pg_database_size up front, switch to a real progress bar.
• Stderr handler hops to MainActor per chunk. Accumulate off-actor, flush once in the termination handler.
  The rest (Process argv, --no-password, --no-owner --no-acl, SSL mapping, partial file cleanup) looks fine.

[samirmhsnv]

@datlechin Thanks for the review. So

• PostgresBackupService sits in Core/Database/ and hardcodes a check for postgres/redshift. That belongs in the postgres plugin. Add a backup method to PluginDatabaseDriver and move both files into Plugins/PostgreSQLDriverPlugin/.

Agreed in principle. Deferred to a follow up commit on this branch, the change requires adding a method to PluginDatabaseDriver, bumping currentPluginKitVersion 11→12, and updating TableProPluginKitVersion in ~14 plugin Info.plists (every bundled and externally distributed driver). That's mechanical but large enough that I'd rather not bundle it with the refactor below. Will push as a separate commit if you want it before merge; otherwise filing as a follow up.

• Backup and restore services are basically the same file twice. Same state shape, same error shape, same stderr cap, same SSL switch, same termination handler. Fold each pair into one type with a Kind, like you already did for the progress and result sheets.

Done. PostgresBackupService + PostgresRestoreService folded into one PostgresDumpService with a PostgresDumpKind enum. One state shape (PostgresDumpState), one error shape (PostgresDumpError), one stderr cap, one SSL switch, one termination handler. The two old files are deleted.

• No tests. Both services are state machines, easy to test with an injected runner.

Added TableProTests/Database/PostgresDumpServiceTests.swift, 6 command construction tests (-Fc/--no-owner shape, host fallback, username omission, password optionality, SSL mode mapping) and 5 state-machine tests (success, failure with stderr surfaced, cancel, double run guard, empty stderr fallback) using a FakeDumpRunner. To make the state machine testable, factored run(command:database:fileURL:totalBytesEstimate:) as a separate seam so tests skip singleton lookups.

• Cancel alert button reads "Continue". Doesn't tell me what continues. Use "Keep Backing Up" / "Keep Restoring".

Fixed. now reads "Keep Backing Up" / "Keep Restoring" via a per kind keepGoingLabel.

• NSOpenPanel for restore has no allowedContentTypes. Save panel filters to .dump, open panel should too. Also you say directory format is supported but canChooseDirectories = false.

Fixed. Open panel now sets allowedContentTypes = [UTType("dump"), .data]. Dropped the "directory format" wording from the message canChooseDirectories stays false and the message now reads "Select a backup file produced by pg_dump in custom archive format (.dump)."

• The two file panels live in different places. Restore shows NSOpenPanel before opening the sheet, backup shows NSSavePanel from inside the sheet. Same flow should feel the same way, pick one.

Fixed. Both flows now open their NSPanel as a sub sheet of the SwiftUI sheet:

Backup: sheet → DB picker → NSSavePanel as sub sheet → progress (unchanged)
Restore: sheet opens → NSOpenPanel as sub sheet on first appearance → source banner with a "Change…" link → DB picker → progress
Restore's pre sheet NSOpenPanel is gone; ActiveSheet.restoreDatabase no longer carries a fileURL.

• Bug: connection.host.isEmpty ? "127.0.0.1" : effective.host checks the wrong side. Should be effective.host.isEmpty. Both services have it.

Looked at this carefully, current code on both services already reads effective.host.isEmpty ? "127.0.0.1" : effective.host (both arms reference effective). There's a hostFallback test asserting this in the new suite. If you were looking at an earlier paste of the file, please point me at the line you saw and I'll recheck.

• Backup progress bar is indeterminate even though you have a live byte count. If you can pull pg_database_size up front, switch to a real progress bar.

Fixed. BackupDatabaseFlow queries SELECT pg_database_size(...) via the live driver before invoking the runner and passes the value through totalBytesEstimate. BackupProgressSheet renders a determinate ProgressView(value:) capped at 95% (the .dump custom format is heavily compressed so the bar would otherwise look "done" while pg_dump is still finalizing the trailer). Restore stays indeterminate as you'd expect.

• Stderr handler hops to MainActor per chunk. Accumulate off-actor, flush once in the termination handler.
  The rest (Process argv, --no-password, --no-owner --no-acl, SSL mapping, partial file cleanup) looks fine.

Fixed. The readabilityHandler now accumulates entirely inside the closure under NSLock, with no actor hop. The single MainActor handoff happens once on termination, carrying the final string back via withCheckedContinuation. The 64KB cap is applied off actor too.