Cách sao chép cơ sở dữ liệu SQLite giữa các máy tính nhanh hơn

• Giải thích một cách nhanh hơn để sao chép cơ sở dữ liệu SQLite giữa các máy tính
• Các chỉ mục của cơ sở dữ liệu là nguyên nhân chính làm chậm tốc độ sao chép
• Có thể dùng lệnh .dump của SQLite để xuất cơ sở dữ liệu thành tệp văn bản
• Tệp văn bản nhỏ hơn cơ sở dữ liệu gốc, và khi nén còn nhỏ hơn nữa
• Phương pháp này giúp sao chép các cơ sở dữ liệu lớn nhanh hơn và ổn định hơn

---

Cách sao chép cơ sở dữ liệu SQLite giữa các máy tính nhanh hơn

• Giải thích cách sao chép cơ sở dữ liệu SQLite được lưu trên máy chủ từ xa về máy tính cục bộ
• Ở giai đoạn đầu của dự án, có thể dùng lệnh rsync để sao chép một cách đơn giản
• Khi cơ sở dữ liệu lớn lên, tốc độ sao chép chậm đi và độ tin cậy cũng giảm

Tạo bản dump cơ sở dữ liệu thành tệp văn bản

• SQLite có thể dùng lệnh .dump để xuất cơ sở dữ liệu thành tệp văn bản
• Tệp văn bản này gồm các câu lệnh SQL và có thể nhỏ hơn cơ sở dữ liệu gốc
• Các chỉ mục được thu gọn thành một dòng duy nhất trong tệp văn bản, giúp tiết kiệm dung lượng lưu trữ

Tiết kiệm dung lượng nhờ nén

• Tệp văn bản sẽ nhỏ hơn nữa khi được nén
• Ví dụ, nếu cơ sở dữ liệu SQLite gốc có dung lượng 3.4GB, thì tệp văn bản được nén bằng gzip có thể giảm xuống còn 240MB
• Tải xuống tệp văn bản đã nén sẽ giúp việc sao chép cơ sở dữ liệu nhanh hơn rất nhiều

Lệnh ssh+rsync mới

• Tạo tệp văn bản đã nén bằng gzip trên máy chủ, sao chép về máy cục bộ rồi dựng lại cơ sở dữ liệu
• Tạo tệp văn bản nén trên máy chủ: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
• Sao chép tệp về máy cục bộ: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
• Giải nén và dựng lại cơ sở dữ liệu, sau đó xóa tệp cục bộ

Bản dump cơ sở dữ liệu là nguồn sao chép ổn định

• Nếu có cập nhật xảy ra trong lúc sao chép cơ sở dữ liệu, rsync có thể tạo ra tệp cơ sở dữ liệu không chính xác
• Có thể giải quyết vấn đề này bằng cách tạo bản dump văn bản để cung cấp một nguồn sao chép ổn định
• Phương pháp này giúp tiết kiệm thời gian khi làm việc với cơ sở dữ liệu lớn, đồng thời khiến việc tải xuống nhanh hơn và đáng tin cậy hơn

[GN⁺]

Ý kiến trên Hacker News

• SQLite cung cấp công cụ chính thức. Nó hoạt động ở cấp độ trang; bản sao gửi hàm băm mật mã của từng trang cho phía nguồn, và phía nguồn sẽ gửi lại toàn bộ nội dung của những trang có hàm băm không khớp
• Việc sao chép tệp cơ sở dữ liệu đang hoạt động có thể làm hỏng dữ liệu. Có thể dùng Litestream để sao chép an toàn
• Một cách để sao chép cơ sở dữ liệu giữa các máy tính là gửi phần đầu và bỏ qua phần còn lại
  • rsync gia tăng nhanh hơn, nhưng không đồng ý với nhận định rằng gửi câu lệnh SQL nhanh hơn gửi cơ sở dữ liệu. Cần thực thi câu lệnh SQL, rồi tối ưu hóa và chạy vacuum
  • Có những kịch bản phải "tái dựng gia tăng" cơ sở dữ liệu từ tệp CSV. Tạo lại cơ sở dữ liệu từ đầu thì tối ưu hơn, nhưng chỉ riêng việc chạy chèn hàng loạt vào một cơ sở dữ liệu trống trong bộ nhớ cũng đã mất 30 phút
• Tiện ích sqlite_rsync mới phát hành gần đây sử dụng một phiên bản thuật toán rsync được tối ưu cho cấu trúc nội bộ của cơ sở dữ liệu SQLite. Nó so sánh hiệu quả các trang dữ liệu bên trong và chỉ đồng bộ những trang bị thay đổi hoặc bị thiếu
• Lưu dưới dạng tệp văn bản là không hiệu quả. Dùng VACUUM INTO để lưu cơ sở dữ liệu sqlite
  • Lệnh VACUUM là một lựa chọn thay thế cho Backup API, trong đó cơ sở dữ liệu sao lưu kết quả có kích thước tối thiểu nên giảm I/O của hệ thống tệp
• Ngạc nhiên là không dùng tính năng nén do rsync cung cấp. Nén bằng gzip rồi truyền có thể nhanh hơn
• Với DuckDB, có thể xuất sang Parquet để giảm kích thước dữ liệu. Tốc độ truyền và tải cũng nhanh hơn
• SQLite cung cấp session extension để theo dõi thay đổi trong các bảng và tạo change set/patch set có thể vá các phiên bản trước của cơ sở dữ liệu SQLite
• Có thể tối ưu bằng tùy chọn --rsyncable của gzip. Nó làm giảm mức nén đôi chút nhưng khu trú khác biệt để không ảnh hưởng đến toàn bộ đầu ra đã nén
  • Có thể bỏ qua việc nén đầu ra dump và để rsync tính khác biệt giữa dump không nén trước đó với dump hiện tại, sau đó để rsync nén change set mà nó truyền qua mạng
• Có người từng phải gửi bản sao lưu đến nhiều máy bằng Postgres vào năm 2008. Mạng bị bão hòa nên đã dùng udpcast để gửi bản sao lưu tới mọi đích cùng lúc