PostgreSQL Online Backup và Point-in-Time Recovery (PITR)

Online Backup trong PostgreSQL được thực hiện bằng cách sử dụng các công cụ như pg_basebackup để sao lưu dữ liệu mà không cần dừng dịch vụ PostgreSQL. Point-in-Time Recovery (PITR) là một kỹ thuật giúp khôi phục cơ sở dữ liệu đến một thời điểm cụ thể.

Dưới đây là hướng dẫn từng bước để thực hiện Online Backup và PITR Recovery trong PostgreSQL:

Ghi chú:

Nguyên lý backup này là sẽ sao lưu lại toàn bộ thư mục /base trong data của postgresql lưu vào thư mục an toàn.

Sao chép toàn bộ wal_file trong thư mục pg_wal theo thời gian thực lưu vào đường dẫn sao lưu an toàn.

Sao chép toàn bộ tablespace nếu có vào thư mục an toàn.

=> Khi có sự cố hoặc cần khôi phục lại => chúng ta bung lại các dữ liệu bên trên là xong.

Tạo sẵn thư mục lưu basebackup và wal_archive

su - root

mkdir /pgbackup/basebackup -p

mkdir /pgbackup/wal_archive -p

chown postgres:postgres /pgbackup/ -R

Cấu hình PostgreSQL để hỗ trợ PITR

Chỉnh sửa postgresql.conf:

Đặt wal_level thành replica hoặc logical:

Đặt archive_mode thành on:

Chỉ định archive_command để lưu trữ các tệp WAL:

su postgres

vi /var/lib/pgsql/15/data/postgresql.conf

wal_level = replica

archive_mode = on

archive_command = 'cp %p /pgbackup/wal_archive/%f'

Khởi động lại PostgreSQL để áp dụng các thay đổi:

##chạy với quyền root user:

sudo systemctl restart postgresql-15.service

sudo systemctl status postgresql-15.service

Thực hiện Online Backup

Tạo bản sao lưu với pg_basebackup:

su - postgres

pg_basebackup -D /pgbackup/basebackup -Ft -z -Xs -P

Trong đó:

-D /pgbackup/basebackup: Đường dẫn đến thư mục sao lưu.

-Fp: Sao lưu ở định dạng plain text, t ở dạng tar.

-Xs: Ghi nhật ký WAL đồng bộ.

-P: Hiển thị tiến trình sao lưu.

-U postgres: Tên người dùng PostgreSQL. (neu co)

Kiểm tra tệp WAL

Đảm bảo rằng các tệp WAL được lưu trữ đúng cách theo cấu hình archive_command đã thiết lập.

cd /pgbackup/wal_archive

ls -lha

total 33M

drwxr-xr-x. 2 postgres postgres 4.0K May 17 17:58 .

drwxr-xr-x. 4 postgres postgres 43 May 17 17:55 ..

-rw——-. 1 postgres postgres 16M May 17 17:58 000000010000000000000001

-rw——-. 1 postgres postgres 16M May 17 17:58 000000010000000000000002

-rw——-. 1 postgres postgres 338 May 17 17:58 000000010000000000000002.00000028.backup

Thực hiện switch_wal xem hệ thống có tạo file mới hay không?

select pg_switch_wal();

postgres=# select pg_switch_wal();

pg_switch_wal

—————

0/3000078

(1 row)

select now();

postgres=# select now();

now

——————————-

2024-05-17 18:00:45.498169+07

(1 row)

###Thêm data và thực hiện switch_wal

Kiểm tra table hiện tại => chưa có table nào:

postgres=# \dt

Did not find any relations.

postgres=#

Tạo table mới và thêm 1000 rows vào:

create table tab1 (id int, data1 text);

INSERT INTO tab1 (id, data1)

SELECT 111, 'chxhcnvn'

FROM generate_series(1, 1000);

select pg_switch_wal();

postgres=# \dt

List of relations

Schema | Name | Type | Owner

——–+——+——-+———-

public | tab1 | table | postgres

(1 row)

=>đã có 1 table

select now();

postgres=# select now();

now

——————————-

2024-05-17 18:03:47.690306+07

(1 row)

Ở đây chúng ta có 2 thời điểm: trước khi tạo tab1 chính là bản backup và sau khi có tab1 (wal_file)

Thực hiện Point-in-Time Recovery (PITR)

Giả sử database bị xoá hết và hệ thống lỗi.

Chúng ta sẽ thực hiện restore database về lúc trước tab1 và sau đó sử dụng wal_archive để recovery về khi có tab1

Dừng dịch vụ PostgreSQL: (login với user ROOT)

sudo systemctl stop postgresql-15.service

sudo systemctl status postgresql-15.service

Xóa hoặc đổi tên thư mục dữ liệu hiện tại – mô phỏng sự cố (lưu ý rằng điều này sẽ xóa tất cả dữ liệu hiện tại TUYỆT ĐỐI KO THỬ TRÊN PRODUCTION, PHẢI CHẮC CHẮN MỚI LÀM):

su - postgres

mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data_old

##không còn thư mục data nữa.

-bash-4.2$ mv /var/lib/pgsql/15/data /var/lib/pgsql/15/data_old

-bash-4.2$ ls /var/lib/pgsql/15/data

ls: cannot access /var/lib/pgsql/15/data: No such file or directory

Khôi phục từ bản sao lưu:

Đầu tiên chúng ta phải tạo lại đường dẫn data trong thư mục /15, Thay đổi quyền 700 (quan trọng)

su - postgres

mkdir -p /var/lib/pgsql/15/data
chmod 700 /var/lib/pgsql/15/data

tar -xvzf /pgbackup/basebackup/base.tar.gz -C /var/lib/pgsql/15/data/

Sửa file postgresql.conf và thêm vào nội dung bên dưới:

vi /var/lib/pgsql/15/data/postgresql.conf

restore_command = 'cp /pgbackup/wal_archive/%f %p'

recovery_target_time = '2024-05-17 18:03:47'

Trong đó, restore_command là lệnh để khôi phục các tệp WAL và recovery_target_time là thời điểm muốn khôi phục.

Tạo tệp recovery.signal trong thư mục dữ liệu mới:

touch /var/lib/pgsql/15/data/recovery.signal

=>file này là file trống không có nội dụng gì, nó có ý nghĩa là chỉ ra cho postgres biết được CSDL đang ở chế độ recovery

Khởi động lại PostgreSQL: (với user root)

sudo systemctl start postgresql-15.service

sudo systemctl status postgresql-15.service

Kiểm tra dữ liệu:

postgres=# \dt

List of relations

Schema | Name | Type | Owner

——–+——+——-+———-

public | tab1 | table | postgres

(1 row)

=>đã có bảng tab1, như vậy là hệ thống đã recover tới thời điểm được chỉ định.

Chúng ta có thể tiến hành xoá file recovery.signal và clear tham số restore_command, recovery_target_time trong file cấu hình postgresql.conf.