2

pg_dump has a great option, it allows you to select custom format for the output of the backup.

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

It can be specified with -Fc argument. e.g.

pg_dump -Fc mydb > db.dump

I have a few databases and roles/users, so I want to back everything up in one file with one command.

There is a great command pg_dumpall for that.

pg_dumpall is a utility for writing out ("dumping") all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to psql to restore the databases. It does this by calling pg_dump for each database in a cluster. pg_dumpall also dumps global objects that are common to all databases.

The output of pg_dumpall is an SQL-script, but I would like to have it as a custom-format archive.

Unfortunately, I can't provide -Fc argument to pg_dumpall.

Is there a way to back up all DBs and roles etc. using custom-format archive?

ald
  • 31
  • 5
  • 1
    You need to write a little batch/shell script that loops over all databases and runs `pg_dump -Fc` for each. –  Oct 19 '21 at 17:00
  • How can I get a list of databases in my bash script? – ald Oct 20 '21 at 15:18

1 Answers1

0

Here is a script that you must modify and run to run pg_dump on all your databases:

Step 1: saving list of dbs to file

psql -h 127.0.0.1 \ # set your host
 -p 5432 \ # and port
 -U admin \ # name of user which has access to all databases
 -d order \ # name of any existing database
 -t \ # tell psql not to print column names
 -c 'SELECT d.datname as "Name" FROM pg_catalog.pg_database d;' \ # sql to list databases
 | grep -v template | grep -v postgres | tr -d ' ' > dbs.txt # filtering databases that we don't want to backup

mkdir backups
cat dbs.txt | xargs -P 0 \ # run in parallel for each db
 -n 1 \ # pass 1 arg to command
 -I{} -x \ # -I{} - alias for argument, -x - print executing command
  pg_dump -Fc \ # custom backup format
   -h 127.0.0.1 \ # your host
   -p 5432 \ # and port
   --exclude-schema=debezium \ # maybe you want to exclude some schema
   -x \ # ignore roles
   -O \ # ignore ownership
   --no-publications \ # ignore publications
   -U admin \ # user
   -f backups/{} \ # path where to store backups
   -d {} # passing name of database from xarg
Alik Khilazhev
  • 995
  • 6
  • 18