I was trying to achieve smallest file possible so tried the xz. Right now the db size returned from SELECT pg_size_pretty(pg_database_size('postgres') is 1.4 GB and the size of the dump with xz is 2.2 GB.
Is there a limit to the size of the database that pg_dump will run on?
No limit..
Will it work when db is in TBs?
... though this would big a rather bad idea. It will work, but it will be damn slow. And its restoration will last way too much. Much better to do PITR backups with databases bigger than 100GB.
Also, I want to know if anyone has found any handy cron scripts for automated backups to run on a daily/weekly basis? i found some on google, but interested to know if there are better ones.
I really like pg_back (though, as a disclaimer, I should say this is written by one of my co-workers, Nicolas Thauvin). Anyway, it's on https://github.com/orgrim/pg_back if you want to take a look.