Postgres pg_dump dump file size bigger than pg_database_size

Question

I have a client made a backup using pg_dump and its size is almost 700GB while the pg_database_size is just about 195GB. I already did run the VACUUM FULL but the result still the same.

How could this be possible? As far as I know dump file should be smaller because of compression.

I used PostgreSQL version 9.5. Firstly i was thinking it's a bug, then i used a binary of PostgreSQL 10 but the result is also the same.

We use regular command for backup

pg_dump -U username -d dbname > dbname.sql

What could be the problem?

Answer 1

pg_dump file can be smaller than database size due to the following reasons:

1. pg_dump doesn't dump the indexes blocks. It only stores the definitions of indexes.
2. If your database has boats, then the size of the database will be higher.

If you SQL Dump is bigger than the database, it could be possible you have bigger data for TEXT/JSONB/JSON/BYTEA, etc. datatypes which get toasted and compressed in PG.