PG备份出来的数据库表文件比原数据库还大,为啥？

前言

关于PostgreSQL的备份，前几天，碰到有人问起这个问题。

奇怪吗？

于是有人就说，是不是备份用的全备，把pg_wal里头的大量的WAL文件也算在内了。提问的同学说，不是。他用的就是简单的pg_dump。

这问题有点儿意思。

重现与分析

那么，有没有这种可能呢？既然有人提起这个现象，那大概率是有的。

为了把问题简化，我们就用一张表来模拟。

建表及填充数据

1、不带toast数据

postgres=# create database foo;
CREATE DATABASE

postgres=# \c foo
You are now connected to database "foo" as user "postgres".
foo=# create table test(id int primary key, col2 text);
CREATE TABLE
foo=# insert into test select n, repeat('test ', 200) || n from generate_series(1, 10000) as n;
INSERT 0 10000
foo=# checkpoint;
CHECKPOINT

大意是插入10000条记录到表TEST当中。第二列，差不多每次都是长度1K的样子。

好，接着我们看一看数据库以及相应的表大小：

foo=# select pg_size_pretty(pg_database_size('foo'));
 pg_size_pretty
----------------
 20 MB
(1 row)

foo=# select pg_total_relation_size('test');
 pg_total_relation_size
------------------------
               11993088
(1 row)

我们再将数据库foo导出看看到底有多大？

[08:57:01-postgres@centos1:/var/lib/pgsql]$ pg_dump -d foo -f /iihero/tmp/foo.dat
[08:57:27-postgres@centos1:/var/lib/pgsql]$ ls -la /iihero/tmp/foo.dat
-rw-r--r-- 1 postgres postgres 10098818 Oct  5 08:57 /iihero/tmp/foo.dat

导出来的文件比test表的总大小还略小一点。再考查一下toast表：

foo=# select oid, relname, reltoastrelid, (select relname from pg_class where oid=a.reltoastrelid) as toastname from pg_class a where relname in ('test');
  oid  | relname | reltoastrelid |   toastname
-------+---------+---------------+----------------
 34169 | test    |         34172 | pg_toast_34169
(1 row)

foo=# select count(*) from pg_toast.pg_toast_34169;
 count
-------
     0
(1 row)

foo=# select pg_total_relation_size('pg_toast.pg_toast_34169');
 pg_total_relation_size
------------------------
                   8192
(1 row)

2、使用带有toast数据的表来看看：

foo=# drop table test;
DROP TABLE
foo=# select pg_size_pretty(pg_database_size('foo'));
 pg_size_pretty
----------------
 8809 kB
(1 row)

CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
    SELECT string_agg(substring('abcdefghijiklmnopqrstuvwxyz', round(random() * 25)::integer, 1), '') FROM generate_series(1, $1);
$$ language sql;

 create table test(id int primary key, col2 text);
 insert into test select n, random_string(1024) from generate_series(1, 200000) as n;
INSERT 0 200000

foo=# select pg_size_pretty(pg_database_size('foo'));
 pg_size_pretty
----------------
 236 MB
(1 row)
[09:12:19-postgres@centos1:/var/lib/pgsql]$ pg_dump -d foo -f /iihero/tmp/foo.dat
[09:13:42-postgres@centos1:/var/lib/pgsql]$  ls -lah /iihero/tmp/foo.dat
-rw-r--r-- 1 postgres postgres 193M Oct  5 09:13 /iihero/tmp/foo.dat

 pg_dump -d foo -F c -f /iihero/tmp/foo.dat
  ls -lah /iihero/tmp/foo.dat
-rw-r--r-- 1 postgres postgres 122M Oct  5 09:20 /iihero/tmp/foo.dat

从上边来看，常规的这些值，似乎备份出来的文件，哪怕是针对单个表，依然大小是小于原库的大小的。

3、使用toast数据，并且会出现压缩

下边的情形，就以一条记录为例 ：

create table test(id int , col2 text);

foo=# insert into test values(1, random_string(20));
INSERT 0 1

foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1
foo=# update test set col2 = col2 || col2;
UPDATE 1

-- 执行update，来个15次
foo=# select length(col2) from test;
 length
--------
 622592
(1 row)

再看看相关大小：

foo=# select oid, relname, reltoastrelid, (select relname from pg_class where oid=a.reltoastrelid) as toastname from pg_class a where relname in ('test');
  oid  | relname | reltoastrelid |   toastname
-------+---------+---------------+----------------
 34210 | test    |         34213 | pg_toast_34210
(1 row)

foo=# select ctid, chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_34210;
 ctid  | chunk_id | chunk_seq | length
-------+----------+-----------+--------
 (0,3) |    34216 |         0 |   1996
 (0,4) |    34216 |         1 |   1996
 (1,1) |    34216 |         2 |   1996
 (1,2) |    34216 |         3 |   1166
(4 rows)

这意味着622592的总长，压缩至1996 + 1996 + 1996 + 1166个字节。

foo=# select pg_total_relation_size('test');
 pg_total_relation_size
------------------------
                  65536
(1 row)

[09:36:43-postgres@centos1:/iihero/tmp]$ pg_dump -d foo -f /iihero/tmp/foo.sql
[12:29:04-postgres@centos1:/iihero/tmp]$ ls -lrt /iihero/tmp/foo.sql
-rw-r--r-- 1 postgres postgres 623834 Oct  5 12:29 /iihero/tmp/foo.sql

foo=# select pg_relation_filepath('test');
 pg_relation_filepath
----------------------
 base/34168/34210
(1 row)

foo=# \! ls -lrt $PGDATA/base/34168/34210
-rw------- 1 postgres postgres 8192 Oct  5 12:28 /var/lib/pgsql/14/data/base/34168/34210
foo=# select pg_relation_filepath('pg_toast.pg_toast_34210');
 pg_relation_filepath
----------------------
 base/34168/34213
(1 row)

foo=# \! ls -lrt $PGDATA/base/34168/34213
-rw------- 1 postgres postgres 16384 Oct  5 12:28 /var/lib/pgsql/14/data/base/34168/34213

从上边，我们能清楚的看到，导出的文件，大小为: 623834B.

而数据库中原表的文件大小分别为：test:　8192B， pg_toast_34210: 16384B.

导出的文件大小明显大于表数据文件的大小。

什么原因：因为toast数据被高度压缩了。

这是单个表是这样的，如果这样的表多了，组成源库的所有的表文件的大小与导出的文件的大小的差距就会进一步拉大。最终就有可能导出的文件比源库大小还要大。