0

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

  1. 350mb inside psql with \l
  2. 350mb with du -sch at postgres' folder
  3. 158mb with pgdumpall to file

running pgsql 9.5

3

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)

0

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.

  • Thanks both. That answer my question – dquake Sep 12 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.