【PostgreSQL】PG_DUMP的文件大小元小于库占用物理空间统计

现象描述

使用pg_dump做pg库备份的时候，发现导出的数据量大小远小于统计出来的占用空间大小：

spark_db=# select pg_size_pretty(pg_database_size('spark_db'));      
 pg_size_pretty 
----------------
 19 GB
(1 row)
1
2
3
4
5

接下来是pg_dump出来的数据量大小：

[postgres@gsqzj11184 ~]$ pg_dump -h127.0.0.1 -p5832 -Upostgres spark_db -f /tmp/spark.sql
Password: 
[postgres@gsqzj11184 ~]$ du -sh /tmp/spark.sql 
626M    /tmp/spark.sql
1
2
3
4

问题复现

创建一个测试库dump_test：

create database dump_test;
/ ======================================
postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 dump_test  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 promanager | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
            |          |          |             |             | postgres=CTc/postgres+
            |          |          |             |             | prom=CTc/postgres
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

创建测试表t1并插入测试数据：

postgres=# \c dump_test 
You are now connected to database "dump_test" as user "postgres".
dump_test=# create table t1(id int);
CREATE TABLE
dump_test=# insert into t1 select generate_series(1,400000000);
INSERT 0 400000000
dump_test=# 
1
2
3
4
5
6
7

查看数据库物理空间大小，此时是14个G：

dump_test=# select pg_size_pretty(pg_database_size('dump_test'));
 pg_size_pretty 
----------------
 14 GB
(1 row)
1
2
3
4
5

接下来模拟日常对数据库进行频繁删除的情况，这里直接删除大量数据，大小并没有发生变化：

dump_test=# delete from t1 where id <> 40000000;
DELETE 399999999
dump_test=# select pg_size_pretty(pg_database_size('dump_test'));
 pg_size_pretty 
----------------
 14 GB
(1 row)
1
2
3
4
5
6
7

此时表中也只有1条数据：

dump_test=# select count(1) from t1;
 count 
-------
     1
(1 row)
1
2
3
4
5

此时使用pg_dump备份数据，查看大小，只有4.0K：

[root@vmax67029 ~]# pg_dump -h127.0.0.1 -p5832 -Upostgres dump_test -f /tmp/dump_test.sql
Password: 
[root@vmax67029 ~]# du -sh /tmp/dump_test.sql 
4.0K    /tmp/dump_test.sql
1
2
3
4

查看t1表的大小，是不是没有变化：

dump_test=# SELECT table_schema , table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables 
 where table_name ='t1'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
 table_schema | table_full_name | size  
--------------+-----------------+-------
 public       | t1              | 14 GB
(1 row)
1
2
3
4
5
6
7
8
9

也就是说，数据虽然删除了，但是统计出来的大小依旧存在，这就是因为触发了高水位，简单来说就是插入1-1000的数据，删除了1-999，这部分空间不会马上释放。

接下来手动做表收缩：

dump_test=# vacuum full t1;
VACUUM
dump_test=# SELECT table_schema , table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables 
 where table_name ='t1'
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
 table_schema | table_full_name |    size    
--------------+-----------------+------------
 public       | t1              | 8192 bytes
(1 row)
1
2
3
4
5
6
7
8
9
10
11

再次查看表的大小，只有8192 bytes大小了，而在备份出来的sql文件中，也是只有一条和库内一样的数据：

COPY public.t1 (id) FROM stdin;
40000000
\.
1
2
3

结论

pg_dump备份的数据不会考虑高水位，因此远小于pg内查出来的空间大小是正常的，并不会造成数据丢失。