PostgreSQL16新特性之pg_dump增强

pg_dump是我们在使用PostgreSQL时常用的数据导出和逻辑备份工具，PostgreSQL16对pg_dump进行了一系列增强，主要体现在：

对子表和分区表的支持

pg_dump添加了导出子表和分区的功能。
新增了如下选项：

• —table-and-children=PATTERN 导出指定表，包括子表和分区表

• —exclude-table-and-children=PATTERN 不导出指定表，子表和分区表也不导出

• —exclude-table-data-and-children=PATTERN 不导出指定表的数据，子表和分区表也不导出

示例：

#创建相关表
dump1=# CREATE TABLE person (
dump1(#     id serial PRIMARY KEY,
dump1(#     first_name text,
dump1(#     last_name text
dump1(# );
CREATE TABLE
dump1=# CREATE TABLE employee (
dump1(#     employee_id serial PRIMARY KEY,
dump1(#     salary int
dump1(# ) INHERITS (person);
CREATE TABLE

#插入测试数据
dump1=# insert into person(first_name, last_name) values('张','三');
INSERT 0 1
dump1=# insert into person(first_name, last_name) values('李','四');
INSERT 0 1
dump1=# select * from person;
 id | first_name | last_name 
----+------------+-----------
  1 | 张         | 三
  2 | 李         | 四
(2 rows)
dump1=# insert into employee(first_name, last_name, salary) values('王','五', 3000);
INSERT 0 1
dump1=# select * from employee;
 id | first_name | last_name | employee_id | salary 
----+------------+-----------+-------------+--------
  3 | 王         | 五        |           1 |   3000
(1 row)

#使用pg_dump导出person的数据
pg_dump -U postgres -d dump1 --table-and-children=person > /data/backup/postgres/person.sql

#恢复数据
postgres=# create database restore3;
CREATE DATABASE

$ psql -U postgres restore3 < /data/backup/postgres/person.sql
SET
SET
SET
SET
SET
 set_config 
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
CREATE SEQUENCE
ALTER SEQUENCE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 1
COPY 2
 setval 
--------
      1
(1 row)

 setval 
--------
      3
(1 row)

ALTER TABLE
ALTER TABLE

#验证恢复结果
postgres=# \c restore3
You are now connected to database "restore3" as user "postgres".
restore3=# \d
                    List of relations
 Schema |           Name           |   Type   |  Owner   
--------+--------------------------+----------+----------
 public | employee                 | table    | postgres
 public | employee_employee_id_seq | sequence | postgres
 public | person                   | table    | postgres
 public | person_id_seq            | sequence | postgres
(4 rows)

restore3=# select * from employee;
 id | first_name | last_name | employee_id | salary 
----+------------+-----------+-------------+--------
  3 | 王         | 五        |           1 |   3000
(1 row)

#可以看到子表也被导出和恢复了

增加新的压缩方式

PostgreSQL16中pg_dump的-Z选项新增了lz4和zstd压缩算法，压缩效率得到了提升。
示例：

pg_dump -U postgres -d dump1 -F p --compress=lz4 -f /data/backup/postgres/dump1_lz4
pg_dump -U postgres -d dump1 -F p --compress=zstd -f /data/backup/postgres/dump1_zstd
#注意：需要在configure时指定--with-lz4和--with-zstd选项，否则会提示如下错误
pg_dump: error: invalid compression specification: this build does not support compression with LZ4

#初始化1000万数据进行导出操作
dump1=# create table mytb1(id int, name varchar(30));
CREATE TABLE
dump1=# insert into mytb1 values(generate_series(1, 10000000), 'aaAA123..');
INSERT 0 10000000

cd /data/backup/postgres/
# du -sh *
24M     dump1_gzip
41M     dump1_lz4
171M    dump1_uncompress
4.0M    dump1_zstd

#我们可以看到在不同的压缩模式下，不同的的压缩算法的压缩效率各不相同，其中新增的zstd的压缩效率最高

增加压缩长模式支持

PostgreSQL16中pg_dump的增加了long模式的支持
示例

pg_dump -U postgres -d dump1 -F p --compress=zstd:long -f /data/backup/postgres/dump1_long
长模式可以提高压缩比，但代价是增加了内存使用。

#查看导出文件
# du -sh *
24M     dump1_gzip
3.9M    dump1_long
41M     dump1_lz4
171M    dump1_uncompress
4.0M    dump1_zstd
# 可以看到使用long模式的压缩比更大，这里提升有限可能是与我们文本本身大小的基数较小有关，生产环境可能提升更大。

增加更多的压缩级别

PostgreSQL16中pg_dump可以拥有更多的压缩级别，如—compress=lz4:1。

示例：

#级别1
pg_dump -U postgres -d dump1 -F p --compress=lz4:1 -f /data/backup/postgres/dump1_lz4_lv1

#级别9
pg_dump -U postgres -d dump1 -F p --compress=lz4:9 -f /data/backup/postgres/dump1_lz4_lv9
#某些压缩算法的级别是确定的，如gzip一共有1-9九个级别，如果我们使用其他级别，则会得到一个报错
$ pg_dump -U postgres -d dump1 --compress=gzip:10 -F c -f /data/backup/postgres/dump_lv10.dmp
pg_dump: error: invalid compression specification: compression algorithm "gzip" expects a compression level between 1 and 9 (default at -1)

#级别越高，压缩比越高，但是可能也更耗时和占用更多资源，我们查看压缩后的文件
du -sh dump1_lz4_lv*
41M     dump1_lz4_lv1
39M     dump1_lz4_lv9

# 压缩时间测试
# level 1
date ; pg_dump -U postgres -d dump1 -F p --compress=lz4:1 -f /data/backup/postgres/dump1_lz4_lv1;date
Thu Mar 14 20:14:43 CST 2024
Thu Mar 14 20:14:47 CST 2024

#level 9
date ; pg_dump -U postgres -d dump1 -F p --compress=lz4:9 -f /data/backup/postgres/dump1_lz4_lv9; date
Thu Mar 14 20:14:57 CST 2024
Thu Mar 14 20:15:14 CST 2024

#可以看到level 1的压缩级别只需要4s，而level 9的压缩级别需要多达17s