Mirror and sync

Both copy one database into another. The difference is what happens to the rows already in the destination — and it is the whole of the difference, so it is first.

Showing Linux. Only one path on this page changes with it — the temporary file --safe writes.

mirror — the destination ends up identical

Both commands are destructive to the destination, so both are typed rather than clicked, and neither will run unattended without --confirm:

--dry-run does all the reading and all the counting and stops where it would write. It is the cheapest way to find out what a copy is about to cost:

10,841 rows in, 1,001 rows going. That second number is the one to read — it is what the destination is about to lose, and mirror loses all of it, including the one row in this example that exists nowhere else.

It finishes by counting both sides, table by table, with exact count(*) — the same verification a restore does, for the same reason. A copy that finished is not the same statement as a copy that arrived.

sync — the destination ends up holding both

Same source, same destination, same starting state as the block above — and a different command. Watch what happens to the row that only staging has:

1 kept that the source has no row for is the sentence that separates these two commands.mirror called that row part of what the destination has now and removed it. sync counted it and left it alone.

The rules sync states as rules

A merge has more ways to go subtly wrong than a replacement does, so sloop is loud about each of them. Four of the five below are visible in the block above.

1. 1

   A primary key per table, or the table is skipped and named

   A merge has to know which destination row a source row replaces, and without a key there is no answer. sloop skips that table, says which one and why, and carries on with the rest rather than refusing the whole run.

2. 2

   Parents first, in foreign-key order

   Rows are copied into temporary tables and then upserted in dependency order, so a child row never arrives before the row it points at.

3. 3

   Sequences and identity columns are reset afterwards

   A merged table whose sequence still points at the destination’s old high-water mark hands out an id that already exists. sloop moves each one past the highest value in the table and prints where it now stands.

4. 4

   Rows the source has deleted are kept, and counted

   A merge adds and replaces; it does not delete. Rows that exist only in the destination stay, and sloop says how many — so a row that lingers is a row you were told about rather than one you find later.

5. 5

   Circular foreign keys are refused outright

   Two tables pointing at each other have no load order at all. sloop refuses the run rather than loading half of it and leaving the rest to fail.

The fifth is a refusal rather than a warning, because there is no half-right answer to give:

And the hint points at the command that can do it — mirror replaces the destination outright, so it has no load order to get right in the first place.

Copying part of a database

--table narrows either command to the tables you name. It is repeatable, it takes a schema in front, and it takes a glob:

Naming a table without naming what it points at does not work, and sloop says so before it touches anything rather than failing halfway through a load:

Note the second line of that block. A scoped mirror drops and recreates the tables you named and leaves the rest of the destination exactly as it is — so the destination does not end up identical to the source, which is the one thing an unscoped mirror guarantees. sloop says that out loud every time, because a flag that quietly changes what a command promises is worse than one that does not exist.

--safe, for a copy you would rather not have to start again

--safe dumps the source to a file before loading it, so a copy that fails partway can be retried from the dump instead of from the source. The trade is stated where it happens:

That file is not encrypted and sloop says so rather than leaving it to be discovered. It is removed once the counts agree, so a successful run leaves nothing behind; a failed one leaves the dump on purpose, which is the point of the flag.

Making the destination first

--create replaces --to when the destination does not exist yet. It makes the database and a role that owns it, registers the result, and copies into it in one run:

the destination is empty rather than replacing what the destination has now — there was nothing there a moment ago. The superuser password is used once and kept nowhere; the new role's password is the one that gets stored.

What both commands refuse

Two refusals, and both exist because the alternative is losing data quietly.

A mirror clears the destination first. If the destination is the source, that clears the source — so the check happens before anything is contacted.

Both commands are same-engine only. PostgreSQL to MySQL is refused rather than attempted, and the reason is in the message: the type systems do not map cleanly, and a copy that quietly rounds, truncates or reinterprets your data is worse than one that does not run. restore refuses across engines for the same reason — a dump is written in its own engine's dialect.

Picking one

If you want the destination to match the source, use mirror. If the destination has rows of its own that matter, use sync — and read the kept count it prints, because those are the rows the source would have removed.

Every flag either command takes is on the command reference, generated from --help.