SQLite 백업과 복구: .backup, VACUUM INTO, Online API 정리

단순히 cp로 파일을 복사하면 안 되는 이유

SQLite 데이터베이스는 파일 하나로 구성돼 있어서 그냥 파일 복사로 백업하고 싶은 유혹이 생깁니다. 가끔은 잘 되기도 하지만, 안 되는 경우가 더 많습니다.

크게 두 가지 문제가 생길 수 있습니다.

• 복사하는 도중에 다른 커넥션이 쓰기 작업 중일 수 있습니다. 이 경우 복사된 파일에는 트랜잭션이 절반만 반영된 상태로 남아, 열자마자 손상된 DB 취급을 받게 됩니다.
• 요즘 대부분의 앱이 기본으로 쓰는 WAL 모드라면, 최근 변경 사항은 별도의 database.db-wal 파일에 들어 있습니다. 메인 파일만 복사하면 모르는 사이에 데이터를 잃게 됩니다.

다행히 SQLite는 이런 상황에 맞는 정식 도구를 제공합니다. 잠금 처리, WAL 내용 반영, 동시 쓰기 작업까지 알아서 처리해 주죠. cp 대신 이 도구들을 쓰는 게 정답입니다.

sqlite .backup 명령어로 백업하기

CLI에서 가장 빠르게 sqlite 백업을 만드는 방법은 도트 명령어인 .backup을 사용하는 것입니다:

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

이렇게 하면 app.db 전체가 backup.db로 그대로 복사됩니다. 다른 프로세스가 데이터베이스를 읽거나 쓰고 있어도 문제없이 동작하는데, 백업 API가 큰 락을 한 번에 거는 대신 작은 락을 여러 번 나눠 잡으면서 페이지를 점진적으로 복사하고, 복사 도중에 수정된 페이지는 다시 읽어오는 방식이기 때문입니다.

결과물은 그 자체로 완전히 사용 가능한 SQLite 데이터베이스 파일입니다. 다른 DB 파일과 똑같이 열면 됩니다:

sqlite3 backup.db
sqlite> .tables

셸 한 줄로 끝내는 것도 가능합니다. 사실 cron으로 돌리는 백업 스크립트는 대부분 이런 형태로 정리됩니다:

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

파일 하나를 받아서 파일 하나를 내보냅니다. 덤프 후 복원 같은 왕복 작업도 없고, SQL 파싱도 없습니다. 그저 스토리지 계층에서 페이지를 그대로 복사할 뿐입니다.

압축된 사본을 만드는 VACUUM INTO

VACUUM INTO는 비슷해 보이지만 쓰임새가 다릅니다. 데이터베이스를 새로 빌드한 사본을 새 파일에 기록하는 명령입니다:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('첫 번째'), ('두 번째'), ('세 번째');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT '백업 완료' AS status;

결과물은 논리적으로 동일한 데이터베이스지만, 처음부터 다시 쓴 파일이라고 보면 됩니다. 모든 페이지가 빈틈없이 채워져 있고, 단편화도 없고, 삭제된 행이 남긴 빈 페이지도 없죠. 그래서 백업 파일 크기가 가능한 한 작아집니다.

언제 어떤 방식을 쓰면 좋을까요:

• .backup — 일상적이고 자주 돌리는 백업에 적합합니다. 더 빠르고, 동시 쓰기 작업과도 잘 어울리며, 바이트 단위까지 그대로 복제됩니다.
• VACUUM INTO — 주기적인 스냅샷을 뜨면서 파일도 깔끔하게 최소 크기로 만들고 싶을 때 좋습니다. 다만 전체를 다시 쓰는 방식이라 더 느리고, 작업 동안 원본에 쓰기 락이 걸립니다.

두 방식 모두 곧바로 열어볼 수 있는 정상적인 .db 파일을 만들어 줍니다.

애플리케이션 코드에서 SQLite Online Backup API 사용하기

애플리케이션 안에서는 sqlite3 명령줄을 따로 호출할 일이 없습니다. 대신 드라이버가 제공하는 online backup API를 직접 호출하면 됩니다. Python 표준 라이브러리 sqlite3에서는 Connection.backup이 그 역할을 합니다:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

backup 메서드는 다른 연결이 작업을 계속하는 동안 source에서 dest로 페이지를 복사합니다. 큰 데이터베이스에서는 복사 속도를 조절하거나 진행 상황을 표시하기 위해 pages=로 청크 단위 복사를, progress=로 콜백을 받을 수 있어 유용합니다.

다른 언어의 드라이버 대부분도 동일한 C API(sqlite3_backup_init, _step, _finish)를 비슷한 이름으로 노출합니다. 흐름은 늘 같습니다. 소스를 열고, 대상을 열고, 페이지를 단계별로 복사한 뒤, 마무리합니다.

사용 중인 DB를 그대로 백업하기 (sqlite 핫 백업)

바로 이 지점이 SQLite가 조용히 빛나는 부분입니다. .backup 명령어와 sqlite online backup API 모두 _핫 백업_을 염두에 두고 설계됐습니다. 즉, 소스 데이터베이스가 백업 내내 열려 있고 활발히 사용돼도 괜찮습니다.

