ป้องกันไว้ดีกว่าแก้ สำรองข้อมูลไว้ก่อนหาย (PostgreSQL)

หากกล่าวถึงสิ่งสำคัญในการพัฒนาแอปพลิเคชันสักหนึ่งแอป เพื่อน ๆ นึกถึงอะไรกันบ้าง อาจจะเป็นสถาปัตยกรรมของระบบ โครงสร้างฐานข้อมูล framework ที่เลือกใช้ในการพัฒนาระบบ หรืออื่น ๆ แต่สิ่งที่สำคัญไม่แพ้กันคือ ข้อมูล เพราะหากไม่มีข้อมูลแล้ว ระบบที่สร้างขึ้นมาก็ไม่มีความหมายอะไร แล้วจะเป็นยังไงถ้าข้อมูลที่เราใช้งานในระบบนั้นเกิดหายไป การกู้คืนอาจทำได้ยากถ้าหากไม่มีการวางแผนสำรองข้อมูลตั้งแต่ต้น ดังนั้นจะดีกว่าไหม ถ้าเราป้องกันไว้ดีกว่าแก้ มาสำรองข้อมูลไว้ก่อนหายกันเถอะ

“ข้อมูล” เป็นสิ่งสำคัญในทุกแอปพลิเคชัน ที่ควรป้องกันไว้ก่อนสูญหาย

บทความนี้จะแนะนำการสำรองและกู้คืนข้อมูลสำหรับ PostgreSQL และแสดงตัวอย่าง shell script เพื่อให้สามารถ backup ข้อมูลแบบอัตโนมัติได้ นอกจากนี้ยังแนะนำการ sync file เพื่อสำรองข้อมูลไว้อีกเครื่อง กรณีเครื่องหลักที่ใช้อยู่เกิดเหตุไม่คาดคิด พร้อมทั้งแสดงตัวอย่าง shell script สำหรับ sync file แบบอัตโนมัตินี้อีกด้วย

โดยเพื่อน ๆ สามารถต่อยอดนำ shell script เหล่านี้ไปตั้งกำหนดเวลาเพื่อให้ทำงานโดยอัตโนมัติได้ ผ่านโปรแกรม scheduling ต่าง ๆ อาทิ CRON สำหรับฐานข้อมูลอื่น ๆ เพื่อน ๆ สามารถนำแนวคิดนี้ไปปรับใช้ได้เช่นกัน หลังจากอ่านมาถึงตรงนี้ดูแล้วไม่ยากเลยใช่ไหมล่ะ มาลองทำกันเลย

การติดตั้ง PostgreSQL 12 พร้อม Client Tool บน Ubuntu 18.04/16.04 LTS

เริ่มต้นจากการติดตั้ง PostgreSQL 12 พร้อมกับ Client Tool เพื่อให้สามารถใช้งานคำสั่งในการสำรอง/กู้คืนข้อมูลได้ (รายละเอียดการติดตั้งสามารถอ่านเพิ่มเติมได้ที่ link นี้)

ขั้นตอนแรก อัปเดต system packages โดยรันคำสั่ง

sudo apt update
sudo apt -y install vim bash-completion wget
sudo apt -y upgrade

และ reboot เครื่อง

sudo reboot

ขั้นตอนที่สอง เพิ่ม PostgreSQL 12 repository

โดยทำการเพิ่ม GPG key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

และเพิ่ม repository ไปยัง sources list เพื่อให้ระบบ ubuntu 18.04/16.04 รู้จัก

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list

ขั้นตอนที่สาม ติดตั้ง PostgreSQL 12 และ Client Tool

sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12

ทำความรู้จัก คำสั่งที่ใช้ในการสำรองและกู้คืนข้อมูล

หลังจากติดตั้ง PostgreSQL Client Tool แล้ว เรามาดูคำสั่งที่ใช้ในการสำรองและกู้คืนข้อมูลกันต่อเลย

1 การสำรองข้อมูลในฐานข้อมูล PostgreSQL สามารถสำรองเก็บเป็นไฟล์ได้หลายประเภท เช่น .sql .backup .tar เป็นต้น สำหรับบทความนี้เราจะเลือกเก็บผลลัพธ์ไฟล์ประเภท .backup โดยใช้คำสั่ง pg_dump (เพื่อน ๆ สามารถอ่านรายละเอียดการตั้งค่าการสำรองข้อมูลเพิ่มเติมได้ที่ link นี้)

