Backup dan Restore

Database SQLite hidup sebagai satu berkas biasa di disk, sehingga menggoda untuk diamankan dengan sekadar cp — padahal salinan yang dibuat saat database sedang ditulis bisa berisi halaman setengah jadi dan tidak bisa dibuka lagi. SQLite menyediakan tiga jalur backup yang benar: perintah .backup yang aman dijalankan sementara database dipakai, VACUUM INTO yang sekaligus memadatkan berkas, dan .dump yang menghasilkan berkas SQL berisi seluruh isi database. Bab ini membahas ketiganya beserta cara menguji bahwa backup benar-benar bisa dipulihkan.

.backup: Salinan Aman Saat Database Dipakai #

Kita siapkan database toko kecil sebagai bahan latihan:

CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  email TEXT NOT NULL
);
INSERT INTO pelanggan (nama, email) VALUES
  ('Budi', '[email protected]'),
  ('Siti', '[email protected]'),
  ('Ani', '[email protected]');

Buat berkas toko.db dari skrip tersebut, lalu kita uji skenario yang paling menakutkan bagi cp: koneksi lain sedang menahan transaksi tulis yang belum di-commit. Baris-baris berikut menahan transaksi tulis selama tiga detik tanpa output apa pun:

( echo "BEGIN IMMEDIATE;"; \
  echo "INSERT INTO pelanggan (nama, email) VALUES ('Rina','[email protected]');"; \
  sleep 3; \
  echo "COMMIT;" ) | sqlite3 toko.db &

Selama jendela tiga detik itu, jalankan backup dari koneksi kedua:

sqlite3 toko.db ".backup backup-toko.db"
wait

Perintah .backup selesai tanpa error dan tanpa menunggu si penulis. Setelah si penulis menyelesaikan COMMIT, kita bandingkan kedua berkas:

sqlite3 backup-toko.db "PRAGMA integrity_check; SELECT count(*) FROM pelanggan;"
sqlite3 toko.db "SELECT count(*) FROM pelanggan;"

Output yang Dihasilkan:

ok
3

4

Mari kita telaah hasil di atas:

1. Baris ok dan 3: salinan backup memuat keadaan terakhir yang sudah di-commit. Baris Rina yang belum di-commit tidak ikut tersalin, karena .backup membaca snapshot konsisten, bukan kondisi transaksi yang sedang berjalan.
2. Baris 4: database utama kini memiliki 4 baris setelah si penulis menyelesaikan COMMIT — data baru tetap tersimpan di berkas asli.

Di balik layar, .backup memakai Online Backup API milik SQLite: engine menyalin database halaman demi halaman, dan bila berkas sumber berubah di tengah proses, penyalinan diulang dari awal sampai didapat salinan yang konsisten. Inilah perbedaan mendasarnya dibanding menyalin berkas mentah.

Risiko Menyalin Berkas Secara Langsung #

Kita buktikan bahaya cp dengan mode WAL. Mulai dari berkas toko.db yang baru berisi tabel pelanggan kosong dalam mode WAL (penjelasan lengkapnya ada di chapter Journal Modes dan WAL):

rm -f toko.db salinan.db
sqlite3 toko.db "PRAGMA journal_mode = WAL;
CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  email TEXT NOT NULL
);"

Output yang Dihasilkan:

wal

Baris wal menandakan perpindahan mode berhasil. Selanjutnya jalankan penulis yang menahan koneksi tetap terbuka selama tiga detik:

