SQLite VACUUM 명령어를 통하여 안전하게 DB 백업

Android POS/KIOSK를 개발하고 실제 운영하다보니 개발에서는 고려하지 못한 문제들이 한번씩 터지고, 그럴 때마다 데이터를 살리기 위한 DB 백업이 필요하다고 느꼈습니다.  

 

DB 백업이 있으면 어떤 시점이후 데이터는 날아갈 수 있어도, 그 시점 이전의 데이터는 모두 확정적으로 살릴 수 있기 때문입니다.

 

DB 백업이 없을 때의 데이터 복원:

1. 모든 LOG를 수집
2. LOG를 기반으로 모든 데이터를 수작업으로 복원

 

DB 백업이 있을 때의 데이터 복원:

1. DB 직전 백업 시점까지의 데이터는 DB 백업을 복원
2. 나머지 DB 백업 시점 이후의 데이터는 LOG를 이용하여 복원

 

문제가 터졌을 때 LOG를 모두 수집하여 수작업으로 복원하는데에는 엄청난 시간도 걸리고, 중간중간 복원하는 사람이 빼먹거나 하면 데이터가 없어진다는 명확한 문제가 있습니다. 하지만 DB 백업을 어떤 단위로 진행하게 된다면 그 DB 백업의 마지막 이후의 데이터만 수작업으로 복원하면 되기 때문에 복원하는 시간도 획기적으로 줄고 백업 시점 이전의 데이터를 잃어버릴 일도 없습니다.

이러한 명확한 장점이 있었기에 DB 백업 로직을 추가하기로 했습니다.

 

DB 백업시 고려해야 할 점

현재 제가 개발하고 있는 POS/KIOSK 프로젝트는 Windows와 Android를 모두 지원하기 때문에 각각 운영체제의 DB로 MSSql과 SQLite를 지원합니다.

Windows	Android
MSSql	SQLite

 

 

그래서 MSSql은 자동 백업 스케줄러와 트리거로 백업 로직을 구현하기로 하였습니다.

하지만 SQLite는 자동 백업 스케줄러가 따로 없으므로 방법을 고민하였습니다.

 

SQLite DB 백업 방법에 대한 고민

처음에는 그냥 단순히 File DB니까 DB File을 직접 복사해줄까 했습니다.

 

근데 DB File을 그냥 복사하자니 SQLite에서 쓰기 작업을 하는 도중에 복사하면 파일이 복사되는 중에 쓰기 작업 때문에 데이터가 잘못될 수 있는 위험이 있을 수 있다는 생각이 들었습니다.

 

그래서 SQLite에서 지원하는 방법이 없나 찾아보니 다른 DB에서는 없는 VACUUM 이라는 명령어를 찾았습니다.

 

https://sqlite.org/lang_vacuum.html

VACUUM

 

VACUUM

위에 첨부한 SQLite doc에 따르면 VACUUM 명령어는 DB 내부의 빈 공간을 최적화하는 명령어입니다.

VACUUM 명령어는 기본적으로 DB File에서 많은 데이터가 삭제되면 빈 공간이 남아 파편화되는데, 이 빈 공간들을 제거하고, 데이터를 파일에 재배치 하는 명령어입니다.

 

기본적인 VACUUM 명령어의 사용법 말고도 doc의 2.1을 보면, VACUUM에 INTO를 붙여 VACUUM INTO '[PATH]' 를 사용하면, 현재 사용 중인 DB를 스냅샷으로 찍어 새로운 파일로 저장합니다.

 

 

File 복사보다 VACUUM이 좋은 이유

File 복사에는 없는 VACUUM의 장점은 아래와 같습니다.

 

 

• Online Backup: 서비스 중단 없이 실시간 백업 가능.
• Single File: WAL 파일 등을 신경 쓸 필요 없이 깔끔한 .db 파일 하나로 병합해줌.
• Auto-Optimization: 백업과 동시에 파일 용량까지 최적화됨.

 

 

그래서 저는 위와 같은 장점이 있기에 VACUUM을 사용하여 Android 환경에서 DB를 백업하기로 하였습니다.

 

 

실제 적용 코드

저는 Android + Kotlin + Ktorm을 사용하고 있어서 해당 로직을 이런식으로 개발하였습니다.

/**
 * SQLite DB file 백업하는 로직.
 */
override suspend fun backup() {
    val rawUrl = database.url // jdbc:sqlite:/.../CROSS_POS_DB?journal_mode=WAL

    // SQLite DB 인지 먼저 체크
    if (!rawUrl.contains("jdbc:sqlite")) {
        logger.warn("Not a SQLite database. Skipping backup.")
        return
    }

    // 1. 순수 파일 경로 추출
    val purePath = rawUrl.removePrefix("jdbc:sqlite:").substringBefore("?")
    val originalDbFile = File(purePath)

    // 2. 백업 폴더 경로 설정 (원본 DB가 있는 폴더 아래 'backups' 폴더 생성)
    // 원본이 /Download/DB 라면 /Download/backups/ 가 됩니다.
    val backupDir = File(originalDbFile.parent, "backups")

    // 3. 폴더가 없으면 생성
    if (!backupDir.exists()) {
        val created = backupDir.mkdirs()
        logger.info("Db Backup directory created: $created at ${backupDir.absolutePath}")
    }

    // 4. 백업 파일명 생성 (예: CROSS_POS_DB_20260304_153000.db)
    val timestamp = SimpleDateFormat("yyyyMMdd_HHmmss", Locale.getDefault()).format(Date())
    val backupFile = File(backupDir, "${originalDbFile.name}_$timestamp.db")

    logger.info("DB BACKUP START! SOURCE=$purePath, TARGET=${backupFile.absolutePath}")

    // 5. 실행
    try {
        database.useConnection { connection ->
            if (backupFile.exists()) backupFile.delete()

            connection.prepareStatement("VACUUM INTO ?").use { stmt ->
                stmt.setString(1, backupFile.absolutePath)
                stmt.execute()
            }
        }
        logger.info("DB BACKUP SUCCESS: ${backupFile.absolutePath}")
    } catch (e: Exception) {
        logger.error("DB BACKUP FAILED", e)
    }
}