วิธีคัดลอกฐานข้อมูล SQLite ระหว่างคอมพิวเตอร์ให้เร็วขึ้น

บทนำ

• หากคัดลอกฐานข้อมูล SQLite โดยตรงด้วย rsync ขนาดไฟล์อาจใหญ่ขึ้นจากสิ่งอย่างดัชนี ทำให้ช้าและไม่เสถียร
• จึงมีการเสนอวิธีใช้ .dump เพื่อบีบอัดและกู้คืนในรูปแบบข้อความ

เนื้อหา

• คำสั่ง .dump จะส่งออกทั้งฐานข้อมูลเป็นข้อความ SQL โดยดัชนีจะถูกแทนด้วยคำสั่งเพียงบรรทัดเดียว ทำให้ไฟล์มีขนาดเล็กลง

  sqlite3 my_database.db .dump > my_database.db.txt  
  

• ไฟล์ข้อความสามารถลดขนาดลงได้เพิ่มเติมเมื่อบีบอัดด้วย gzip:

  sqlite3 my_database.db .dump | gzip -c > my_database.db.txt.gz  
  

• เปลี่ยนมาใช้ขั้นตอนสร้างไฟล์บีบอัดบนเซิร์ฟเวอร์ คัดลอกมายังเครื่องโลคัล แล้วจึงกู้คืน:

  ssh username@server "sqlite3 db.db .dump | gzip -c > db.txt.gz"  
  rsync --progress username@server:db.txt.gz .  
  gunzip db.txt.gz  
  cat db.txt | sqlite3 restored.db  
  

• ไฟล์ฐานข้อมูลต้นฉบับ 3.4GB → ข้อความดัมพ์ 1.3GB → ไฟล์ที่บีบอัดด้วย gzip 240MB ลดลงประมาณ 14 เท่า

• วิธี rsync แบบเดิมอาจเกิดข้อผิดพลาด database disk image is malformed ได้ หากฐานข้อมูลมีการเปลี่ยนแปลงระหว่างการส่ง

• วิธีดัมพ์เป็นข้อความไม่มีความเสี่ยงที่เนื้อหาจะเปลี่ยนหลังเริ่มคัดลอก จึงสำรองข้อมูลได้อย่างสอดคล้องกัน

สรุป

• วิธี .dump + บีบอัด + กู้คืน ช่วยปรับปรุงทั้งความเร็วและความเสถียรในการส่ง SQLite ขนาดใหญ่
• มีประสิทธิภาพเป็นพิเศษกับฐานข้อมูลที่มีดัชนีจำนวนมาก และช่วยลดโอกาสการส่งล้มเหลวหรือข้อมูลเสียหาย
• หากต้องจัดการ SQLite ขนาดใหญ่อยู่บ่อย ๆ นี่เป็นวิธีปรับแต่งที่ใช้งานได้จริงและควรนำไปใช้

[ng0301]

สงสัยว่าทำไมถึงต้องทำงานแบบนี้ มีที่มาที่ไปอย่างไรบ้าง

[winterjung]

ต้นฉบับบอกว่าใช้สำหรับการสำรองข้อมูลและการวิเคราะห์ คิดว่าน่าจะเป็นเพราะอยากนำไปวิเคราะห์บนเครื่องโลคัลด้วยอะไรอย่าง duckdb มากกว่าล่ะมั้ง