I'm writing a backup script. Right now, I only have one database on my postgresql server. I'm deciding if I should use pg_dump or pg_dumpall when backing up the server. As far as I can tell, pg_dumpall cannot compress the dumps automatically and it only dumps data in the standard SQL text file format. This means that I would not be able to use pg_restore to selectively restore the database, correct?
What is *NOT* included in a pg_dump that IS in pg_dumpall (Other than all the databases)? Things like user-defined functions and datatypes? Roles? Views?
I was leaning towards pg_dumpall, but then I realized that it only dumps in the standard SQL text file format, and it cannot be compressed automatically.
