Backup and restore

Right-click a connection in the navigator and choose Backup… or Restore…. For PostgreSQL and MySQL, Selekta runs the database's own client tools installed on your Mac and shows their progress. SQLite needs no tools. Backup for SQL Server is not available yet.

What each database needs

The tools are not bundled with Selekta. Install them with Homebrew or another installer:

# PostgreSQL client tools
brew install libpq

# MySQL client tools
brew install mysql-client

For PostgreSQL you can also use the tools inside Postgres.app.

For SQL Server, the Backup tab offers Export as SQL… instead, which writes INSERT statements for one table at a time. See Export and import. For a full backup with schema and permissions, run BACKUP DATABASE on the server.

Where Selekta looks for the tools

When the Backup tab opens, Selekta searches for the tool in this order and uses the first one it finds:

1. The path you chose with Choose path… (the tool itself or its bin folder).
2. Every folder on your PATH.
3. /opt/homebrew/bin, /usr/local/bin, /opt/homebrew/opt/libpq/bin, /usr/local/opt/libpq/bin, /opt/homebrew/opt/mysql-client/bin, /usr/local/mysql/bin and /Applications/Postgres.app/Contents/Versions/latest/bin.
4. Homebrew versioned installs in /opt/homebrew/opt and /usr/local/opt (postgresql@…, libpq, mysql@…, mysql-client, mariadb), newest version first.

Apps on macOS start with a short PATH, so the fixed folders matter even when the tool works in Terminal. For MySQL and MariaDB, Selekta prefers the tool from the same vendor as the server.

Tool version

The PostgreSQL tool must be the same major version as the server or newer. pg_dump 14 cannot back up a PostgreSQL 16 server. The same check applies to MySQL tools against a MySQL server and to MariaDB tools against a MariaDB server; a tool from the other vendor is not compared. When mysqldump 8 or newer backs up MySQL 5.7 or MariaDB, Selekta adds --column-statistics=0 for you.

Back up a database

1. Right-click the connection and choose Backup…, or right-click a table and choose Backup table….
2. Scope: Whole database, Selected schemas (PostgreSQL) or Selected tables. The tree shows each table's size and the estimated total.
3. Options: choose the format, content and flags. The command preview shows exactly what will run; Copy command copies it.
4. Destination: the default is ~/Backups/<connection>/ with the connection, database, date and time in the file name. Change it or click Browse….
5. Click Start backup.

Options

MySQL backups always use --single-transaction --routines --triggers. mysqldump writes a DROP TABLE before each table by default.

The password is never part of the command. Selekta passes it to the tool through the environment (PGPASSWORD or MYSQL_PWD), read from the macOS Keychain. The tool connects the same way the connection does: through its SSH tunnel and with its TLS settings.

While a backup runs

The tab shows objects done, the current object, bytes written and the tool's log. Continue in background lets you keep working. Cancelling stops the tool and deletes the partial file. When it is done, Restore this backup… starts a restore of the same file.

If the role cannot read a table, the backup stops and names the table. Use Exclude to back up without it, or Edit connection role to use another role.

Restore a backup

1. Right-click the target connection and choose Restore…. It is disabled on read-only connections.
2. File: pick the Target connection, the Database and the Backup file.
3. Contents: for Custom and Directory backups, Selekta lists the backup's table of contents (pg_restore --list). Untick entries to restore only some of them. Plain SQL cannot be restored selectively.
4. Options: restore into the existing database, or Create new… with a new name. For PostgreSQL archives you can set Parallel jobs, Clean: drop objects first (--clean), If exists (--if-exists) and No owner (--no-owner). Single transaction (PostgreSQL) or Stop on first error (MySQL) makes the restore all or nothing.
5. Click Restore… and confirm.

Selekta reads the file to find its format: a pg_dump custom archive, a directory archive with toc.dat, a MySQL or MariaDB dump, a SQLite database, or plain SQL. A dump made for another engine is refused, for example "This is a MySQL dump; the target is PostgreSQL." Gzip files must be decompressed first.

A plain SQL backup into PostgreSQL is run by Selekta itself, statement by statement, in one transaction. If a statement fails, everything is rolled back.

If the target database does not exist, the restore stops before changing anything and offers Create … and retry. If the restore finished with errors, the tab lists the skipped statements and Copy errors copies them.

SQLite

For a SQLite connection, Backup… opens a single step. Back up to defaults to a backups folder next to the database file. Selekta copies the database itself, and the database stays usable while it copies.

Restore from file… replaces the database file with a backup:

1. Choose the backup file. Selekta checks that it is not damaged.
2. Confirm the replacement. On Production, type the connection name.
3. Selekta closes the open tabs on that connection, moves the current file and its -wal and -shm files to the Trash, puts the backup in its place and reopens the connection.

Apply or discard uncommitted changes in open tabs first: restore is disabled while any tab on the connection has them.

Related

• Export and import
• Production safety
• SSH and TLS
• Troubleshooting