Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Can someone confirm if pg_dumpall does a vacuum? I cannot explain the size differences between a local and dumped database but thinking duplicate/rewritten entries are ignored
psql with \ldu -sch at postgres' folderpgdumpall to filerunning pgsql 9.5
The size reported by psql -l includes the size of all indexes.
The dump only contains the definition of the indexes, but no data for them, that's why the dump file is (substantially) smaller then the reported size on disk.
In addition to the size of the indexes, the dump only contains the raw data, while the size on disk might include table bloat (free space inside the table's data files)
Short answer: No.
dump_all takes the current records in each table and writes them to a file.
Any "old" or inaccessible rows, that are eligible for vacuuming, are not written, so the dump file will be smaller.
As a_horse_with_no_name says, only Index definitions are written to the file, saving yet more space.
The indexes are reconstructed from scratch when the dump is loaded into a new database.
