Penguin
Recent Changes
Blame: pg_restore(1)
EditPageHistoryDiffInfoLikePages
Annotated edit history of pg_restore(1) version 4, including all changes. View license author blame.
Rev Author # Line
1 perry 1 PG_RESTORE
2 !!!PG_RESTORE
3 NAME
4 SYNOPSIS
5 DESCRIPTION
6 DIAGNOSTICS
7 NOTES
8 EXAMPLES
9 HISTORY
10 SEE ALSO
11 ----
12 !!NAME
13
14
15 pg_restore - restore a PostgreSQL database from an archive file created by pg_dump
16 !!SYNOPSIS
17
18
19 __pg_restore__ [[ __-a__ ] [[ __-c__ ] [[ __-C__ ]
20 [[ __-d__ ''dbname'' ] [[ __-f__ ''output-file'' ]
21 [[ __-F__ ''format'' ] [[ __-i__ ''index'' ] [[
22 __-l__ ] [[ __-L__ ''contents-file'' ] [[ __-N__ |
23 __-o__ | __-r__ ] [[ __-O__ ] [[ __-P__
24 ''function-name'' ] [[ __-R__ ] [[ __-s__ ] [[
25 __-S__ ] [[ __-t__ ''table'' ] [[ __-T__
26 ''trigger'' ] [[ __-v__ ] [[ __-x__ ] [[ __-X__
27 ''keyword'' ] [[ __-h__ ''host'' ] [[ __-p__
28 ''port'' ] [[ __-U__ ''username'' ] [[ __-W__ ] [[
29 ''archive-file'' ]
30 !!DESCRIPTION
31
32
33 __pg_restore__ is a utility for restoring a PostgreSQL
4 perry 34 database from an archive created by pg_dump(1) in one
1 perry 35 of the non-plain-text formats. It will issue the commands
36 necessary to re-generate all user-defined types, functions,
37 tables, indexes, aggregates, and operators, as well as the
38 data in the tables.
39
40
41 The archive files contain information for __pg_restore__
42 to rebuild the database, but also allow __pg_restore__ to
43 be selective about what is restored, or even to reorder the
44 items prior to being restored. The archive files are
45 designed to be portable across architectures.
46
47
48 __pg_restore__ can operate in two modes: If a database
49 name is specified, the archive is restored directly into the
50 database. Otherwise, a script containing the SQL commands
51 necessary to rebuild the database is created (and written to
52 a file or standard output), similar to the ones created by
53 the __pg_dump__ plain text format. Some of the options
54 controlling the script output are therefore analogous to
55 __pg_dump__ options.
56
57
58 Obviously, __pg_restore__ cannot restore information that
59 is not present in the archive file; for instance, if the
60 archive was made using the ``dump data as __INSERT__s''
61 option, __pg_restore__ will not be able to load the data
62 using __COPY__ statements.
63
64
65 __OPTIONS__
66
67
68 __pg_restore__ accepts the following command line
69 arguments. (Long option forms are only available on some
70 platforms.)
71
72
73 ''archive-name''
74
75
76 Specifies the location of the archive file to be restored.
77 If not specified, the standard input is used.
78
79
80 __-a__
81
82
83 __--data-only__
84
85
86 Restore only the data, no schema (definitions).
87
88
89 __-c__
90
91
92 __--clean__
93
94
95 Clean (drop) database objects before recreating
96 them.
97
98
99 __-C__
100
101
102 __--create__
103
104
105 Create the database before restoring into it. (When this
106 switch appears, the database named with __-d__ is used
107 only to issue the initial CREATE DATABASE command. All data
108 is restored into the database name that appears in the
109 archive.)
110
111
112 __-d__ ''dbname''
113
114
115 __--dbname=__''dbname''
116
117
118 Connect to database ''dbname'' and restore directly into
119 the database. Large objects can only be restored by using a
120 direct database connection.
121
122
123 __-f__ ''filename''
124
125
126 __--file=__''filename''
127
128
129 Specify output file for generated script, or for the listing
130 when used with __-l__. Default is the standard
131 output.
132
133
134 __-F__ ''format''
135
136
137 __--format=__''format''
138
139
140 Specify format of the archive. It is not necessary to
141 specify the format, since __pg_restore__ will determine
142 the format automatically. If specified, it can be one of the
143 following:
144
145
146 __t__
147
148
149 Archive is a ''tar'' archive. Using this archive format
150 allows reordering and/or exclusion of schema elements at the
151 time the database is restored. It is also possible to limit
152 which data is reloaded at restore time.
153
154
155 __c__
156
157
158 Archive is in the custom format of __pg_dump__. This is
159 the most flexible format in that it allows reordering of
160 data load as well as schema elements. This format is also
161 compressed by default.
162
163
164 __-i__ ''index''
165
166
167 __--index=__''index''
168
169
170 Restore definition for named ''index'' only.
171
172
173 __-l__
174
175
176 __--list__
177
178
179 List the contents of the archive. The output of this command
180 can be used with the __-L__ option to restrict and
181 reorder the items that are restored.
182
183
184 __-L__ ''list-file''
185
186
187 __--use-list=__''list-file''
188
189
190 Restore elements in ''list-file'' only, and in the order
191 they appear in the file. Lines can be moved and may also be
192 commented out by placing a ; at the start of the
193 line.
194
195
196 __-N__
197
198
199 __--orig-order__
200
201
202 Restore items in the original dump order. By default
203 __pg_dump__ will dump items in an order convenient to
204 __pg_dump__, then save the archive in a modified OID
205 order. This option overrides the OID ordering.
206
207
208 __-o__
209
210
211 __--oid-order__
212
213
214 Restore items in the OID order. By default __pg_dump__
215 will dump items in an order convenient to __pg_dump__,
216 then save the archive in a modified OID order. This option
217 enforces strict OID ordering.
218
219
220 __-O__
221
222
223 __--no-owner__
224
225
226 Prevent any attempt to restore original object ownership.
227 Objects will be owned by the user name used to attach to the
228 database.
229
230
231 __-P__ ''function-name''
232
233
234 __--function=__''function-name''
235
236
237 Specify a procedure or function to be restored.
238
239
240 __-r__
241
242
243 __--rearrange__
244
245
246 Restore items in modified OID order. By default
247 __pg_dump__ will dump items in an order convenient to
248 __pg_dump__, then save the archive in a modified OID
249 order. Most objects will be restored in OID order, but some
250 things (e.g., rules and indexes) will be restored at the end
251 of the process irrespective of their OIDs. This option is
252 the default.
253
254
255 __-R__
256
257
258 __--no-reconnect__
259
260
261 While restoring an archive, __pg_restore__ typically has
262 to reconnect to the database several times with different
263 user names to set the correct ownership of the created
264 objects. If this is undesirable (e.g., because manual
265 interaction (passwords) would be necessary for each
266 reconnection), this option prevents __pg_restore__ from
267 issuing any reconnection requests. (A connection request
268 while in plain text mode, not connected to a database, is
269 made by putting out a psql(1) __connect__
270 command.) However, this option is a rather blunt instrument
271 because it makes __pg_restore__ lose all object ownership
272 information, __unless__ you use the __-X
273 use-set-session-authorization__ option.
274
275
276 __-s__
277
278
279 __--schema-only__
280
281
282 Restore the schema (definitions), no data. Sequence values
283 will be reset.
284
285
286 __-S__ ''username''
287
288
289 __--superuser=__''username''
290
291
292 Specify the superuser user name to use when disabling
293 triggers and/or setting ownership of schema elements. By
294 default, __pg_restore__ will use the current user name if
295 it is a superuser.
296
297
298 __-t__ ''table''
299
300
301 __--table=__''table''
302
303
304 Restore schema/data for ''table'' only.
305
306
307 __-T__ ''trigger''
308
309
310 __--trigger=__''trigger''
311
312
313 Restore definition of ''trigger'' only.
314
315
316 __-v__
317
318
319 __--verbose__
320
321
322 Specifies verbose mode.
323
324
325 __-x__
326
327
328 __--no-privileges__
329
330
331 __--no-acl__
332
333
334 Prevent restoration of access privileges (grant/revoke
335 commands).
336
337
338 __-X use-set-session-authorization__
339
340
341 __--use-set-session-authorization__
342
343
344 Normally, if restoring an archive requires altering the
345 current database user (e.g., to set correct object
346 ownerships), a new connection to the database must be
347 opened, which might require manual interaction (e.g.,
348 passwords). If you use the __-X
349 use-set-session-authorization__ option, then
350 __pg_restore__ will instead use the
351 __set_session_authorization__(l) command. This has the
352 same effect, but it requires that the user restoring the
353 archive is a database superuser. This option effectively
354 overrides the __-R__ option.
355
356
357 __pg_restore__ also accepts the following command line
358 arguments for connection parameters:
359
360
361 __-h__ ''host''
362
363
364 __--host=__''host''
365
366
367 Specifies the host name of the machine on which the server
368 is running. If host begins with a slash, it is used as the
369 directory for the Unix domain socket.
370
371
372 __-p__ ''port''
373
374
375 __--port=__''port''
376
377
378 Specifies the Internet TCP/IP port or local Unix domain
379 socket file extension on which the server is listening for
380 connections. The port number defaults to 5432, or the value
381 of the __PGPORT__ environment variable (if
382 set).
383
384
385 __-U__ ''username''
386
387
388 Connect as the given user.
389
390
391 __-W__
392
393
394 Force a password prompt. This should happen automatically if
395 the server requires password authentication.
396 !!DIAGNOSTICS
397
398
399 Connection to database 'template1' failed.
400 connectDBStart() -- connect() failed: No such file or directory
401 Is the postmaster running locally
402 and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
403 __pg_restore__ could not attach to the __postmaster__ process on the specified host and port. If you see this message, ensure that the server is running on the proper host and that you have specified the proper port. If your site uses an authentication system, ensure that you have obtained the required authentication credentials.
404
405
406 __Note:__ When a direct database connection is specified
407 using the -d option, __pg_restore__ internally executes
408 __SQL__ statements. If you have problems running
409 __pg_restore__, make sure you are able to select
410 information from the database using, for example,
411 __psql__.
412 !!NOTES
413
414
415 If your installation has any local additions to the
416 template1 database, be careful to load the output of
417 __pg_restore__ into a truly empty database; otherwise you
418 are likely to get errors due to duplicate definitions of the
419 added objects. To make an empty database without any local
420 additions, copy from template0 not template1, for
421 example:
422
423
424 CREATE DATABASE foo WITH TEMPLATE = template0;
425 The limitations of __pg_restore__ are detailed below.
426
427
428 When restoring data to a pre-existing table,
429 __pg_restore__ emits queries to disable triggers on user
430 tables before inserting the data then emits queries to
431 re-enable them after the data has been inserted. If the
432 restore is stopped in the middle, the system catalogs may be
433 left in the wrong state.
434
435
436 __pg_restore__ will not restore large objects for a
437 single table. If an archive contains large objects, then all
438 large objects will be restored.
439
440
4 perry 441 See the pg_dump(1) documentation for details on
1 perry 442 limitations of __pg_dump__.
443 !!EXAMPLES
444
445
446 To dump a database:
447
448
449 $ __pg_dump mydb
450 __To reload this database:
451
452
453 $ __psql -d database -f db.out
454 __To dump a database called mydb that contains large objects to a ''tar'' file:
455
456
457 $ __pg_dump -Ft -b mydb
458 __To reload this database (with large objects) to an existing database called newdb:
459
460
461 $ __pg_restore -d newdb db.tar
462 __To reorder database items, it is first necessary to dump the table of contents of the archive:
463
464
465 $ __pg_restore -l archive.file
466 __The listing file consists of a header and one line for each item, e.g.,
467
468
469 ;
470 ; Archive created at Fri Jul 28 22:28:36 2000
471 ; dbname: birds
472 ; TOC Entries: 74
473 ; Compression: 0
474 ; Dump Version: 1.4-0
475 ; Format: CUSTOM
476 ;
477 ;
478 ; Selected TOC Entries:
479 ;
480 2; 145344 TABLE species postgres
481 3; 145344 ACL species
482 4; 145359 TABLE nt_header postgres
483 5; 145359 ACL nt_header
484 6; 145402 TABLE species_records postgres
485 7; 145402 ACL species_records
486 8; 145416 TABLE ss_old postgres
487 9; 145416 ACL ss_old
488 10; 145433 TABLE map_resolutions postgres
489 11; 145433 ACL map_resolutions
490 12; 145443 TABLE hs_old postgres
491 13; 145443 ACL hs_old
492 Semi-colons are comment delimiters, and the numbers at the start of lines refer to the internal archive ID assigned to each item.
493
494
495 Lines in the file can be commented out, deleted, and
496 reordered. For example,
497
498
499 10; 145433 TABLE map_resolutions postgres
500 ;2; 145344 TABLE species postgres
501 ;4; 145359 TABLE nt_header postgres
502 6; 145402 TABLE species_records postgres
503 ;8; 145416 TABLE ss_old postgres
504 could be used as input to __pg_restore__ and would only restore items 10 and 6, in that order.
505
506
507 $ __pg_restore -L archive.list archive.file
508 __
509 !!HISTORY
510
511
512 The __pg_restore__ utility first appeared in PostgreSQL
513 7.1.
514 !!SEE ALSO
515
516
4 perry 517 pg_dump(1), __pg_dumpall__(1), psql(1),
1 perry 518 ''PostgreSQL Administrator's Guide''
519 ----
This page is a man page (or other imported legacy content). We are unable to automatically determine the license status of this page.