| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>Database Remote-Copy Tool For SQLite</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
Database Remote-Copy Tool For SQLite |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div> |
| 126 |
<div class="fancy-toc1"><a href="#features">2. Features</a></div> |
| 127 |
<div class="fancy-toc1"><a href="#limitations">3. Limitations</a></div> |
| 128 |
<div class="fancy-toc1"><a href="#how_to_install">4. How To Install</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#backwards_compatibility_issues">4.1. Backwards-Compatibility Issues</a></div> |
| 130 |
<div class="fancy-toc1"><a href="#network_bandwidth">5. Network Bandwidth</a></div> |
| 131 |
<div class="fancy-toc1"><a href="#why_can_t_i_just_use_ordinary_rsync_">6. Why Can't I Just Use Ordinary rsync?</a></div> |
| 132 |
</div> |
| 133 |
</details> |
| 134 |
</div> |
| 135 |
|
| 136 |
|
| 137 |
|
| 138 |
|
| 139 |
<a name="intro"></a> |
| 140 |
|
| 141 |
<h1 id="overview"><span>1. </span>Overview</h1> |
| 142 |
|
| 143 |
<p>The following command causes REPLICA to become a copy of ORIGIN: |
| 144 |
|
| 145 |
</p><div class="codeblock"><pre>$ sqlite3_rsync ORIGIN REPLICA ?OPTIONS? |
| 146 |
</pre></div> |
| 147 |
|
| 148 |
<p>Use the <code>--help</code> or <code>-?</code> flag to see the |
| 149 |
complete list of options. Option flags may appear before, after, or |
| 150 |
between the ORIGIN and REPLICA arguments.</p> |
| 151 |
|
| 152 |
<p>Add the <code>-v</code> option to see more output, in a format |
| 153 |
similar to "rsync". |
| 154 |
|
| 155 |
</p><h1 id="features"><span>2. </span>Features</h1> |
| 156 |
|
| 157 |
<ol> |
| 158 |
|
| 159 |
<li>One or the other of ORIGIN or REPLICA may be of the form |
| 160 |
"USER@HOST:PATH". The other is just a simple PATH. |
| 161 |
This utility causes REPLICA to become a copy of ORIGIN. |
| 162 |
|
| 163 |
<ol type="a"> |
| 164 |
<li><p>If REPLICA does not already exist, it is created.</p></li> |
| 165 |
|
| 166 |
<li><p><a href="https://en.wikipedia.org/wiki/Secure_Shell">ssh</a> |
| 167 |
is used for communication, so "USER@HOST" may be an SSH |
| 168 |
alias.</p></li> |
| 169 |
|
| 170 |
<li><p>It is not required that one of ORIGIN or REPLICA be remote. |
| 171 |
The sqlite3_rsync utility works fine if both ORIGIN and REPLICA are |
| 172 |
local. |
| 173 |
|
| 174 |
</p></li></ol> |
| 175 |
</li> |
| 176 |
|
| 177 |
<li><p>Both databases may be "live" while this utility is |
| 178 |
running. Other programs can have active connections to the |
| 179 |
databases on either end while this utility is running. Other |
| 180 |
programs can write to ORIGIN and can read from REPLICA while |
| 181 |
this utility runs. |
| 182 |
</p><p> |
| 183 |
REPLICA becomes a copy of a snapshot of ORIGIN as it existed when the |
| 184 |
sqlite3_rsync command started. If other processes change the content |
| 185 |
of ORIGIN while this command is running, those changes will be applied |
| 186 |
to ORIGIN, but they are not transferred to REPLICA |
| 187 |
Thus, REPLICA ends up as a fully-consistent snapshot of ORIGIN |
| 188 |
at an instant in time. |
| 189 |
</p></li> |
| 190 |
|
| 191 |
<li><p>The synchronization uses a bandwidth-efficient |
| 192 |
protocol, similar to <a href="https://rsync.samba.org">rsync</a> |
| 193 |
(from which its name is derived).</p></li> |
| 194 |
|
| 195 |
</ol> |
| 196 |
|
| 197 |
<h1 id="limitations"><span>3. </span>Limitations</h1> |
| 198 |
|
| 199 |
<ol> |
| 200 |
|
| 201 |
<li><p></p><s>The database files must both be in <a href="wal.html">WAL</a> mode, and must have the |
| 202 |
same page-size.</s> ← These limitations were removed in |
| 203 |
version 3.50.0 (2025-05-29).</li> |
| 204 |
|
| 205 |
<li><p>While sqlite3_rsync is running, REPLICA is read-only. |
| 206 |
Queries can be run against REPLICA while this utility is running, |
| 207 |
just not write transactions.</p></li> |
| 208 |
|
| 209 |
<li><p>Only a single database is synchronized for each invocation of |
| 210 |
this utility. It is not (yet) possible to synchronize many different |
| 211 |
databases using wildcards, as it is with standard "rsync". |
| 212 |
|
| 213 |
</p></li><li><p>At least one of ORIGIN or REPLICA must be on the local machine. |
| 214 |
They cannot both be databases on other machines. |
| 215 |
|
| 216 |
</p></li><li><p>On the remote system, this utility must be installed in one of |
| 217 |
the directories in the default $PATH for SSH. The |
| 218 |
<code>/usr/local/bin</code> directory is often a good choice. |
| 219 |
Alternately, the <code>--exe |
| 220 |
NAME</code> flag may be used to specify a remote location for the |
| 221 |
binary, e.g. <code>--exe /opt/bin/sqlite3_rsync</code>.</p></li> |
| 222 |
|
| 223 |
<li><p>The replica will be a very close copy of the origin, but not |
| 224 |
an exact copy. All of the table (and index) content will be byte-for-byte |
| 225 |
identical in the replica. However, there can be some minor changes in |
| 226 |
the <a href="fileformat2.html#database_header">database header</a>. In particular, the replica will have the following |
| 227 |
differences from the origin: |
| 228 |
</p><ol type="a"> |
| 229 |
<li><p> |
| 230 |
The <a href="fileformat2.html#chngctr">change counter</a> in bytes 24 through 27 of the database header |
| 231 |
might be incremented in the replica. |
| 232 |
|
| 233 |
</p></li><li><p> |
| 234 |
The <a href="fileformat2.html#validfor">version-valid-for number</a> in bytes in 96 through 99 of the |
| 235 |
database header will be the SQLite version number of the |
| 236 |
sqlite3_rsync program that made the copy, not the version number of |
| 237 |
the last writer to the origin database. |
| 238 |
</p></li></ol> |
| 239 |
|
| 240 |
</li><li><p>On Windows, a single-letter HOST without a USER@ prefix will |
| 241 |
be interpreted as a Windows drive-letter, not as a hostname.</p></li> |
| 242 |
|
| 243 |
</ol> |
| 244 |
|
| 245 |
<h1 id="how_to_install"><span>4. </span>How To Install</h1> |
| 246 |
|
| 247 |
<p>Install sqlite3_rsync simply by putting the executable somewhere |
| 248 |
on your $PATH. If you are synchronizing with a remote system, the |
| 249 |
sqlite3_rsync executable must be installed on both the local and the |
| 250 |
remote system. When installing the sqlite3_rsync executable on the |
| 251 |
remote system, ensure that it is found on the $PATH used by SSH. |
| 252 |
Putting the sqlite3_rsync executable in the /usr/local/bin directory |
| 253 |
is often a good choice. |
| 254 |
|
| 255 |
</p><p>Unfortunately, on MacOS, the default PATH for ssh is |
| 256 |
"/usr/bin:/bin:/usr/sbin:/sbin" and MacOS does not allow you to |
| 257 |
add new programs to any of those directories. As a work-around, |
| 258 |
sqlite3_rsync will attempt to augment the PATH like this: |
| 259 |
</p><blockquote> |
| 260 |
PATH=$HOME/bin:/usr/local/bin:/opt/homebrew/bin:$PATH |
| 261 |
</blockquote> |
| 262 |
<p>So if you are trying to sync with a remote Mac, it should suffice |
| 263 |
to install the sqlite3_rsync binary in any of the three new PATH |
| 264 |
locations: |
| 265 |
</p><ul> |
| 266 |
<li> $HOME/bin |
| 267 |
</li><li> /usr/local/bin |
| 268 |
</li><li> /opt/homebrew/bin |
| 269 |
</li></ul> |
| 270 |
|
| 271 |
<p>If you need to install sqlite3_rsync in some other (non-standard) |
| 272 |
place on the remote machine, simply use the --exe option on the command |
| 273 |
line to specify its precise location. For example: |
| 274 |
|
| 275 |
</p><blockquote> |
| 276 |
sqlite3_rsync sample.db mac:sample.db --exe /some/weird/place/sqlite3_rsync |
| 277 |
</blockquote> |
| 278 |
|
| 279 |
<p>The writer of this document has never had any success in getting |
| 280 |
SSHD to run on Windows. Perhaps he will figure that out and be able to |
| 281 |
provide instructions for syncing a database to or from a remote Windows |
| 282 |
machine in a future release. |
| 283 |
|
| 284 |
</p><h2 id="backwards_compatibility_issues"><span>4.1. </span>Backwards-Compatibility Issues</h2> |
| 285 |
|
| 286 |
<p>The sqlite3_rsync program is designed to negotiate the details of |
| 287 |
the sync algorithm between the origin and the replica at startup, and |
| 288 |
to use the most advanced algorithm available on both sides. However, |
| 289 |
due to a bug in this algorithm negotiation logic (a missing fflush() |
| 290 |
call) it is possible for the application to hang if you try to use |
| 291 |
sqlite3_rsync with version 3.50.0 or later on the local side of the |
| 292 |
connection with version 3.49.1 or earlier on the remote side. |
| 293 |
Your best solution to |
| 294 |
this is to install the latest version of sqlite3_rsync on both sides. |
| 295 |
But if that is not possible, you can work around the problem by adding |
| 296 |
the "--protocol 1" option to the sqlite3_rsync command-line. |
| 297 |
|
| 298 |
</p><h1 id="network_bandwidth"><span>5. </span>Network Bandwidth</h1> |
| 299 |
|
| 300 |
<p>The core idea behind the protocol is that the replica sends |
| 301 |
cryptographic hashes for pages or groups of pages over to the |
| 302 |
origin side. The origin sends back page content that differs, or requests |
| 303 |
finer-grain hashes if a multi-page hash does not match. If the origin |
| 304 |
and replica start out being vastly different, the total network bandwidth |
| 305 |
can exceed the size of the entire database, due to the overhead of exchanging |
| 306 |
hashes, but the excess bandwidth is not great - a few percent at most. |
| 307 |
On the other hand, if the origin and replica start out being very |
| 308 |
similar (the usual case) then the total bandwidth is often less than |
| 309 |
0.01% of the database size. In tests, a 500MB database will typically |
| 310 |
synchronize with about 20KB of network traffic. |
| 311 |
|
| 312 |
</p><p>Prior to version 3.50.0 (2025-05-29), the protocol would only |
| 313 |
send hashes of individual pages, not groups of pages. The meant that the |
| 314 |
bandwidth requirements were usually at least about 0.5% of the database size, |
| 315 |
even if the two sides started out being identical. Version 3.50.0 and later |
| 316 |
is more bandwidth efficient if there are few differences between the origin |
| 317 |
and replica. However, both sides of the connection must have sqlite3_rsync |
| 318 |
version 3.50.0 or later installed or else the protocol falls back to the |
| 319 |
older and less bandwidth-efficient algorithm. |
| 320 |
|
| 321 |
</p><h1 id="why_can_t_i_just_use_ordinary_rsync_"><span>6. </span>Why Can't I Just Use Ordinary <tt>rsync</tt>?</h1> |
| 322 |
|
| 323 |
<p>Ordinary rsync does not understand SQLite transactions. Rsync will make a |
| 324 |
copy of ORIGIN into REPLICA, however the copy might not be consistent. Parts |
| 325 |
of the copy might be from one transaction, while other parts might be from |
| 326 |
a different transaction. The database copy might be corrupt. |
| 327 |
|
| 328 |
</p><p>If no other processes are connected to the database for the entire |
| 329 |
time that rsync is running, and if the database does not have a |
| 330 |
<a href="fileformat2.html#hotjrnl">hot journal</a>, then rsync will make a consistent copy of the database. |
| 331 |
But if you cannot guarantee that both of those conditions are met, then |
| 332 |
rsync might generate a corrupt copy. The sqlite3_rsync utility, on the |
| 333 |
other hand, always generates a consistent copy. |
| 334 |
</p><p align="center"><small><i>This page was last updated on 2025-11-13 07:12:58Z </small></i></p> |
| 335 |
|