PG_DUMPALL(1)

📦 version 16.15, in opensuse-leap 16.0

🌸 PG_DUMPALL(1) 🌸

okay bestie so we're doing the pg_dumpall page from the PostgreSQL 16.15 Documentation and honestly? iconic. grab a boba, this one's long but we're getting through it together 🫶

🎀 NAME 🎀

pg_dumpall — extract a PostgreSQL database cluster into a script file ✨

📜 SYNOPSIS 📜

pg_dumpall [connection-option...] [option...]

💖 DESCRIPTION 💖

So pg_dumpall is literally the utility that writes out (a.k.a. "dumps") all the PostgreSQL databases in a cluster into one single script file. one file. every database. slay 💅

That script file is full of SQL commands, and you can feed it straight into psql(1) to restore everything. how does it do it? it just calls pg_dump(1) once for each database in the cluster, one by one, no cap.

On top of that, pg_dumpall also dumps the global objects — the stuff that's common to every database, namely database roles, tablespaces, and privilege grants for configuration parameters. and here's the tea: pg_dump does not save these objects. so this is pg_dumpall's whole moment 🎀

Because pg_dumpall reads tables from all the databases, you're most likely gonna have to connect as a database superuser to get a complete dump. and yep, you'll also need superuser privileges to actually run the saved script, since it's gotta be allowed to add roles and create databases. no superuser, no party 😭

The SQL script goes to standard output. want it in a file? use the -f/--file option, or just be a shell-gremlin and use shell operators to redirect it. both are valid 💜

Also lowkey important: pg_dumpall has to connect to the PostgreSQL server several times — once per database. so if you're using password authentication, it's gonna ask for a password each time. that's a lot of typing. the convenient move here is to have a ~/.pgpass file. see Section 34.16 for more info 🧚

🚨 Warning 🚨

Restoring a dump causes the destination to execute arbitrary code of the source superusers' choice. Partial dumps and partial restores do not limit that. If the source superusers are not trusted, the dumped SQL statements must be inspected before restoring. Note that the client running the dump and restore need not trust the source or destination superusers. 💀

✨ OPTIONS ✨

