快速体验PostgreSQL 17 Beta 1 新特性之Backup and Export Management

##

快速体验PostgreSQL 17 Beta 1 新特性之Backup and Export Management

1、Released-Backup and Export Management

PostgreSQL 17 introduces the ability to perform incremental backups using pg_basebackup, and adds a new utility called pg_combinebackup that’s used as part of the backup restoration process. This release adds a new flag to pg_dump called --filter, letting you specify a file that contains instructions on what objects to include/exclude from a dump.

2、 pg_basebackup增量备份与合并备份

关键字： incremental ， pg_combinebackup

2.1、第一次全备(这里使用压缩)

[postgres@pgdb01:/home/postgres]$pg_basebackup -h 192.168.5.130 -p 5432 -U postgres -Xs -Ft -z -Pv -Z9 -D /postgresql/backup/bak01
Password:
复制

2.2、第一次增量备份

[postgres@pgdb01:/home/postgres]$pg_basebackup -h 192.168.5.130 -p 5432 -U postgres -i /postgresql/backup/bak01/backup_manifest -Xs -Ft -z -Pv -Z9 -D /postgresql/backup/bak01-irc
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: error: could not initiate base backup: ERROR:  incremental backups cannot be taken unless WAL summarization is enabled
pg_basebackup: removing data directory "/postgresql/backup/bak01-irc"
复制

这里提示unless WAL summarization is enabled，不知道是啥参数，查一下settings。

postgres=# select name,setting from pg_settings where name like '%wal%';
             name              |  setting
-------------------------------+-----------
 auto_explain.log_wal          | off
 max_slot_wal_keep_size        | -1
 max_wal_senders               | 10
 max_wal_size                  | 1024
 min_wal_size                  | 80
 summarize_wal                 | off
 track_wal_io_timing           | off
 wal_block_size                | 8192
 wal_buffers                   | 2048
 wal_compression               | off
 wal_consistency_checking      |
 wal_decode_buffer_size        | 524288
 wal_init_zero                 | on
 wal_keep_size                 | 0
 wal_level                     | replica
 wal_log_hints                 | off
 wal_receiver_create_temp_slot | off
 wal_receiver_status_interval  | 10
 wal_receiver_timeout          | 60000
 wal_recycle                   | on
 wal_retrieve_retry_interval   | 5000
 wal_segment_size              | 16777216
 wal_sender_timeout            | 60000
 wal_skip_threshold            | 2048
 wal_summary_keep_time         | 14400
 wal_sync_method               | fdatasync
 wal_writer_delay              | 200
 wal_writer_flush_after        | 128
(28 rows)
复制

2.3、参数处理

[postgres@pgdb01:/home/postgres]$echo 'summarize_wal=on' >> /postgresql/data/postgresql.conf
[postgres@pgdb01:/home/postgres]$pg_ctl -m fast restart

[postgres@pgdb01:/home/postgres]$psql -c "select name,setting from pg_settings where name='summarize_wal'"
     name      | setting
---------------+---------
 summarize_wal | on
(1 row)
复制

2.4、再次执行增量备份

[postgres@pgdb01:/home/postgres]$pg_basebackup -h 192.168.5.130 -p 5432 -U postgres -i /postgresql/backup/bak01/backup_manifest -Xs -Ft -z -Pv -Z9 -D /postgresql/backup/bak01-irc
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  aborting backup due to backend exiting before pg_backup_stop was called
pg_basebackup: error: could not initiate base backup: ERROR:  WAL summaries are required on timeline 1 from 0/2000028 to 0/5000028, but the summaries for that timeline and LSN range are incomplete
DETAIL:  The first unsummarized LSN in this range is 0/2000028.
pg_basebackup: removing data directory "/postgresql/backup/bak01-irc"
复制

继续报错，看信息是第一次全备份时的参数没有启用导致的。

2.5、全备+增量备份再次

