lightdb物理备份工具之lt_probackup介绍及时间点恢复
postgresql自带pg_basebackup,也可用于在主库或备库对postgresql数据库进行在线实时全量备份,和oracle rman全量备份还是挺类似的。如下:
[zjh@hs-10-20-30-193 zjh]$ lt_basebackup --help lt_basebackup takes a base backup of a running LightDB server. Usage: lt_basebackup [OPTION]... Options controlling the output: -D, --pgdata=DIRECTORY receive base backup into directory -F, --format=p|t output format (plain (default), tar) -r, --max-rate=RATE maximum transfer rate to transfer data directory (in kB/s, or use suffix "k" or "M") -R, --write-recovery-conf write configuration for replication -T, --tablespace-mapping=OLDDIR=NEWDIR relocate tablespace in OLDDIR to NEWDIR --waldir=WALDIR location for the write-ahead log directory -X, --wal-method=none|fetch|stream include required WAL files with specified method -y, --decrypt receive the data decrypted -z, --gzip compress tar output -Z, --compress=0-9 compress tar output with given compression level General options: -c, --checkpoint=fast|spread set fast or spread checkpointing -C, --create-slot create replication slot -l, --label=LABEL set backup label -n, --no-clean do not clean up after errors -N, --no-sync do not wait for changes to be written safely to disk -P, --progress show progress information -S, --slot=SLOTNAME replication slot to use -v, --verbose output verbose messages -V, --version output version information, then exit --manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE use algorithm for manifest checksums --manifest-force-encode hex encode all file names in manifest --no-estimate-size do not estimate backup size in server side --no-manifest suppress generation of backup manifest --no-slot prevent creation of temporary replication slot --no-verify-checksums do not verify checksums -?, --help show this help, then exit Connection options: -d, --dbname=CONNSTR connection string -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -s, --status-interval=INTERVAL time between status packets sent to server (in seconds) -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) Report bugs to <https://github.com/hslightdb>. LightDB home page: <https://www.hs.net/lightdb> [zjh@hs-10-20-30-193 zjh]$ lt_basebackup -h 127.0.0.1 -p 18383 -D test_initdb_backup -P # 不压缩 30831/30831 kB (100%), 1/1 tablespace [zjh@hs-10-20-30-193 zjh]$ lt_basebackup -F t -z -h 127.0.0.1 -p 18383 -D test_initdb_backup -P # 压缩 30846/30846 kB (100%), 1/1 tablespace
[zjh@hs-10-20-30-193 zjh]$ lt_basebackup -h 127.0.0.1 -p 18383 -D test_initdb_backup -Pv
lt_basebackup: initiating base backup, waiting for checkpoint to complete
lt_basebackup: checkpoint completed
lt_basebackup: write-ahead log start point: 1/60000028 on timeline 1
lt_basebackup: starting background WAL receiver
lt_basebackup: created temporary replication slot "pg_basebackup_78391"
30926/30926 kB (100%), 1/1 tablespace
lt_basebackup: write-ahead log end point: 1/60000100
lt_basebackup: waiting for background process to finish streaming ...
lt_basebackup: syncing data to disk ...
lt_basebackup: renaming backup_manifest.tmp to backup_manifest
lt_basebackup: base backup completed
#################恢复#################
[zjh@hs-10-20-30-193 zjh]$ vim test_initdb1/lightdb.conf
recovery_target = 'immediate'
restore_command = 'cp /data1/zjh/archive/%f %p'
[zjh@hs-10-20-30-193 zjh]$ touch test_initdb1/recovery.signal
[zjh@hs-10-20-30-193 zjh]$ lt_ctl -D test_initdb1 start
[zjh@hs-10-20-30-193 zjh]$ ltsql -p18383
ltsql (13.8-23.2)
Type "help" for help.
zjh@postgres=# create table t(id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
zjh@postgres=# select pg_backend_pid();
pg_backend_pid
----------------
47345
(1 row)
zjh@postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
zjh@postgres=# create table t(id int);
CREATE TABLE
zjh@postgres=#
## 一般来说,如果不做时间点恢复,不需要创建recovery.signal,直接lt_ctl start即可。
lt_basebackup的缺点在于不支持增量备份,这对于大型数据库来说是不可接受的。所以,一般推荐使用更强大的probackup。
[zjh@hs-10-20-30-193 zjh]$ lt_probackup init -B /data1/zjh/lt_probackup_dir INFO: Backup catalog '/data1/zjh/lt_probackup_dir' successfully inited [zjh@hs-10-20-30-193 zjh]$ lt_probackup add-instance -B /data1/zjh/lt_probackup_dir -D /data1/zjh/test_initdb1 --instance test_initdb1_193_base INFO: Instance 'test_initdb1_193_base' successfully inited [zjh@hs-10-20-30-193 zjh]$ lt_probackup backup -B /data1/zjh/lt_probackup_dir --instance test_initdb1_193_base -b FULL -h 127.0.0.1 -p 18383 INFO: Backup start, lt_probackup version: 2.4.15, instance: test_initdb1_193_base, backup ID: RVERU0, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1 WARNING: Current LightDB role is superuser. It is not recommended to run backup or checkdb as superuser. INFO: wait for pg_start_backup() INFO: Wait for WAL segment /data1/zjh/lt_probackup_dir/wal/test_initdb1_193_base/000000020000000100000004 to be archived WARNING: By default lt_probackup assume WAL delivery method to be ARCHIVE. If continuous archiving is not set up, use '--stream' option to make autonomous backup. Otherwise check that continuous archiving works correctly.
注:由于lightdb端大小限制为32GB,而pg_probackup仅支持单个文件最大4GB,故必须使用lt_probackup。
由于lt_probackup使用较为复杂, 推荐使用lightdb-em进行集中式备份和恢复。
https://www.cnblogs.com/lottu/p/14097905.html
https://github.com/postgrespro/pg_probackup/releases