One of the limitation of pg_dumpall up to PostgreSQL version 18 is, that you can only dump in text mode. While pg_dump can dump in various formats (plain,custom,tar and directory) since ages. pg_dump was always limited to text mode. This will change with PostgreSQL 19 because this was committed recently.

The default is still plain text but now you have the same options as pg_dump:

postgres@:/home/postgres/ [pgdev] pg_dumpall --help | grep -A 1 -w format
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))

Lets assume we have have five user databases like this:

postgres@:/home/postgres/ [pgdev] for i in {1..5}; do createdb ${i}; done
postgres@:/home/postgres/ [pgdev] psql -l
                                                        List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    |   Locale    | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+-------------+-------------+-------------+-----------+-----------------------
 1         | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 2         | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 3         | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 4         | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 5         | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 postgres  | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | 
 template0 | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | =c/postgres          +
           |          |          |                 |             |             |             |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US-x-icu |           | =c/postgres          +
           |          |          |                 |             |             |             |           | postgres=CTc/postgre

… and a table in each:

postgres@:/home/postgres/ [pgdev] for i in {1..5}; do psql -c "create table t${i} as select * from generate_series(1,100)" ${i}; done
SELECT 100
SELECT 100
SELECT 100
SELECT 100
SELECT 100

Dumping that using the default mode of pg_dumpall results in a plain text as usual:

postgres@:/home/postgres/ [pgdev] pg_dumpall > dmp.sql
postgres@:/home/postgres/ [pgdev] head -50 dmp.sql | egrep -v "^$"
postgres@:/home/postgres/ [pgdev] head -50 dmp.sql | egrep -v "^$|--"
\restrict fkHQ08xl5cQnk99WM6prphaZBPdqlARNru7uAdgFRFMUJm7RdeI6Elk8zoOr8mg
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
\unrestrict fkHQ08xl5cQnk99WM6prphaZBPdqlARNru7uAdgFRFMUJm7RdeI6Elk8zoOr8mg
\connect template1
\restrict EFrJ3U9GH8T5SSHUVhWam0FHhJyxp1pAMXSTXTLVaiHayK4ASUZzweTUywMJbmq

Starting with PostgreSQL 19 this can be changed to any of the other supported formats. The tar format will give you a directory structure like this:

postgres@:/home/postgres/ [pgdev] pg_dumpall --format=t -f dmp_tar
postgres@:/home/postgres/ [pgdev] ls -la dmp_tar/
total 12
drwx------. 1 postgres postgres   46 Feb 27 08:53 .
drwx------. 1 postgres postgres  436 Feb 27 08:53 ..
drwx------. 1 postgres postgres  110 Feb 27 08:53 databases
-rw-r--r--. 1 postgres postgres  448 Feb 27 08:53 map.dat
-rw-r--r--. 1 postgres postgres 1810 Feb 27 08:53 toc.glo
postgres@:/home/postgres/ [pgdev] ls -la dmp_tar/databases/
total 56
drwx------. 1 postgres postgres  110 Feb 27 08:53 .
drwx------. 1 postgres postgres   46 Feb 27 08:53 ..
-rw-r--r--. 1 postgres postgres 6656 Feb 27 08:53 16388.tar
-rw-r--r--. 1 postgres postgres 6656 Feb 27 08:53 16389.tar
-rw-r--r--. 1 postgres postgres 6656 Feb 27 08:53 16390.tar
-rw-r--r--. 1 postgres postgres 6656 Feb 27 08:53 16391.tar
-rw-r--r--. 1 postgres postgres 6656 Feb 27 08:53 16392.tar
-rw-r--r--. 1 postgres postgres 6656 Feb 27 08:53 1.tar
-rw-r--r--. 1 postgres postgres 5632 Feb 27 08:53 5.tar

The “map” file maps OIDs to database names:

postgres@:/home/postgres/ [pgdev] cat dmp_tar/map.dat 
#################################################################
# map.dat
#
# This file maps oids to database names
#
# pg_restore will restore all the databases listed here, unless
# otherwise excluded. You can also inhibit restoration of a
# database by removing the line or commenting out the line with# a # mark.
#################################################################
1 template1
16388 1
16389 2
16390 3
16391 4
16392 5
5 postgres

The “toc” file, as usual is the table of contents which can be listed with pg_restore:

postgres@:/home/postgres/ [pgdev] pg_restore -l dmp_tar/toc.glo
;
; Archive created at 2026-02-27 08:53:53 CET
;     dbname: postgres
;     TOC Entries: 10
;     Compression: none
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped by pg_dump version: 19devel dbi services build
;
;
; Selected TOC Entries:
;
1; 0 0 default_transaction_read_only - default_transaction_read_only 
2; 0 0 client_encoding - client_encoding 
3; 0 0 standard_conforming_strings - standard_conforming_strings 
4; 0 0 DROP_GLOBAL - DATABASE "1" 
5; 0 0 DROP_GLOBAL - DATABASE "2" 
6; 0 0 DROP_GLOBAL - DATABASE "3" 
7; 0 0 DROP_GLOBAL - DATABASE "4" 
8; 0 0 DROP_GLOBAL - DATABASE "5" 
9; 0 0 DROP_GLOBAL - ROLE postgres 
10; 0 0 ROLE - ROLE postgres

This comes with the possibility (as with the plain text format) to restore individual databases out of this global dump, or to reload global object only:

postgres@:/home/postgres/ [pgdev] pg_restore --globals-only dmp_tar/ -d postgres --verbosepg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_restore: creating default_transaction_read_only "default_transaction_read_only"
pg_restore: creating client_encoding "client_encoding"
pg_restore: creating standard_conforming_strings "standard_conforming_strings"
pg_restore: creating ROLE "ROLE postgres"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 10; 0 0 ROLE ROLE postgres (no owner)
pg_restore: error: could not execute query: ERROR:  role "postgres" already exists
Command was: CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

pg_restore: database restoring skipped because option -g/--globals-only was specified
pg_restore: warning: errors ignored on restore: 1

postgres@:/home/postgres/ [pgdev] pg_restore --globals-only dmp_tar/ -d postgres --verbosepg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_restore: creating default_transaction_read_only "default_transaction_read_only"
pg_restore: creating client_encoding "client_encoding"
pg_restore: creating standard_conforming_strings "standard_conforming_strings"
pg_restore: creating ROLE "ROLE postgres"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 10; 0 0 ROLE ROLE postgres (no owner)
pg_restore: error: could not execute query: ERROR:  role "postgres" already exists
Command was: CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

pg_restore: database restoring skipped because option -g/--globals-only was specified
pg_restore: warning: errors ignored on restore: 1

Another option is to run multiple restore processes to load databases in parallel. Nice to have that option and as usual: Thanks to all involved.

Post Views: 12
Thumbnail [60x60]
by
Daniel Westermann