[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/bak01
[postgres@pgdb01:/home/postgres]$pg_basebackup -h 192.168.5.130 -p 5432 -U postgres -Xs -Ft -z -Pv -Z9 -D /postgresql/backup/bak01
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  skipping special file "./.s.PGSQL.5432"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/6000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_63948"
WARNING:  skipping special file "./.s.PGSQL.5432"backup/bak01/base.tar.gz)
38970/38970 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/6000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed


[postgres@pgdb01:/home/postgres]$psql dvdrental
psql (17beta1)
Type "help" for help.

dvdrental=# create table t2 as select * from actor;
SELECT 200
dvdrental=# \q


[postgres@pgdb01:/home/postgres]$pg_basebackup -h 192.168.5.130 -p 5432 -U postgres -i /postgresql/backup/bak01/backup_manifest -Xs -Ft -z -Pv -Z9 -D /postgresql/backup/bak01-irc
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  skipping special file "./.s.PGSQL.5432"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/8000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_63971"
WARNING:  skipping special file "./.s.PGSQL.5432"p/bak01-irc/base.tar.gz)
 5444/38976 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/8000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
复制

成功

增量的关键是backup_manifest，以及参数summarize_wal

2.6、恢复

重要信息：需要合并全备与增量，这个mysql的PXB备份很像。

2.6.1、第一次合并备份

[postgres@pgdb01:/home/postgres]$pg_combinebackup /postgresql/backup/bak01 /postgresql/backup/bak01-irc
pg_combinebackup: error: no output directory specified

[postgres@pgdb01:/home/postgres]$pg_combinebackup /postgresql/backup/bak01 /postgresql/backup/bak01-irc -o /postgresql/backup/restore01
pg_combinebackup: error: could not open file "/postgresql/backup/bak01-irc/PG_VERSION": No such file or directory
复制

合并备份需要指定合并输出的目录，并且需要解压文件。

2.6.2、解压文件

[postgres@pgdb01:/home/postgres]$cd /postgresql/backup/bak01
[postgres@pgdb01:/postgresql/backup/bak01]$ll
total 5856
-rw------- 1 postgres postgres  193251 May 24 17:12 backup_manifest
-rw------- 1 postgres postgres 5777258 May 24 17:12 base.tar.gz
-rw------- 1 postgres postgres   17680 May 24 17:12 pg_wal.tar.gz
[postgres@pgdb01:/postgresql/backup/bak01]$tar -zxvf base.tar.gz


[postgres@pgdb01:/postgresql/backup/bak01]$tar -zxvf pg_wal.tar.gz -C pg_wal/


[postgres@pgdb01:/postgresql/backup]$cd bak01-irc/
[postgres@pgdb01:/postgresql/backup/bak01-irc]$ll
total 304
-rw------- 1 postgres postgres 202375 May 24 17:13 backup_manifest
-rw------- 1 postgres postgres  85489 May 24 17:13 base.tar.gz
-rw------- 1 postgres postgres  17668 May 24 17:13 pg_wal.tar.gz
[postgres@pgdb01:/postgresql/backup/bak01-irc]$tar -zxvf base.tar.gz

[postgres@pgdb01:/postgresql/backup/bak01-irc]$tar -zxvf pg_wal.tar.gz -C pg_wal/
000000010000000000000008
archive_status/000000010000000000000008.done

复制

这里没有去做恢复，需要的话，可以自行处理wal去恢复，仅测试pg_combinebackup

2.6.3、再次合并

[postgres@pgdb01:/home/postgres]$pg_combinebackup /postgresql/backup/bak01 /postgresql/backup/bak01-irc -o /postgresql/backup/restore01
pg_combinebackup: warning: "/postgresql/backup/bak01-irc/backup_manifest" contains no entry for "base.tar.gz"
pg_combinebackup: warning: "/postgresql/backup/bak01-irc/backup_manifest" contains no entry for "pg_wal.tar.gz"
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/restore01
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/bak01/base
base/        base.tar.gz
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/bak01/base.tar.gz
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/bak01/pg_wal.tar.gz
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/bak01-irc/base.tar.gz
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/backup/bak01-irc/pg_wal.tar.gz

