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

lightdb lt_probackup 归档模式和流模式的区别

postgresql物理备份工具及lightdb支持情况

posted @ 2023-05-29 15:20  zhjh256  阅读(42)  评论(0编辑  收藏  举报