pg_dump -U [username] -d [dbname] -h [host] -p 5432 -n [schema] --format=c --encoding=UTF-8 --verbose -f [file.backup]

2 การกู้คืนข้อมูลในฐานข้อมูล PostgreSQL สำหรับไฟล์ .backup สามารถทำได้โดยใช้คำสั่ง pg_restore (เพื่อน ๆ สามารถอ่านรายละเอียดการตั้งค่าการกู้คืนข้อมูลเพิ่มเติมได้ที่ link นี้)

pg_restore -U [username] -d [dbname] -h [host] -p 5432 --verbose -f [file.backup]

ตัวอย่าง Shell Script สำหรับสำรองข้อมูล

เมื่อเราทดสอบรันคำสั่งสำหรับสำรองและกู้คืนข้อมูลว่าสามารถทำงานได้แล้ว ถัดไปจะเป็นการเขียน shell script เพื่อให้สามารถสำรองข้อมูลได้แบบอัตโนมัติ

#!/bin/bash
# This script will backup the postgresql database
# and store it in a specified directory

# Constants
USER="username"
DATABASE="dbname"
HOST="host"
BACKUP_DIRECTORY="/home/oper/pgbackup_test"

# Date stamp (formated YYYYMMDD)
# just used in file name
CURRENT_DATE=$(date "+%Y%m%d%H%M")

# Database named (command line argument) use pg_dump for backup
export PGPASSFILE='/home/oper/.pgpass'
cd /usr/lib/postgresql/12/bin
pg_dump -U $USER -d $DATABASE -h $HOST -p 5432  -n public --format=c --encoding=UTF-8 --verbose -f $BACKUP_DIRECTORY/$DATABASE_$CURRENT_DATE.backup
echo 'backup file finish'