[postgres@pgdb01:/home/postgres]$pg_combinebackup /postgresql/backup/bak01 /postgresql/backup/bak01-irc -o /postgresql/backup/restore01
复制

这里我是删除了备份的文件，实际cp/mv会更合理。

2.7、模拟恢复

[postgres@pgdb01:/home/postgres]$pg_ctl -m fast stop
waiting for server to shut down.... done
server stopped
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/data/*
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/archive/*
[postgres@pgdb01:/home/postgres]$rm -fr /postgresql/wal/*
[postgres@pgdb01:/home/postgres]$
[postgres@pgdb01:/home/postgres]$
[postgres@pgdb01:/home/postgres]$cp -r /postgresql/backup/restore01/* /postgresql/data/
复制

2.8、启动检查

[postgres@pgdb01:/home/postgres]$pg_ctl start
waiting for server to start....2024-05-24 17:24:44.040 CST [64394] LOG:  redirecting log output to logging collector process
2024-05-24 17:24:44.040 CST [64394] HINT:  Future log output will appear in directory "/postgresql/log".
 done
server started

[postgres@pgdb01:/home/postgres]$psql dvdrental -c 'select count(*) from t2'
 count
-------
   200
(1 row)
复制

3、pg_dump的filter测试

3.1、测试案例

[postgres@pgdb01:/home/postgres]$cat filter.txt
include table t1
exclude table t2

[postgres@pgdb01:/home/postgres]$pg_dump --filter=filter.txt dvdrental > db-dvdrental.sql

[postgres@pgdb01:/home/postgres]$cat db-dvdrental.sql |grep -i t1
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
CREATE TABLE public.t1 (
ALTER TABLE public.t1 OWNER TO postgres;
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
COPY public.t1 (actor_id, first_name, last_name, last_update) FROM stdin;

[postgres@pgdb01:/home/postgres]$cat db-dvdrental.sql |grep -i t2

复制

3.2、filter参数表

Specify a filename from which to read patterns for objects to include or exclude from the dump. The patterns are interpreted according to the same rules as the corresponding options: -t/--table, --table-and-children, --exclude-table-and-children or -T for tables, -n/--schema for schemas, --include-foreign-data for data on foreign servers, --exclude-table-data, --exclude-table-data-and-children for table data, and -e/--extension or --exclude-extension for extensions. To read from STDIN, use - as the filename. The --filter option can be specified in conjunction with the above listed options for including or excluding objects, and can also be specified more than once for multiple filter files.

The file lists one object pattern per row, with the following format:

{ include | exclude } { extension | foreign_data | table | table_and_children | table_data | table_data_and_children | schema } PATTERN
复制

The first keyword specifies whether the objects matched by the pattern are to be included or excluded. The second keyword specifies the type of object to be filtered using the pattern:

• extension: extensions, works like the -e/--extension option.
• foreign_data: data on foreign servers, works like the --include-foreign-data option. This keyword can only be used with the include keyword.
• table: tables, works like the -t/--table option.
• table_and_children: tables including any partitions or inheritance child tables, works like the --table-and-children option.
• table_data: table data of any tables matching pattern, works like the --exclude-table-data option. This keyword can only be used with the exclude keyword.
• table_data_and_children: table data of any tables matching pattern as well as any partitions or inheritance children of the table(s), works like the --exclude-table-data-and-children option. This keyword can only be used with the exclude keyword.
• schema: schemas, works like the -n/--schema option.

Lines starting with # are considered comments and ignored. Comments can be placed after an object pattern row as well. Blank lines are also ignored. See Patterns for how to perform quoting in patterns.

4、总结

官方自带的工具越来越强大了，毕竟第三方备份工具太多了。减少学习成本，好事一件。