1PG_DUMPALL(1) PostgreSQL Client Applications PG_DUMPALL(1)
2
3
4
6 pg_dumpall - extract a PostgreSQL database cluster into a script file
7
8
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
22 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
28 The SQL script will be written to the standard output. Shell operators
29 should be used to redirect it into a file.
30
31 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
40 -a
41
42 --data-only
43 Dump only the data, not the schema (data definitions).
44
45 -c
46
47 --clean
48 Include SQL commands to clean (drop) databases before recreating
49 them. DROP commands for roles and tablespaces are added as well.
50
51 -d
52
53 --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
60 -D
61
62 --column-inserts
63
64 --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
70 -g
71
72 --globals-only
73 Dump only global objects (roles and tablespaces), no databases.
74
75 -i
76
77 --ignore-version
78 Ignore version mismatch between pg_dumpall and the database
79 server.
80
81 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
87 -o
88
89 --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
94 -O
95
96 --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
106 -s
107
108 --schema-only
109 Dump only the object definitions (schema), not data.
110
111 -S username
112
113 --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
119 -v
120
121 --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
126 -x
127
128 --no-privileges
129
130 --no-acl
131 Prevent dumping of access privileges (grant/revoke commands).
132
133 --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
138 --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
145 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
150 --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
156 The following command-line options control the database connection
157 parameters.
158
159 -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
166 -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
172 -U username
173 Connect as the given user.
174
175 -W Force a password prompt. This should happen automatically if the
176 server requires password authentication.
177
179 PGHOST
180
181 PGPORT
182
183 PGUSER Default connection parameters
184
185 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
192 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
196 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
203 $ pg_dumpall > db.out
204
205
206 To reload this database use, for example:
207
208 $ psql -f db.out postgres
209
210 (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.
216
217
218
219Application 2008-06-08 PG_DUMPALL(1)