okay so the following command-line options control the content and format of the output. there are a lot of them. we're doing all of them. buckle up 🦋

  • -a, --data-only — dump only the data, not the schema (the data definitions). data girlie only 💅
  • -c, --clean — emit SQL commands to DROP all the dumped databases, roles, and tablespaces before recreating them. super useful when your restore is meant to overwrite an existing cluster. heads up though: if any of those objects don't exist in the destination cluster, you'll get ignorable error messages during restore — unless --if-exists is also specified. so like, pair them together, iykyk ✨
  • *-E *encoding, --encoding=*encoding*** — create the dump in the specified character set encoding. by default the dump is created in the database encoding. (another way to get the same result is to set the PGCLIENTENCODING` environment variable to the dump encoding you want 👑)
  • -f filename, `--file=filename — send output to the specified file. omit it and standard output is used. simple as that 🎀
  • -g, --globals-only — dump only global objects (roles and tablespaces), no databases. globals only, periodt.
  • -O, --no-owner — don't output commands that set ownership of objects to match the original database. by default, pg_dumpall issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created schema elements — and those statements will fail when the script is run unless it's started by a superuser (or the same user that owns all of the objects in the script). so to make a script that any user can restore, but which gives that user ownership of all the objects, specify -O. it's giving hand-it-over energy 🫶
  • -r, --roles-only — dump only roles, no databases or tablespaces. roles only, no crumbs.
  • -s, --schema-only — dump only the object definitions (the schema), not the data. structure over chaos 💖
  • *-S *username, --superuser=*username*** — specify the superuser user name to use when disabling triggers. this is only relevant if --disable-triggers` is used. (usually it's better to leave this out and instead just start the resulting script as superuser — trust the process ✨)
  • -t, --tablespaces-only — dump only tablespaces, no databases or roles. tablespace supremacy, nothing else invited 🌈
  • -v, --verbose — verbose mode! this makes pg_dumpall output start/stop times to the dump file, plus progress messages to standard error. repeat the option and you get additional debug-level messages on standard error. it's also passed down to pg_dump. very main character energy 👑
  • -V, --version — print the pg_dumpall version and exit. goodbye 💅
  • -x, --no-privileges, --no-acl — prevent dumping of access privileges (grant/revoke commands). not today, permissions 🚫
  • --binary-upgrade — this option is for use by in-place upgrade utilities. using it for other purposes is not recommended or supported. and the behavior of the option may change in future releases without notice. fr fr, don't touch it unless you're the upgrade utility 😭
  • --column-inserts, --attribute-inserts — dump data as INSERT commands with explicit column names (INSERT INTO table (column, ...) VALUES ...). this makes restoration very slow — it's mainly useful for making dumps that can be loaded into non-PostgreSQL databases. slow but portable, the tradeoff is real 🍓
  • --disable-dollar-quoting — disables dollar quoting for function bodies and forces them to be quoted using SQL standard string syntax. no dollar signs, no vibes 🎀
  • --disable-triggers — only relevant when creating a data-only dump. it tells pg_dumpall to include commands that temporarily disable triggers on the target tables while the data is restored. use this if you've got referential integrity checks or other triggers on the tables that you do not want going off during the data restore 🚨
Presently, the commands emitted for --disable-triggers must be done as superuser. so you should also specify a superuser name with -S, or preferably be careful to start the resulting script as a superuser. no cap, this one's a whole thing 💀
  • --exclude-database=*pattern* — do not dump databases whose name matches pattern. multiple patterns can be excluded by writing multiple --exclude-database switches. the pattern parameter is interpreted according to the same rules used by psql's \d commands (see Patterns), so you can also exclude multiple databases by writing wildcard characters in the pattern. when using wildcards, be careful to quote the pattern if needed to prevent shell wildcard expansion — the shell will absolutely steal your shine otherwise 🦋
  • --extra-float-digits=*ndigits* — use the specified value of extra_float_digits when dumping floating-point data, instead of the maximum available precision. routine dumps made for backup purposes should not use this option. backup dumps, boring; this option, specific 💅
  • --if-exists — use DROP ... IF EXISTS commands to drop objects in --clean mode. this suppresses "does not exist" errors that might otherwise get reported. and heads up: this option is not valid unless --clean is also specified. they're a package deal 👑✨
  • --inserts — dump data as INSERT commands (rather than COPY). restoration gets very slow, and it's mainly useful for making dumps that can be loaded into non-PostgreSQL databases. ngl there's a catch: the restore might fail altogether if you've rearranged column order. the --column-inserts option is safer, though even slower. risk it or don't, bestie 😭
  • --load-via-partition-root — when dumping data for a table partition, make the COPY or INSERT statements target the root of the partitioning hierarchy that contains it, rather than the partition itself. this makes the appropriate partition get re-determined for each row when the data is loaded. useful when restoring on a server where rows don't always fall into the same partitions as they did on the original server — for example, if the partitioning column is of type text and the two systems have different definitions of the collation used to sort the partitioning column. fancy but purposeful 🧚
  • --lock-wait-timeout=*timeout* — don't wait forever to acquire shared table locks at the beginning of the dump. instead, fail if it can't lock a table within the specified timeout. the timeout can be specified in any of the formats accepted by SET statement_timeout. boundaries! we love them 🌸
  • --no-comments — do not dump comments. silence is golden 💜
  • --no-publications — do not dump publications.
  • --no-role-passwords — do not dump passwords for roles. when restored, roles will have a null password, and password authentication will always fail until the password is set. since password values aren't needed when this option is specified, the role info is read from the catalog view pg_roles instead of pg_authid. so this option also helps if access to pg_authid is restricted by some security policy. security girlie behavior 🔥
  • --no-security-labels — do not dump security labels.
  • --no-subscriptions — do not dump subscriptions.
  • --no-sync — by default, pg_dumpall waits for all files to be written safely to disk. this option makes pg_dumpall return without waiting, which is faster — but it means a subsequent operating system crash can leave the dump corrupt. generally this option is useful for testing but should not be used when dumping data from a production installation. fast and dangerous, pick one ⚠️
  • --no-table-access-method — do not output commands to select table access methods. with this option, all objects will be created with whichever table access method is the default during restore. default vibes only.
  • --no-tablespaces — do not output commands to create tablespaces nor select tablespaces for objects. with this option, all objects are created in whichever tablespace is the default during restore. 🎀
  • --no-toast-compression — do not output commands to set TOAST compression methods. with this option, all columns will be restored with the default compression setting. let the default cook 💅
  • --no-unlogged-table-data — do not dump the contents of unlogged tables. this has no effect on whether the table definitions (schema) are dumped — it only suppresses dumping the table data. so the schema stays, the data dips ✨
  • --on-conflict-do-nothing — add ON CONFLICT DO NOTHING to INSERT commands. this option is not valid unless --inserts or --column-inserts is also specified. team effort, bestie 🫶
  • --quote-all-identifiers — force quoting of all identifiers. this option is recommended when dumping a database from a server whose PostgreSQL major version is different from pg_dumpall's, or when the output is intended to be loaded into a server of a different major version. by default, pg_dumpall quotes only the identifiers that are reserved words in its own major version — which sometimes causes compatibility issues with servers of other versions that may have slightly different sets of reserved words. using --quote-all-identifiers prevents those issues, at the price of a harder-to-read dump script. safe but ugly, iconic tradeoff 🌈
  • --restrict-key=restrict_key — bestie, this drops the string you give it in as the psql \restrict key inside the dump output ✨. If you don't hand it one, pg_dumpall just makes up a random one whenever it needs to. Heads up: keys can only contain alphanumeric characters, no funny business 🎀.
Lowkey this one's mostly for testing and for situations where you need repeatable output (like comparing dump files side by side 🙈). It is not recommended for general use — a malicious server that somehow already knows the key might be able to inject arbitrary code that runs on the machine executing psql with that dump output 💀. So like… be careful, okay?
  • --rows-per-insert=nrows — dumps your data as INSERT commands instead of COPY 🧚. This sets the max number of rows per INSERT. The value has to be a number greater than zero, no zero, no negatives, periodt. The cute part: if something goes wrong during the restore, only the rows that are part of the problematic INSERT get lost — not the whole table's contents 🫶.
  • --use-set-session-authorization — outputs SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to figure out object ownership 👑. It makes the dump more standards-compatible, but depending on the history of the objects in the dump, it might not restore properly 😭 — so don't say I didn't warn you.
  • -?, --help — shows help about the pg_dumpall command line arguments and dips ✌️.

The following command-line options control the database connection parameters 🔌 (yes, this is a whole other little section, stay with me).

  • -d connstr, --dbname=connstr — specifies the parameters used to connect to the server as a connection string, and these will override any conflicting command line options 💅.

The option is called --dbname for consistency with other client applications, but because pg_dumpall has to connect to many databases, the database name inside the connection string is ignored 🙈. Use the -l option to specify the name of the database used for the initial connection — that's the one that dumps global objects and figures out which other databases should be dumped.

  • -h host, --host=host — specifies the host name of the machine where the database server is running 🖥️. If the value begins with a slash, it's used as the directory for the Unix domain socket. The default comes from the PGHOST environment variable if it's set, otherwise a Unix domain socket connection is attempted ✨.
  • -l dbname, --database=dbname — specifies the name of the database to connect to for dumping global objects and discovering what other databases should be dumped 🎀. If you don't specify it, the postgres database gets used, and if that one doesn't exist, template1 is used instead.
  • -p port, --port=port — specifies the TCP port or local Unix domain socket file extension the server is listening on for connections 🔥. Defaults to the PGPORT environment variable if set, or a compiled-in default.
  • -U username, --username=username — the user name to connect as 👤.
  • -w, --no-password — never ever issue a password prompt 🙅. If the server requires password authentication and no password is available by other means (like a .pgpass file), the connection attempt just fails. This one's a lifesaver in batch jobs and scripts where there's literally no human around to type a password 💀.
  • -W, --password — forces pg_dumpall to prompt for a password before connecting to a database 🔐.

Ngl this option is never essential, since pg_dumpall will automatically prompt for a password if the server demands password authentication anyway. But the catch is pg_dumpall wastes a whole connection attempt finding out the server wants a password 😭. In some cases it's worth typing -W just to skip that extra attempt. Also note the password prompt will happen again for each database to be dumped, so honestly it's usually better to set up a ~/.pgpass file than to rely on manual password entry 🍓.

  • --role=rolename — specifies a role name to be used to create the dump 🎭. This makes pg_dumpall issue a SET ROLE rolename command after connecting to the database. Super useful when the authenticated user (the one from -U) lacks the privileges pg_dumpall needs but can switch to a role that has the required rights. Some installations have a policy against logging in directly as a superuser, and this option lets dumps happen without violating that policy 👑.

🌸 ENVIRONMENT 🌸

  • PGHOST
  • PGOPTIONS
  • PGPORT
  • PGUSER

Default connection parameters ✨

  • PG_COLOR — specifies whether to use color in diagnostic messages 🌈. Possible values are always, auto and never.

This utility, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 34.15) 🦋.

💜 NOTES 💜

Since pg_dumpall calls pg_dump internally, some diagnostic messages will refer to pg_dump — don't be confused bestie, it's the same operation under the hood 🙈.

The --clean option can be useful even when your intention is to restore the dump script into a fresh cluster 🎀. Using --clean authorizes the script to drop and re-create the built-in postgres and template1 databases, making sure those databases keep the same properties they had in the source cluster (things like locale and encoding, iykyk). Without the option, those databases keep their existing database-level properties and any pre-existing contents 👀.

Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics 📊. You can also run vacuumdb -a -z to analyze all databases — one command, all the slay 💅.

Okay so real talk: the dump script should not be expected to run completely without errors 😭. In particular, because the script issues CREATE ROLE for every role that exists in the source cluster, it's certain to get a “role already exists” error for the bootstrap superuser — unless the destination cluster was initialized with a different bootstrap superuser name. This error is harmless and should be ignored, fr fr. Using --clean is likely to produce additional harmless error messages about non-existent objects, though you can minimize those by adding --if-exists ✨.

pg_dumpall requires all needed tablespace directories to exist before the restore 🗂️ — otherwise database creation will fail for databases in non-default locations. No cap.

It is generally recommended to use the -X (--no-psqlrc) option when restoring a database from a pg_dumpall script, to ensure a clean restore process and prevent potential conflicts with non-default psql configurations 🧚. Also — because the pg_dumpall script may include psql meta-commands, it may be incompatible with clients other than psql 👀.

⭐ EXAMPLES ⭐

To dump all databases:

$ pg_dumpall > db.out

To restore database(s) from this file, you can use:

$ psql -X -f db.out -d postgres

It is not important which database you connect to here, since the script file created by pg_dumpall contains the appropriate commands to create and connect to the saved databases 🫶. The one exception: if you specified --clean, you must connect to the postgres database initially — the script will attempt to drop other databases immediately, and that will fail for the database you're currently connected to 💀.

🦋 SEE ALSO 🦋

Check pg_dump(1) for details on possible error conditions 👑.

And that's the whole page, bestie! 💖 This was PostgreSQL 16.15, dated 2026, and it's the PG_DUMPALL(1) manual page — go forth and dump responsibly 🌈✨🎀

📦 where this page comes from