( echo "INSERT INTO pelanggan (nama, email) VALUES
    ('Budi','[email protected]'),('Siti','[email protected]'),('Ani','[email protected]');"; \
  sleep 3 ) | sqlite3 toko.db &

Selama koneksi itu terbuka, periksa berkas yang ada di direktori kerja, lalu salin hanya berkas utamanya:

sleep 1
ls toko.db*
cp toko.db salinan.db
wait

Output yang Dihasilkan:

toko.db
toko.db-shm
toko.db-wal

Sekarang bandingkan isi berkas asli dengan salinan naif:

sqlite3 toko.db "SELECT count(*) FROM pelanggan;"
sqlite3 salinan.db "SELECT count(*) FROM pelanggan;"

Output yang Dihasilkan:

3

0

Berkas asli memiliki 3 baris, tetapi salinan hasil cp kosong. Ketiga baris baru masih berada di berkas sampingan toko.db-wal yang tidak ikut tersalin — cp hanya menyalin toko.db yang saat itu belum menerima perubahan.

.dump: Backup dalam Bentuk SQL #

.backup menghasilkan salinan biner yang hanya bisa dibaca SQLite. .dump menuliskan seluruh database sebagai skrip SQL yang bisa dibaca manusia, disimpan di version control, dan dipulihkan lintas versi SQLite:

sqlite3 toko.db .dump > dump-toko.sql
cat dump-toko.sql

Output yang Dihasilkan:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE pelanggan (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  email TEXT NOT NULL
);
INSERT INTO pelanggan VALUES(1,'Budi','[email protected]');
INSERT INTO pelanggan VALUES(2,'Siti','[email protected]');
INSERT INTO pelanggan VALUES(3,'Ani','[email protected]');
COMMIT;

Restore-nya cukup membaca skrip tersebut ke database baru:

sqlite3 pulih.db ".read dump-toko.sql"
sqlite3 pulih.db "SELECT count(*) FROM pelanggan;"

Output yang Dihasilkan:

3

Membandingkan Ketiga Metode #

Ketiga metode menghasilkan ukuran berkas yang berbeda pada database yang sama. Sebagai bahan uji, kita buat berkas baru arsip.db berisi tabel 50.000 baris lalu menghapus 45.000 baris — berkasnya tetap gemuk karena halaman kosong menunggu dipakai ulang:

CREATE TABLE log_pesanan (
  id INTEGER PRIMARY KEY,
  kode TEXT NOT NULL,
  payload TEXT NOT NULL
);
WITH RECURSIVE angka(x) AS (
  SELECT 1 UNION ALL SELECT x + 1 FROM angka WHERE x < 50000
)
INSERT INTO log_pesanan (kode, payload)
SELECT 'LOG-' || printf('%06d', x),
       printf('payload panjang nomor %d untuk mengisi halaman database dengan data', x)
FROM angka;
DELETE FROM log_pesanan WHERE id > 5000;

sqlite3 arsip.db ".backup backup-harian.db"
sqlite3 arsip.db "VACUUM INTO 'backup-vacuum.db';"
sqlite3 arsip.db .dump > dump-arsip.sql
ls -l arsip.db backup-harian.db backup-vacuum.db dump-arsip.sql | awk '{print $5, $9}'

Output yang Dihasilkan:

4640768 arsip.db
4640768 backup-harian.db
462848 backup-vacuum.db
612940 dump-arsip.sql

.backup menyalin halaman apa adanya sehingga ukurannya identik dengan aslinya. VACUUM INTO membangun ulang berkas tanpa halaman kosong sehingga hasilnya sepuluh kali lebih ramping (mekanismenya dibahas pada chapter VACUUM dan Maintenance Database), sedangkan .dump menghasilkan teks yang ukurannya bergantung pada panjang data.

Menguji dan Merotasi Backup #

Backup yang belum pernah diuji restore-nya bukan backup — hanya harapan. Minimal tiga langkah: pulihkan ke berkas sementara, periksa integritasnya, lalu bandingkan jumlah baris dengan database asal:

sqlite3 backup-harian.db "PRAGMA integrity_check; SELECT count(*) FROM log_pesanan;"

Output yang Dihasilkan:

ok
5000

Untuk arsip harian, sertakan tanggal pada nama berkas agar rotasi mudah dikelola — prefiks yang khusus membuat berkas backup lain dari demo di atas tidak ikut tampil:

sqlite3 toko.db ".backup snapshot-2025-06-15.db"
sqlite3 toko.db ".backup snapshot-2025-06-16.db"
ls -l snapshot-*.db | awk '{print $5, $9}'

Output yang Dihasilkan:

8192 snapshot-2025-06-15.db
8192 snapshot-2025-06-16.db