1PG_DUMPALL(1) PostgreSQL Client Applications PG_DUMPALL(1)
2
6 pg_dumpall - extract a PostgreSQL database cluster into a script file
7
10 pg_dumpall [ option... ]
11
13 pg_dumpall is a utility for writing out (``dumping'') all PostgreSQL
14 databases of a cluster into one script file. The script file contains
15 SQL commands that can be used as input to psql(1) to restore the data‐
16 bases. It does this by calling pg_dump(1) for each database in a clus‐
17 ter. pg_dumpall also dumps global objects that are common to all data‐
18 bases. (pg_dump does not save these objects.) This currently includes
19 information about database users and groups, and access permissions
20 that apply to databases as a whole.
21 Since pg_dumpall reads tables from all databases you will most likely
23 have to connect as a database superuser in order to produce a complete
24 dump. Also you will need superuser privileges to execute the saved
25 script in order to be allowed to add users and groups, and to create
26 databases.
27 The SQL script will be written to the standard output. Shell operators
29 should be used to redirect it into a file.
30 pg_dumpall needs to connect several times to the PostgreSQL server
32 (once per database). If you use password authentication it is likely to
33 ask for a password each time. It is convenient to have a ~/.pgpass file
34 in such cases. See in the documentation for more information.
35
37 The following command-line options control the content and format of
38 the output.
39 -a
41 --data-only
43 Dump only the data, not the schema (data definitions).
44 -c
46 --clean
48 Include SQL commands to clean (drop) databases before recreating
49 them. DROP commands for roles and tablespaces are added as well.
50 -d
52 --inserts
54 Dump data as INSERT commands (rather than COPY). This will make
55 restoration very slow; it is mainly useful for making dumps that
56 can be loaded into non-PostgreSQL databases. Note that the
57 restore may fail altogether if you have rearranged column order.
58 The -D option is safer, though even slower.
59 -D
61 --column-inserts
63 --attribute-inserts
65 Dump data as INSERT commands with explicit column names (INSERT
66 INTO table (column, ...) VALUES ...). This will make restoration
67 very slow; it is mainly useful for making dumps that can be
68 loaded into non-PostgreSQL databases.
69 -g
71 --globals-only
73 Dump only global objects (roles and tablespaces), no databases.
74 -i
76 --ignore-version
78 Ignore version mismatch between pg_dumpall and the database
79 server.
80 pg_dumpall can handle databases from previous releases of Post‐
82 greSQL, but very old versions are not supported anymore (cur‐
83 rently prior to 7.0). Use this option if you need to override
84 the version check (and if pg_dumpall then fails, don't say you
85 weren't warned).
86 -o
88 --oids Dump object identifiers (OIDs) as part of the data for every ta‐
90 ble. Use this option if your application references the OID col‐
91 umns in some way (e.g., in a foreign key constraint). Other‐
92 wise, this option should not be used.
93 -O
95 --no-owner
97 Do not output commands to set ownership of objects to match the
98 original database. By default, pg_dumpall issues ALTER OWNER or
99 SET SESSION AUTHORIZATION statements to set ownership of created
100 schema elements. These statements will fail when the script is
101 run unless it is started by a superuser (or the same user that
102 owns all of the objects in the script). To make a script that
103 can be restored by any user, but will give that user ownership
104 of all the objects, specify -O.
105 -s
107 --schema-only
109 Dump only the object definitions (schema), not data.
110 -S username
112 --superuser=username
114 Specify the superuser user name to use when disabling triggers.
115 This is only relevant if --disable-triggers is used. (Usually,
116 it's better to leave this out, and instead start the resulting
117 script as superuser.)
118 -v
120 --verbose
122 Specifies verbose mode. This will cause pg_dumpall to output
123 start/stop times to the dump file, and progress messages to
124 standard error. It will also enable verbose output in pg_dump.
125 -x
127 --no-privileges
129 --no-acl
131 Prevent dumping of access privileges (grant/revoke commands).
132 --disable-dollar-quoting
134 This option disables the use of dollar quoting for function bod‐
135 ies, and forces them to be quoted using SQL standard string syn‐
136 tax.
137 --disable-triggers
139 This option is only relevant when creating a data-only dump. It
140 instructs pg_dumpall to include commands to temporarily disable
141 triggers on the target tables while the data is reloaded. Use
142 this if you have referential integrity checks or other triggers
143 on the tables that you do not want to invoke during data reload.
144 Presently, the commands emitted for --disable-triggers must be
146 done as superuser. So, you should also specify a superuser name
147 with -S, or preferably be careful to start the resulting script
148 as a superuser.
149 --use-set-session-authorization
151 Output SQL-standard SET SESSION AUTHORIZATION commands instead
152 of ALTER OWNER commands to determine object ownership. This
153 makes the dump more standards compatible, but depending on the
154 history of the objects in the dump, may not restore properly.
155 The following command-line options control the database connection
157 parameters.
158 -h host
160 Specifies the host name of the machine on which the database
161 server is running. If the value begins with a slash, it is used
162 as the directory for the Unix domain socket. The default is
163 taken from the PGHOST environment variable, if set, else a Unix
164 domain socket connection is attempted.
165 -p port
167 Specifies the TCP port or local Unix domain socket file exten‐
168 sion on which the server is listening for connections. Defaults
169 to the PGPORT environment variable, if set, or a compiled-in
170 default.
171 -U username
173 Connect as the given user.
174 -W Force a password prompt. This should happen automatically if the
176 server requires password authentication.
177
179 PGHOST
180 PGPORT
182 PGUSER Default connection parameters
184 This utility, like most other PostgreSQL utilities, also uses the envi‐
186 ronment variables supported by libpq (see in the documentation).
187
189 Since pg_dumpall calls pg_dump internally, some diagnostic messages
190 will refer to pg_dump.
191 Once restored, it is wise to run ANALYZE on each database so the opti‐
193 mizer has useful statistics. You can also run vacuumdb -a -z to analyze
194 all databases.
195 pg_dumpall requires all needed tablespace directories to exist before
197 the restore or database creation will fail for databases in non-default
198 locations.
199
201 To dump all databases:
202 $ pg_dumpall > db.out
204 To reload this database use, for example:
207 $ psql -f db.out postgres
209 (It is not important to which database you connect here since the
211 script file created by pg_dumpall will contain the appropriate commands
212 to create and connect to the saved databases.)
213
215 Check pg_dump(1) for details on possible error conditions.
216Application 2008-06-08 PG_DUMPALL(1)