내부적으로는 이렇게 동작합니다:

1. 백업이 공유 잠금(shared lock)을 잡고 페이지 복사를 시작합니다.
2. 아직 복사되지 않은 페이지를 라이터(writer)가 수정하면, 백업이 이를 감지하고 해당 페이지를 다시 읽습니다.
3. 모든 페이지가 일관된 상태가 되면 복사가 완료됩니다.

앱을 멈출 필요도, 연결을 끊을 필요도, 점검 시간을 잡을 필요도 없습니다. 트래픽이 많은 DB라면 수렴하는 데 몇 사이클이 더 걸릴 수는 있지만, 결국 수렴합니다. 그렇게 만들어진 대상 파일은 특정 시점의 일관된 스냅샷이 됩니다.

한 가지만 짚자면, WAL 모드를 쓰고 있다면 WAL 파일이 무한정 커지지 않도록 가끔 PRAGMA wal_checkpoint(TRUNCATE);를 실행해 주세요. sqlite WAL 백업 자체는 WAL을 알아서 잘 처리하므로, 이건 그저 일반적인 WAL 관리 팁입니다.

sqlite 백업 복구하기

SQLite 데이터베이스 복구는 놀라울 정도로 시시한데, 그게 바로 핵심입니다. 백업 파일 자체가 곧 데이터베이스니까요. 사용하려면 그냥 열기만 하면 됩니다:

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

운영 중인 데이터베이스를 덮어쓰는 방식으로 복구하는 경우 — 예를 들어 데이터 유실 후 복원할 때 — 다음 순서를 지키는 게 안전합니다.

1. 해당 데이터베이스를 열고 있는 프로세스를 모두 중지합니다.
2. 기존의 app.db, app.db-wal, app.db-shm 파일을 삭제합니다. 이전 데이터베이스에서 남은 WAL/SHM 파일이 복원된 메인 파일과 섞이면 SQLite가 혼란을 일으킵니다.
3. 백업 파일을 제자리에 복사합니다: cp backup.db app.db.
4. 애플리케이션을 다시 시작합니다.

여기서 -wal, -shm 파일이 핵심입니다. 2단계를 건너뛰면 SQLite가 복원된 메인 파일 위에 오래된 WAL을 적용하려고 시도해서, 데이터가 손상되거나 이상하게 뒤섞인 결과가 나올 수 있습니다.

CLI 안에서는 .backup과 짝을 이루는 .restore 명령어도 사용할 수 있습니다.

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

연결된 데이터베이스의 내용을 backup.db의 내용으로 덮어쓰는 동작입니다. 동일한 online backup API를 반대 방향으로 사용하는 셈이죠.

.dump은 성격이 다른 도구입니다

오래된 튜토리얼을 보면 .dump에 대한 언급이 자주 등장합니다. 하지만 이건 앞서 말한 의미의 백업과는 다릅니다. .dump은 CREATE와 INSERT 구문으로 이루어진 SQL 텍스트 파일을 만들어 줍니다:

sqlite3 app.db .dump > app.sql

복원할 때는 이 SQL을 다시 실행하면 됩니다:

sqlite3 new.db < app.sql

이 방식은 SQLite 버전 간 마이그레이션, git에서 스키마 diff, 다른 DB 엔진으로 데이터 이전 같은 작업에 유용합니다. 다만 .backup보다 느리고 용량도 크며, 정보 손실도 있습니다(커스텀 collation, 생성 컬럼, 일부 pragma는 별도 처리가 필요할 수 있음). 실제로 운영 중인 데이터베이스를 백업할 목적이라면 .backup이나 VACUUM INTO를 쓰는 편이 낫습니다.

현실적인 SQLite 백업 루틴

대부분의 애플리케이션에서는 다음 조합이 잘 맞습니다.

• 정기 .backup 실행 — 매시간, 매일 등 데이터 유실 허용 범위에 맞춰 돌립니다. 가볍고 빠르며, DB를 멈추지 않는 핫 백업입니다.
• 주 1회 VACUUM INTO로 별도 경로에 스냅샷 생성. 누적된 드리프트를 잡아내고, 압축된 스냅샷을 확보하면서 다른 코드 경로도 한 번씩 거치게 됩니다.
• 보관 정책 정하기: 일일 백업은 최근 N개, 주간 백업은 최근 M개만 유지. SQLite DB는 압축이 잘 되니 gzip backup.db로 한 번 더 줄여두면 좋습니다.
• 가끔은 실제로 복구해보고 몇 가지 쿼리를 돌려보세요. 검증하지 않은 백업은 백업이 아니라 그냥 희망사항입니다.

# 毎日、cronで実行:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# 毎週:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

두 명령 모두 앱이 요청을 처리하는 도중에도 안전하게 실행할 수 있습니다.

다음: PRAGMA 설정

백업이 운영 측면의 한 축이라면, 런타임 동작을 튜닝하는 것은 또 다른 축입니다. SQLite는 PRAGMA 문을 통해 각종 설정값을 노출합니다. 저널 모드, 동기화 수준, 캐시 크기, 외래 키 제약 적용 여부 같은 것들이죠. 다음 페이지에서는 알아둘 만한 PRAGMA들을 하나씩 살펴봅니다.