# Cleanup old backups
find $BACKUP_DIRECTORY/* -mtime +7 -type f -exec rm {} ;
echo 'cleanup files older than 7 days finish'

ตัวอย่าง shell script (pg_backup_test.sh) สำหรับการสำรองข้อมูล PostgreSQL แบบอัตโนมัติ ประกอบด้วย 4 ส่วนหลัก ดังต่อไปนี้

ส่วนที่หนึ่ง กำหนดรายละเอียดของการเชื่อมต่อไปยัง PostgreSQL 

# Constants
USER="username"
DATABASE="dbname"
HOST="host"

และกำหนดที่อยู่ของไฟล์สำรอง รวมถึงตัวแปร CURRENT_DATE เพื่อใส่ Timestamp ลงไปในชื่อไฟล์สำรอง

BACKUP_DIRECTORY="/home/oper/pgbackup_test"

# Date stamp (formated YYYYMMDD)
# just used in file name
CURRENT_DATE=$(date "+%Y%m%d%H%M")

ส่วนที่สอง ทำการตั้งค่า environment variable ที่ชื่อ PGPASSFILE เพื่ออ้างอิง password จากไฟล์ .pgpass เพื่อทำให้ script สามารถเชื่อมต่อกับ PostgreSQL ได้แบบอัตโนมัติ โดยที่ไม่ต้องใส่ password

export PGPASSFILE='/home/oper/.pgpass'

ซึ่งไฟล์ .pgpass มีโครงสร้างเนื้อหาดังนี้ (สำหรับตัวอย่างการใช้งานไฟล์ .pgpass สามารถอ่านเพิ่มเติมได้ที่ link นี้)

host:5432:dbname:username:password

ส่วนที่สาม สั่งการสำรองข้อมูล PostgreSQL โดยเก็บผลลัพธ์เป็นไฟล์ประเภท .backup

cd /usr/lib/postgresql/12/bin
pg_dump -U $USER -d $DATABASE -h $HOST -p 5432  -n public --format=c --encoding=UTF-8 --verbose -f $BACKUP_DIRECTORY/$DATABASE_$CURRENT_DATE.backup
echo 'backup file finish'

ส่วนสุดท้าย ทำการลบไฟล์สำรองที่มีอายุเกิน 7 วัน เพื่อป้องกันไม่ให้ขนาดที่เก็บไฟล์สำรองมีพื้นที่เพิ่มขึ้นเรื่อย ๆ จนกินทรัพยากรของเครื่องจนเกินไป

# Cleanup old backups
find $BACKUP_DIRECTORY/* -mtime +7 -type f -exec rm {} ;
echo 'cleanup files older than 7 days finish'

ทำความรู้จัก rsync เครื่องมือที่ใช้สำหรับ sync ไฟล์ไปเก็บไว้อีกเครื่อง

ทีนี้เรามาดูการ sync ไฟล์ไปเก็บไว้ที่อีกเครื่อง เผื่อกรณีที่เครื่องหลักเกิดเหตุไม่คาดคิด ทำให้ไม่สามารถเข้าเครื่องได้ เราก็ยังสามารถเข้าถึงไฟล์สำรองได้จากอีกเครื่องหนึ่ง บทความนี้จะแนะนำการ sync ไฟล์โดยใช้เครื่องมือที่ชื่อว่า rsync ซึ่งจะ sync ไฟล์จากเครื่องหลัก (เครื่องที่หนึ่ง) ไปยังเครื่องสำรอง (เครื่องที่สอง) โดยที่สามารถกำหนดรูปแบบการ sync ได้หลากหลายวิธีขึ้นอยู่กับการตั้งค่าเพิ่มเติม ในตัวอย่างคำสั่ง rsync ที่นำเสนอเป็นรูปแบบการ sync โดยกำหนดให้เครื่องที่สองนั้นมีไฟล์และจำนวนไฟล์เท่ากันกับเครื่องแรก ยกตัวอย่างเช่น หากเครื่องที่หนึ่งมีการเพิ่มหรือลบไฟล์ เครื่องที่สองก็จะมีการเพิ่ม/ลบไฟล์ เช่นเดียวกับเครื่องที่หนึ่ง

การติดตั้ง rsync

เริ่มต้นใช้งาน rsync ด้วยการติดตั้งกันก่อนเลย ซึ่งการติดตั้ง rsync นั้นจะต้องติดตั้งทั้งเครื่องต้นทางและปลายทาง โดยใช้คำสั่ง

sudo apt install rsync

รูปแบบการใช้งาน rsync

สำหรับการใช้งานคำสั่ง rsync มีรูปแบบดังนี้

rsync [OPTION] [SOURCE] [DESTINATION]

ตัวอย่างการใช้งาน rsync ข้ามเครื่องโดยใช้ SSH key pair (ไม่ต้องระบุ password)

ในตัวอย่างนี้ เราจะทำการ sync ไฟล์จาก directory /home/oper/pgbackup_test บนเครื่อง local (เครื่องที่ 1) ไปยัง directory /home/oper/pgbackup_reserve_test บนเครื่อง remote (เครื่องที่ 2)

ขั้นตอนที่หนึ่ง ทดสอบใช้งานคำสั่ง rsync โดยยังต้องกรอก password เพื่อเข้าสู่เครื่อง remote

rsync -vrc --delete /home/oper/pgbackup_test/ [email protected]_host:/home/oper/pgbackup_reserve_test/

ตัวอย่างผลลัพธ์การ sync ข้อมูลสำเร็จ

ขั้นตอนที่สอง ทำการสร้าง SSH key pair  เพื่อให้สามารถเข้าถึงเครื่อง remote โดยที่ไม่ต้องใส่ password โดยใช้คำสั่ง

ssh-keygen
Enter passphrase (empty for no passphrase): **ไม่จำเป็นต้องระบุ สามารถ enter ไปได้เลย
$ Enter same passphrase again:

ผลลัพธ์ public key จะอยู่ที่ ~/.ssh/id_rsa.pub

จากนั้นให้ copy public key ดังกล่าวไปไว้ที่เครื่อง remote โดยใช้คำสั่ง

ssh-copy-id -i ~/.ssh/id_rsa.pub [email protected]_host

หลังจากเสร็จสิ้นขั้นตอนด้านบน หากทดสอบเชื่อมต่อไปยัง remote host จะสามารถเข้าถึงได้โดยไม่ต้องระบุ password

ssh [email protected]_host

ขั้นตอนที่สาม การใช้งานคำสั่ง rsync โดยที่ไม่ต้องใส่ password

rsync -vrc --delete -e "ssh -i ~/.ssh/id_rsa" /home/oper/pgbackup_test/ [email protected]_host: /home/oper/pgbackup_reserve_test/

โดยที่ -e "ssh -i ~/.ssh/id_rsa" เป็นการระบุ public key ที่เชื่อมต่อไปยังเครื่อง remote ทำให้เราไม่ต้องกรอก password นั่นเอง

ตัวอย่าง Shell Script สำหรับ sync ไฟล์ไปเก็บไว้อีกเครื่อง

ตัวอย่าง shell script (sync_backup_file_test.sh) สำหรับการ sync ไฟล์ไปยังเครื่อง remote ประกอบด้วย 2 ส่วน ดังต่อไปนี้

#!/bin/bash
# This script will sync backup file between server1 and server2

# Constants
SERVER1_BACKUP_DIRECTORY="/home/oper/pgbackup_test/"
SERVER2_BACKUP_DIRECTORY="/home/oper/pgbackup_reserve_test/"
SERVER2_USER="user"
SERVER2_HOST="remote_host"

sudo rsync -vrc --delete -e 'ssh -i /home/oper/.ssh/id_rsa' $SERVER1_BACKUP_DIRECTORY [email protected]$SERVER2_HOST:$SERVER2_BACKUP_DIRECTORY
echo 'sync backup file finish'

ส่วนที่หนึ่ง กำหนด directory ของเครื่องต้นทาง (SERVER1) และกำหนด user host และ directory ของเครื่องปลายทาง (SERVER2)

# Constants
SERVER1_BACKUP_DIRECTORY="/home/oper/pgbackup_test/"
SERVER2_BACKUP_DIRECTORY="/home/oper/pgbackup_reserve_test/"
SERVER2_USER="user"
SERVER2_HOST="remote_host"

ส่วนที่สอง สั่ง sync ไฟล์จากเครื่องต้นทางไปยังเครื่องปลายทาง

sudo rsync -vrc --delete -e 'ssh -i /home/oper/.ssh/id_rsa' $SERVER1_BACKUP_DIRECTORY [email protected]$SERVER2_HOST:$SERVER2_BACKUP_DIRECTORY
echo 'sync backup file finish'

การตั้งเวลาสำรองข้อมูลและ sync ไฟล์

สำหรับการสำรองข้อมูลและการ sync ไฟล์ไปเก็บไว้ที่อีกเครื่องนั้น เพื่อน ๆ สามารถตั้งกำหนดเวลาให้ทำการแบบอัตโนมัติได้โดยที่เราไม่ต้องมานั่งกดรัน
script เอง โดยการตั้งกำหนดการรัน shell script ผ่านโปรแกรม scheduling ต่าง ๆ อาทิ CRON สำหรับตัวอย่างการใช้งาน CRON สามารถอ่านเพิ่มเติมได้ที่ https://bigdata.go.th/big-data-101/scheduling-cron/

บทสรุป

บทความนี้แนะนำ Database Administrator มือใหม่ สำหรับการสำรองข้อมูล PostgreSQL และการ sync ไฟล์ไปเก็บไว้อีกเครื่องแบบอัตโนมัติ โดยสามารถนำ script ข้างต้นไปตั้งค่าเป็น cron job ได้ สำหรับฐานข้อมูลอื่น ๆ สามารถนำแนวคิดนี้ไปปรับใช้งานได้เช่นกัน หวังว่าบทความนี้จะช่วยให้เพื่อน ๆ ทำงานได้ง่ายและมีประสิทธิภาพมากขึ้นนะคะ

References

https://linuxize.com/post/how-to-use-rsync-for-local-and-remote-data-transfer-and-synchronization/

เนื้อหาโดย ฐิติรัตน์ บุญช่วยชู
ตรวจทานและปรับปรุงโดย ณัฐพัชร์ เศรษฐเสถียร