Postgresql Backup and Restore sử dụng pg_dump pg_dumpall

Việc sao lưu database PostgreSQL thường xuyên là điều cần thiết để đảm bảo bạn có thể khôi phục dữ liệu trong trường hợp xảy ra sự cố, chẳng hạn như lỗi phần cứng, lỗi phần mềm, xóa nhầm dữ liệu, hoặc tấn công bảo mật. Có nhiều phương pháp để sao lưu và restore dữ liệu nhưng cơ bản là 3 cách chính như sau:

• SQL dump
• File system level backup
• On-line backup

SQL Dump: sử dụng pg_dump để xuất ra file chứa các lệnh và dữ liệu để phục hồi lại database.

Backup dump: Login vào user postgres và sử chạy lệnh sau:

pg_dump: backup 1 database cụ thể được chỉ định

su postgres

pg_dump dbname > outfile

#dbname là tên CSDL cần backup, dump

#outfile là tên file hoặc đường dẫn đến file output

pg_dump duongdb > /pgbackup/backup_file.sql

Lệnh trên sẽ backup CSDL duongdb thành file backup_file nằm trong đường dẫn /pgbackup

Backup thành tar file:

pg_dump -Ft mydb > db.tar

pg_restore -d newdb db.tar

pg_dumpall: sử dụng để backup tất cả database trong cluster

pg_dumpall > outfile
pg_dumpall > /pgbackup/all_db_backup_file.sql

pg_dumpall --schema-only > output.sql

pg_dumpall --roles-only > output.sql

pg_dumpall --tablespaces-only > output.sql

Restore dump:

su postgres

psql dbname < infile
pg_restore -U postgres -d dvdrental /home/dvdrental.tar

# dbname là tên CSDL cần restore, chúng ta phải tạo CSDL trước, file dump chỉ có các lệnh sql tạo table, object mà thôi

#infile là file dump sql đã tạo ở bên trên.

Chúng ta cũng có thể thực hiện dump và restore trực tiếp từ 2 server cho nhau:

pg_dump -h host1 dbname | psql -h host2 dbname

# host1 dump sang host2 => dạng nhân bản database

Restore pg_dumpall: restore tất cả database được tạo ra bởi dumpall

psql -f infile postgres

Backup và restore database LỚN:

Nén file dump:

pg_dump dbname | gzip > filename.gz

pg_dump duongdb | gzip > /pgbackup/backup_file.sql.gz

Restore:

createdb dbname 
gunzip -c filename.gz | psql dbname
hoặc:

cat filename.gz | gunzip | psql dbname

Chia nhỏ file dump:

pg_dump dbname | split -b 1m -filename

Restore:
createdb dbname 
cat filename* | psql dbname

File system level backup

Là backup dạng copy toàn bộ thư mục data và nén lại, phải tắt DB đi trước khi thực hiện thì DB mới restore được để đảm bảo toàn vẹn dữ liệu.

tar -cf /pgbackup/backup_all.tar /pgdata/15/data

Script Backup: có thẻ chạy trong crontab tự động

#!/bin/bash

# Set variables for database connection
PGUSER=your_username
PGDATABASE=your_database_name

# Set the path where you want to store the backup files
BACKUP_DIR=/path/to/backup/directory

# Get current date and time
datestamp=$(date +'%Y-%m-%d')
timestamp=$(date +'%H%M')

# Execute pg_dump command to dump the database
pg_dump -U "$PGUSER" -d "$PGDATABASE" > "$BACKUP_DIR/$PGDATABASE"_"$datestamp"_"$timestamp".sql