Size of backup is almost 2 times bigger than the database

[JP95Git]

I noticed that the size of the backup is almost 2 times bigger then the database. Restoring the database from the backup results in a database with the same big size as the backup. Is this normal? How can I "shrink" the backup to the size of the database?

The database is 318 GB, as seen here:

[user@host ~]$ df -h
Dateisystem  Größe Benutzt Verf. Verw% Eingehängt auf
host:/database-share   750G    318G  433G   43% /media/database
host:/backup-share 1,5T    710G  791G   48% /media/backup

The backup is 586 GB, even the first backup. "df" confirmed this after the first backup (the above output is after several backups).

Here are the sizes, reported by barman:

[user@host ~]$  /opt/mytool/programs/barman/bin/barman --config /opt/mytool/programs/barman/etc/barman.conf list-backup localhost
localhost 20241024T000001 - Thu Oct 24 00:03:14 2024 - Size: 586.4 GiB - WAL Size: 140.4 MiB
localhost 20241023T000002 - Wed Oct 23 00:03:43 2024 - Size: 586.2 GiB - WAL Size: 155.2 MiB
localhost 20241022T000001 - Tue Oct 22 00:03:52 2024 - Size: 586.1 GiB - WAL Size: 156.6 MiB
localhost 20241021T000002 - Mon Oct 21 00:03:43 2024 - Size: 585.9 GiB - WAL Size: 159.9 MiB
localhost 20241020T000001 - Sun Oct 20 00:03:31 2024 - Size: 585.8 GiB - WAL Size: 144.6 MiB
localhost 20241019T000002 - Sat Oct 19 00:01:11 2024 - Size: 585.7 GiB - WAL Size: 138.7 MiB
localhost 20241018T204456 - Fri Oct 18 20:46:20 2024 - Size: 585.7 GiB - WAL Size: 13.9 MiB
localhost 20241018T185558 - Fri Oct 18 20:07:33 2024 - Size: 585.7 GiB - WAL Size: 1.5 MiB

Versions:

postgres (PostgreSQL) 16.1
3.10.0 Barman by EnterpriseDB (www.enterprisedb.com)

Configuration:

localhost.conf

[localhost]
conninfo = host=localhost port=5432 dbname=postgres user=barman
streaming_conninfo = host=localhost port=5432 dbname=postgres user=barman
ssh_command = ssh user@localhost
retention_policy_mode = auto
wal_retention_policy = main
retention_policy = RECOVERY WINDOW OF 7 days

barman.conf

[barman]
barman_home = /opt/mytool/database-backup
path_prefix = /opt/mytool/programs/postgres/bin
configuration_files_directory = /opt/mytool/programs/barman/etc
barman_user = mytool
log_file = /opt/mytool/programs/barman/logs/barman.log
compression = gzip
backup_method = rsync
reuse_backup = link
backup_options = concurrent_backup
streaming_archiver = on
slot_name = barman
recovery_options = 'get-wal'

[martinmarques]

What do you get when you run the following SQL?

select pg_size_pretty(sum(pg_tablespace_size(oid))) from pg_tablespace;

(I used the tablespace size instead of the database size as this will also bring the catalog tables)

[JP95Git]

Your command returns this: 587 GB

This matches the backup size of barman:
localhost 20241027T000003 - Sun Oct 27 00:03:51 2024 - Size: 586.8 GiB - WAL Size: 145.1 MiB

But it does not match the real size of the database:

Dateisystem          Größe Benutzt Verf. Verw% Eingehängt auf
host:/database-share  750G    318G  433G   43% /media/database

/media/database is only used for PGDATA. The admin of the server confirmed that the size of /media/database is 318 GB.

[martinmarques]

Check that you don't have tablespaces somewhere else. The query I shared gives the size of all the data, what's inside PGDATA and anything in other tablespaces.

[thoro]

@JP95Git It might be that the DB share is compressed, try to use du -h /path/to/pgdata and du -h --apparent-size /path/to/pgdata

It seems all it correct with the backup.