0

Do pg_dump and pg_restore make the size of the restored data half?

I have a disk Postgres DB data with a size of 93GB, I used pg_dump and restore to backup and restore the database into another Postgres server.

Below steps, I used to backup and restore the Postgres database from one server to another server.

# Original database disk size is 93GB in main DB server to be backed up and restored.
root@dev-postgres-0:/# du -sh /var/lib/postgresql/data/
93G /var/lib/postgresql/data/
root@dev-postgres-0:/# 

## 1st Method used to Backup and restore
# To backup test database 
root@test-postgres-0:/home/postgres/pgdata# pg_dump -U test_admin -h 10.44.5.10 -p 5432 -F t test > test.tar

root@test-postgres-0:/home/postgres/pgdata# ls
test.tar  lost+found  pgroot
root@test-postgres-0:/home/postgres/pgdata#.

# Size of tarball created 
root@fx-postgres-0:/home/postgres/pgdata# du -sh test.tar 
76G test.tar
root@fx-postgres-0:/home/postgres/pgdata#

## Used the below command to restore the above-created tarball
root@test-postgres-0:/home/postgres# pg_restore -U test_admin -d test test.tar

root@test-postgres-0:/home/postgres#

## Size of the restored database directory was 45GB
root@test-postgres-0:/home/postgres/pgdata# du -sh pgroot/
45G pgroot/
root@test-postgres-0:/home/postgres/pgdata#


## 2nd Method used to Backup and restore postgres db
# To backup test database 
root@test-postgres-0:/home/postgres/pgdata# pg_dump -U test_admin -h 10.44.5.10 -p 5432 test > test.sql

root@test-postgres-0:/home/postgres/pgdata# ls
test.sql  lost+found  pgroot
root@test-postgres-0:/home/postgres/pgdata#.

# Size of tarball created was 76GB
root@fx-postgres-0:/home/postgres/pgdata# du -sh test.sql 
76G test.sql
root@fx-postgres-0:/home/postgres/pgdata#

## Used the below command to restore the above-created sql file
root@test-postgres-0:/home/postgres/pgdata# psql -U test_admin test < test.sql

root@test-postgres-0:/home/postgres/pgdata#

## Size of the restored database directory was 45GB
root@test-postgres-0:/home/postgres/pgdata# du -sh pgroot/
45G pgroot/
root@test-postgres-0:/home/postgres/pgdata#

Though everything went well, the dump file was created successfully without any error and got restored to another database successfully using the above two methods, but restored database disk size is 45GB, almost half of the original database disk size of 93GB.

So why such a strange behavior with pg_dump and pg_restore ? Did I miss anything or some other/extra stuff need to be done to get full data size.

devops-admin
  • 1,447
  • 1
  • 15
  • 26
  • In operation, the writing of the data happens optimised for speed, not efficiency. It's entirely possible a bunch of dead data has accumulated in the storage files, which gets omitted/purged/optimised during an export/import. Are you vacuuming your database occasionally…? – deceze Jul 26 '22 at 13:34
  • No. we are not vacuuming/cleaning the database anytime, right from start as of now. Also could please provide us a reference link with above mention information, that would be really helpful besides what you have provided. – devops-admin Jul 26 '22 at 14:12
  • See the above duplicates, and https://dba.stackexchange.com/q/59895/25140. – deceze Jul 26 '22 at 14:28

0 Answers0