postgresql - Postgresql database size is lower after restore

Question

I just made a simple bash script to backup a postgresql database from remote server and restore it to the same server with prefix _bak. This script looks like:

export FILENAME="/var/pg-backups/$4-$(date +%s).bak"
echo "$(date) Creating dump with args - $1 $2 $3 $4. Filename = $FILENAME"
pg_dump -h $1 -p $2 -U $3 -b -F d -j 4 -v -f "$FILENAME" $4
echo "$(date) Re-create _bak database"
psql -h $1 -p $2 -U $3 -d $4 -c "drop database if exists $4_bak;"
psql -h $1 -p $2 -U $3 -d $4 -c "create database $4_bak;"
echo "$(date) Restoring hot copy with _bak postfix"
pg_restore -h $1 -p $2 -U $3 -d "$4_bak" -w -v "$FILENAME"
echo "$(date) Done"

It works well, but there's some strange thing with _bak database, which was restored - it weights lower than original!! Here's the output of pg_database_size for original (backuped) database and its restored copy:

postgres=# select pg_database_size('somedb');
 pg_database_size
------------------
        548152175

postgres=# select pg_database_size('somedb_bak');
 pg_database_size
------------------
        511648623
(1 строка)

Also, I took one table as an example to check relation size difference, with such query:

select pg_relation_size('resources.attachment', 'main') as main, 
       pg_relation_size('resources.attachment', 'fsm') as fsm, 
       pg_relation_size('resources.attachment', 'vm') as vm, 
       pg_relation_size('resources.attachment', 'init') as init, 
       pg_table_size('resources.attachment'), pg_indexes_size('resources.attachment') as indexes, 
       pg_total_relation_size('resources.attachment') as total;

and here's what I got for original database:

 main  |  fsm  |  vm  | init | pg_table_size | indexes |   total
-------+-------+------+------+---------------+---------+-----------
 65536 | 24576 | 8192 |    0 |     109158400 |   32768 | 109191168

and backuped version:

main  |  fsm  | vm | init | pg_table_size | indexes |   total
-------+-------+----+------+---------------+---------+-----------
 65536 | 24576 |  0 |    0 |     100302848 |   32768 | 100335616

So only pg_table_size and VM differs... records count is the same btw in original and backuped table.

Can someone please try to explain the size difference between original and restored database?

Postgresql verison:

PostgreSQL 12.7 (Debian 12.7-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

P.S.

i've also tried -F c without parallelism - same result

标签： postgresqlpg-dumppg-restore