| blob | 205063aa50a322efc55265cf2a1e2fe724f7e3b9 |
1 .\\" auto-generated by docbook2man-spec $Revision: 1.1 $
2 .TH "PG_RESTORE" "1" "2003-11-02" "Application" "PostgreSQL Client Applications"
3 .SH NAME
4 pg_restore \- restore a PostgreSQL database from an archive file created by pg_dump
6 .SH SYNOPSIS
7 .sp
8 \fBpg_restore\fR\fR [ \fR\fB\fIoption\fB\fR...\fB \fR\fR]\fR\fR [ \fR\fB\fIfilename\fB \fR\fR]\fR
9 .SH "DESCRIPTION"
10 .PP
11 \fBpg_restore\fR is a utility for restoring a
12 PostgreSQL database from an archive
13 created by \fBpg_dump\fR(1) in one of the non-plain-text
14 formats. It will issue the commands necessary to reconstruct the
15 database to the state it was in at the time it was saved. The
16 archive files also allow \fBpg_restore\fR to
17 be selective about what is restored, or even to reorder the items
18 prior to being restored. The archive files are designed to be
19 portable across architectures.
20 .PP
21 \fBpg_restore\fR can operate in two modes: If
22 a database name is specified, the archive is restored directly into
23 the database. (Large objects can only be restored by using such a direct
24 database connection.) Otherwise, a script containing the SQL
25 commands necessary to rebuild the database is created (and written
26 to a file or standard output), similar to the ones created by the
27 \fBpg_dump\fR plain text format. Some of the
28 options controlling the script output are therefore analogous to
29 \fBpg_dump\fR options.
30 .PP
31 Obviously, \fBpg_restore\fR cannot restore information
32 that is not present in the archive file. For instance, if the
33 archive was made using the ``dump data as
34 \fBINSERT\fR commands'' option,
35 \fBpg_restore\fR will not be able to load the data
36 using \fBCOPY\fR statements.
37 .SH "OPTIONS"
38 .PP
39 \fBpg_restore\fR accepts the following command
40 line arguments.
41 .TP
42 \fB\fIfilename\fB\fR
43 Specifies the location of the archive file to be restored.
44 If not specified, the standard input is used.
45 .TP
46 \fB-a\fR
47 .TP
48 \fB--data-only\fR
49 Restore only the data, not the schema (data definitions).
50 .TP
51 \fB-c\fR
52 .TP
53 \fB--clean\fR
54 Clean (drop) database objects before recreating them.
55 .TP
56 \fB-C\fR
57 .TP
58 \fB--create\fR
59 Create the database before restoring into it. (When this
60 option is used, the database named with \fB-d\fR is
61 used only to issue the initial CREATE DATABASE
62 command. All data is restored into the database name that
63 appears in the archive.)
64 .TP
65 \fB-d \fIdbname\fB\fR
66 .TP
67 \fB--dbname=\fIdbname\fB\fR
68 Connect to database \fIdbname\fR and restore directly
69 into the database.
70 .TP
71 \fB-f \fIfilename\fB\fR
72 .TP
73 \fB--file=\fIfilename\fB\fR
74 Specify output file for generated script, or for the listing
75 when used with \fB-l\fR. Default is the standard
76 output.
77 .TP
78 \fB-F \fIformat\fB\fR
79 .TP
80 \fB--format=\fIformat\fB\fR
81 Specify format of the archive. It is not necessary to specify
82 the format, since \fBpg_restore\fR will
83 determine the format automatically. If specified, it can be
84 one of the following:
85 .RS
86 .TP
87 \fBt\fR
88 The archive is a \fBtar\fR archive. Using this
89 archive format allows reordering and/or exclusion of schema
90 elements at the time the database is restored. It is also
91 possible to limit which data is reloaded at restore time.
92 .TP
93 \fBc\fR
94 The archive is in the custom format of
95 \fBpg_dump\fR. This is the most
96 flexible format in that it allows reordering of data load
97 as well as schema elements. This format is also compressed
98 by default.
99 .RE
100 .PP
101 .TP
102 \fB-i\fR
103 .TP
104 \fB--ignore-version\fR
105 Ignore database version checks.
106 .TP
107 \fB-I \fIindex\fB\fR
108 .TP
109 \fB--index=\fIindex\fB\fR
110 Restore definition of named index only.
111 .TP
112 \fB-l\fR
113 .TP
114 \fB--list\fR
115 List the contents of the archive. The output of this operation
116 can be used with the \fB-L\fR option to restrict
117 and reorder the items that are restored.
118 .TP
119 \fB-L \fIlist-file\fB\fR
120 .TP
121 \fB--use-list=\fIlist-file\fB\fR
122 Restore elements in \fIlist-file\fR only, and in the
123 order they appear in the file. Lines can be moved and may also
124 be commented out by placing a ; at the
125 start of the line. (See below for examples.)
126 .TP
127 \fB-N\fR
128 .TP
129 \fB--orig-order\fR
130 Restore items in the order they were originally generated within
131 \fBpg_dump\fR. This option has no known
132 practical use, since \fBpg_dump\fR generates
133 the items in an order convenient to it, which is unlikely to be a
134 safe order for restoring them. (This is \fBnot\fR the order
135 in which the items are ultimately listed in the archive's table of
136 contents.) See also \fB-r\fR.
137 .TP
138 \fB-o\fR
139 .TP
140 \fB--oid-order\fR
141 Restore items in order by OID. This option is of limited usefulness,
142 since OID is only an approximate indication of original creation
143 order. This option overrides \fB-N\fR if both are specified.
144 See also \fB-r\fR.
145 .TP
146 \fB-O\fR
147 .TP
148 \fB--no-owner\fR
149 Do not output commands to set
150 ownership of objects to match the original database.
151 By default, \fBpg_restore\fR issues
152 \fBSET SESSION AUTHORIZATION\fR
153 statements to set ownership of created schema elements.
154 These statements will fail unless the initial connection to the
155 database is made by a superuser
156 (or the same user that owns all of the objects in the script).
157 With \fB-O\fR, any user name can be used for the
158 initial connection, and this user will own all the created objects.
159 .TP
160 \fB-P \fIfunction-name(argtype [, ...])\fB\fR
161 .TP
162 \fB--function=\fIfunction-name(argtype [, ...])\fB\fR
163 Restore the named function only. Be careful to spell the function
164 name and arguments exactly as they appear in the dump file's table
165 of contents.
166 .TP
167 \fB-r\fR
168 .TP
169 \fB--rearrange\fR
170 Rearrange items by object type (this occurs after the sorting
171 specified by \fB-N\fR or \fB-o\fR, if
172 given). The rearrangement is intended to give the best possible
173 restore performance.
175 When none of \fB-N\fR, \fB-o\fR, and
176 \fB-r\fR appear, \fBpg_restore\fR restores
177 items in the order they appear in the dump's table of contents,
178 or in the order they appear in the \fIlist-file\fR if \fB-L\fR is
179 given. The combination of \fB-o\fR and \fB-r\fR
180 duplicates the sorting done by \fBpg_dump\fR
181 before creating the dump's table of contents,
182 and so it is normally unnecessary to specify it.
183 .TP
184 \fB-R\fR
185 .TP
186 \fB--no-reconnect\fR
187 This option is obsolete but still accepted for backwards
188 compatibility.
189 .TP
190 \fB-s\fR
191 .TP
192 \fB--schema-only\fR
193 Restore only the schema (data definitions), not the data.
194 Sequence values will be reset.
195 .TP
196 \fB-S \fIusername\fB\fR
197 .TP
198 \fB--superuser=\fIusername\fB\fR
199 Specify the superuser user name to use when disabling triggers.
200 This is only relevant if \fB--disable-triggers\fR is used.
201 .TP
202 \fB-t \fItable\fB\fR
203 .TP
204 \fB--table=\fItable\fB\fR
205 Restore definition and/or data of named table only.
206 .TP
207 \fB-T \fItrigger\fB\fR
208 .TP
209 \fB--trigger=\fItrigger\fB\fR
210 Restore named trigger only.
211 .TP
212 \fB-v\fR
213 .TP
214 \fB--verbose\fR
215 Specifies verbose mode.
216 .TP
217 \fB-x\fR
218 .TP
219 \fB--no-privileges\fR
220 .TP
221 \fB--no-acl\fR
222 Prevent restoration of access privileges (grant/revoke commands).
223 .TP
224 \fB-X use-set-session-authorization\fR
225 .TP
226 \fB--use-set-session-authorization\fR
227 This option is obsolete but still accepted for backwards
228 compatibility.
229 \fBpg_restore\fR now always behaves in the
230 way formerly selected by this option.
231 .TP
232 \fB-X disable-triggers\fR
233 .TP
234 \fB--disable-triggers\fR
235 This option is only relevant when performing a data-only restore.
236 It instructs \fBpg_restore\fR to execute commands
237 to temporarily disable triggers on the target tables while
238 the data is reloaded. Use this if you have referential
239 integrity checks or other triggers on the tables that you
240 do not want to invoke during data reload.
242 Presently, the commands emitted for
243 \fB--disable-triggers\fR must be done as superuser. So, you
244 should also specify a superuser name with \fB-S\fR, or
245 preferably run \fBpg_restore\fR as a
246 PostgreSQL superuser.
247 .PP
248 .PP
249 \fBpg_restore\fR also accepts
250 the following command line arguments for connection parameters:
251 .TP
252 \fB-h \fIhost\fB\fR
253 .TP
254 \fB--host=\fIhost\fB\fR
255 Specifies the host name of the machine on which the server is
256 running. If the value begins with a slash, it is used as the
257 directory for the Unix domain socket. The default is taken
258 from the \fBPGHOST\fR environment variable, if set,
259 else a Unix domain socket connection is attempted.
260 .TP
261 \fB-p \fIport\fB\fR
262 .TP
263 \fB--port=\fIport\fB\fR
264 Specifies the TCP port or local Unix domain socket file
265 extension on which the server is listening for connections.
266 Defaults to the \fBPGPORT\fR environment variable, if
267 set, or a compiled-in default.
268 .TP
269 \fB-U \fIusername\fB\fR
270 Connect as the given user
271 .TP
272 \fB-W\fR
273 Force a password prompt. This should happen automatically if
274 the server requires password authentication.
275 .PP
276 .SH "ENVIRONMENT"
277 .TP
278 \fBPGHOST\fR
279 .TP
280 \fBPGPORT\fR
281 .TP
282 \fBPGUSER\fR
283 Default connection parameters
284 .SH "DIAGNOSTICS"
285 .PP
286 When a direct database connection is specified using the
287 \fB-d\fR option, \fBpg_restore\fR
288 internally executes SQL statements. If you have
289 problems running \fBpg_restore\fR, make sure
290 you are able to select information from the database using, for
291 example, \fBpsql\fR.
292 .SH "NOTES"
293 .PP
294 If your installation has any local additions to the
295 template1 database, be careful to load the output of
296 \fBpg_restore\fR into a truly empty database;
297 otherwise you are likely to get errors due to duplicate definitions
298 of the added objects. To make an empty database without any local
299 additions, copy from template0 not template1, for example:
300 .sp
301 .nf
302 CREATE DATABASE foo WITH TEMPLATE template0;
303 .sp
304 .fi
305 .PP
306 The limitations of \fBpg_restore\fR are detailed below.
307 .TP 0.2i
308 \(bu
309 When restoring data to a pre-existing table and the option
310 \fB--disable-triggers\fR is used,
311 \fBpg_restore\fR emits commands
312 to disable triggers on user tables before inserting the data then emits commands to
313 re-enable them after the data has been inserted. If the restore is stopped in the
314 middle, the system catalogs may be left in the wrong state.
315 .TP 0.2i
316 \(bu
317 \fBpg_restore\fR will not restore large objects for a single table. If
318 an archive contains large objects, then all large objects will be restored.
319 .PP
320 .PP
321 See also the \fBpg_dump\fR(1) documentation for details on
322 limitations of \fBpg_dump\fR.
323 .PP
324 Once restored, it is wise to run \fBANALYZE\fR on each
325 restored table so the optimizer has useful statistics.
326 .SH "EXAMPLES"
327 .PP
328 To dump a database called mydb that contains
329 large objects to a \fItar\fR file:
330 .sp
331 .nf
332 $ \fBpg_dump -Ft -b mydb > db.tar\fR
333 .sp
334 .fi
335 .PP
336 To reload this database (with large objects) to an
337 existing database called newdb:
338 .sp
339 .nf
340 $ \fBpg_restore -d newdb db.tar\fR
341 .sp
342 .fi
343 .PP
344 To reorder database items, it is first necessary to dump the table of
345 contents of the archive:
346 .sp
347 .nf
348 $ \fBpg_restore -l archive.file > archive.list\fR
349 .sp
350 .fi
351 The listing file consists of a header and one line for each item, e.g.,
352 .sp
353 .nf
354 ;
355 ; Archive created at Fri Jul 28 22:28:36 2000
356 ; dbname: birds
357 ; TOC Entries: 74
358 ; Compression: 0
359 ; Dump Version: 1.4-0
360 ; Format: CUSTOM
361 ;
362 ;
363 ; Selected TOC Entries:
364 ;
365 2; 145344 TABLE species postgres
366 3; 145344 ACL species
367 4; 145359 TABLE nt_header postgres
368 5; 145359 ACL nt_header
369 6; 145402 TABLE species_records postgres
370 7; 145402 ACL species_records
371 8; 145416 TABLE ss_old postgres
372 9; 145416 ACL ss_old
373 10; 145433 TABLE map_resolutions postgres
374 11; 145433 ACL map_resolutions
375 12; 145443 TABLE hs_old postgres
376 13; 145443 ACL hs_old
377 .sp
378 .fi
379 Semicolons start a comment, and the numbers at the start of lines refer to the
380 internal archive ID assigned to each item.
381 .PP
382 Lines in the file can be commented out, deleted, and reordered. For example,
383 .sp
384 .nf
385 10; 145433 TABLE map_resolutions postgres
386 ;2; 145344 TABLE species postgres
387 ;4; 145359 TABLE nt_header postgres
388 6; 145402 TABLE species_records postgres
389 ;8; 145416 TABLE ss_old postgres
390 .sp
391 .fi
392 could be used as input to \fBpg_restore\fR and would only restore
393 items 10 and 6, in that order:
394 .sp
395 .nf
396 $ \fBpg_restore -L archive.list archive.file\fR
397 .sp
398 .fi
399 .SH "HISTORY"
400 .PP
401 The \fBpg_restore\fR utility first appeared in
402 PostgreSQL 7.1.
403 .SH "SEE ALSO"
404 \fBpg_dump\fR(1), \fBpg_dumpall\fR(1), \fBpsql\fR(1)