In the previous sections, we learned about pg_dump and pg_restore, which are two vital programs when it comes to creating backups. The thing is, pg_dump creates database dumps—it works on the database level. If we want to back up an entire instance, we have to make use of pg_dumpall or dump all of the databases separately. Before we dig into that, it makes sense to see how pg_dumpall works:

pg_dumpall > /tmp/all.sql  

Let's see, pg_dumpall will connect to one database after the other and send stuff to standard out, where you can process it with Unix. Note that pg_dumpall can be used just like pg_dump. However, it has some downsides. It does not support a custom or directory format, and therefore does not offer multicore support. This means that we will be stuck with one thread.

However, there is more to pg_dumpall. Keep in mind that users live on the instance level. If you create a normal database dump, you will get all of the permissions, but you won't get all of the CREATE USER statements. Those globals are not included in a normal dump—they will only be extracted by pg_dumpall.

If we only want the globals, we can run pg_dumpall using the -g option:

pg_dumpall -g > /tmp/globals.sql  

In most cases, you might want to run pg_dumpall -g, along with a custom or directory format dump to extract your instances. A simple backup script might look such as this:

#!/bin/sh
    
BACKUP_DIR=/tmp/
    
pg_dumpall -g > $BACKUP_DIR/globals.sql
    
for x in $(psql -c "SELECT datname FROM  pg_database
   WHERE  datname NOT IN ('postgres', 'template0', 'template1')" postgres -A -t)
do
pg_dump -Fc $x > $BACKUP_DIR/$x.fc done  

It will first dump the globals and then loop through the list of databases to extract them one by one in a custom format.