pg_dump vs pg_dumpall? which one to use to database backups?

The usual process is:

• pg_dumpall --globals-only to get users/roles/etc
• pg_dump -Fc for each database to get a nice compressed dump suitable for use with pg_restore.

Yes, this kind of sucks. I’d really like to teach pg_dump to embed pg_dumpall output into -Fc dumps, but right now unfortunately it doesn’t know how so you have to do it yourself.

Up until PostgreSQL 11 there was also a nasty caveat with this approach: Neither pg_dump, nor pg_dumpall in --globals-only mode would dump user access GRANTs on DATABASEs. So you pretty much had to extract them from the catalogs or filter a pg_dumpall. This is fixed in PostgreSQL 11; see the release notes.

Make pg_dump dump the properties of a database, not just its contents (Haribabu Kommi)

Previously, attributes of the database itself, such as database-level GRANT/REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall. Now pg_dump --create and pg_restore --create will restore these database properties in addition to the objects within the database. pg_dumpall -g now only dumps role- and tablespace-related attributes. pg_dumpall‘s complete output (without -g) is unchanged.

You should also know about physical backups – pg_basebackup, PgBarman and WAL archiving, PITR, etc. These offer much “finer grained” recovery, down to the minute or individual transaction. The downside is that they take up more space, are only restoreable to the same PostgreSQL version on the same platform, and back up all tables in all databases with no ability to exclude anything.