different sizes of postgres databases. move db from server1 to server2

Question

I'm trying to move a database from server1 to server2. I read docummentation of postgres, and I think everything is right except that after I dumped db from server1 moved it and restored on server2 the sizes are different.

Server1

 SELECT pg_size_pretty(pg_database_size('db_name'));                                                                                                   
 pg_size_pretty 
----------------
 118 MB
(1 row)

Server2

 select pg_size_pretty(pg_database_size('db_name'));
 pg_size_pretty 
----------------
 81 MB
(1 row)

I've made the dump with -a -Fc -Z9 flags and restore with pg_restore -U user -c -d db_name dump_file.dump

My questions are:

• Why the sizes are different?
• What is the correct approach to move a database like this if the application that access the db is a rails one? (I mean, I want a restore that doesn't affect future rails migrations)
• Do you have other ideas? Other docummentation that I can read?

Thank you for reading this.

Answer 1

This is fine and normal.

Dump and reload produces a more compact database because there's no dead space in the tables and the b-tree indexes are newly reindexed so they're packed and well balanced. You'll find the size is the same or much closer if you:

VACUUM FULL;
REINDEX DATABASE mydb;

on the main DB.

On a side note, though, I strongly recommend restoring using the -1 option to pg_restore unless you need parallel restore. That way you'll either get an empty DB or a complete restore. Of course, you should also always check the return codes from pg_dump and pg_restore.

No comment on the Rails part, I don't know what you're referring to. Please don't do multi-questions like this, they're hard to answer definitively and you get different "correct" answers in different parts. Post a new SO question for a new question.