1PG_RESTORE(1) PostgreSQL Client Applications PG_RESTORE(1)
2
6 pg_restore - restore a PostgreSQL database from an archive file created
7 by pg_dump
8
11 pg_restore [ option... ] [ filename ]
12
14 pg_restore is a utility for restoring a PostgreSQL database from an ar‐
15 chive created by pg_dump(1) in one of the non-plain-text formats. It
16 will issue the commands necessary to reconstruct the database to the
17 state it was in at the time it was saved. The archive files also allow
18 pg_restore to be selective about what is restored, or even to reorder
19 the items prior to being restored. The archive files are designed to be
20 portable across architectures.
21 pg_restore can operate in two modes. If a database name is specified,
23 the archive is restored directly into the database. Otherwise, a script
24 containing the SQL commands necessary to rebuild the database is cre‐
25 ated and written to a file or standard output. The script output is
26 equivalent to the plain text output format of pg_dump. Some of the
27 options controlling the output are therefore analogous to pg_dump
28 options.
29 Obviously, pg_restore cannot restore information that is not present in
31 the archive file. For instance, if the archive was made using the
32 ``dump data as INSERT commands'' option, pg_restore will not be able to
33 load the data using COPY statements.
34
36 pg_restore accepts the following command line arguments.
37 filename
39 Specifies the location of the archive file to be restored. If
40 not specified, the standard input is used.
41 -a
43 --data-only
45 Restore only the data, not the schema (data definitions).
46 -c
48 --clean
50 Clean (drop) database objects before recreating them.
51 -C
53 --create
55 Create the database before restoring into it. (When this option
56 is used, the database named with -d is used only to issue the
57 initial CREATE DATABASE command. All data is restored into the
58 database name that appears in the archive.)
59 -d dbname
61 --dbname=dbname
63 Connect to database dbname and restore directly into the data‐
64 base.
65 -e
67 --exit-on-error
69 Exit if an error is encountered while sending SQL commands to
70 the database. The default is to continue and to display a count
71 of errors at the end of the restoration.
72 -f filename
74 --file=filename
76 Specify output file for generated script, or for the listing
77 when used with -l. Default is the standard output.
78 -F format
80 --format=format
82 Specify format of the archive. It is not necessary to specify
83 the format, since pg_restore will determine the format automati‐
84 cally. If specified, it can be one of the following:
85 t
87 tar The archive is a tar archive. Using this archive format
89 allows reordering and/or exclusion of schema elements at
90 the time the database is restored. It is also possible to
91 limit which data is reloaded at restore time.
92 c
94 custom The archive is in the custom format of pg_dump. This is
96 the most flexible format in that it allows reordering of
97 data load as well as schema elements. This format is also
98 compressed by default.
99 -i
101 --ignore-version
103 Ignore database version checks.
104 -I index
106 --index=index
108 Restore definition of named index only.
109 -l
111 --list List the contents of the archive. The output of this operation
113 can be used with the -L option to restrict and reorder the items
114 that are restored.
115 -L list-file
117 --use-list=list-file
119 Restore elements in list-file only, and in the order they
120 appear in the file. Lines can be moved and may also be commented
121 out by placing a ; at the start of the line. (See below for
122 examples.)
123 -n namespace
125 --schema=schema
127 Restore only objects that are in the named schema. This can be
128 combined with the -t option to restore just a specific table.
129 -O
131 --no-owner
133 Do not output commands to set ownership of objects to match the
134 original database. By default, pg_restore issues ALTER OWNER or
135 SET SESSION AUTHORIZATION statements to set ownership of created
136 schema elements. These statements will fail unless the initial
137 connection to the database is made by a superuser (or the same
138 user that owns all of the objects in the script). With -O, any
139 user name can be used for the initial connection, and this user
140 will own all the created objects.
141 -P function-name(argtype [, ...])
143 --function=function-name(argtype [, ...])
145 Restore the named function only. Be careful to spell the func‐
146 tion name and arguments exactly as they appear in the dump
147 file's table of contents.
148 -R
150 --no-reconnect
152 This option is obsolete but still accepted for backwards compat‐
153 ibility.
154 -s
156 --schema-only
158 Restore only the schema (data definitions), not the data (table
159 contents). Sequence current values will not be restored, either.
160 (Do not confuse this with the --schema option, which uses the
161 word ``schema'' in a different meaning.)
162 -S username
164 --superuser=username
166 Specify the superuser user name to use when disabling triggers.
167 This is only relevant if --disable-triggers is used.
168 -t table
170 --table=table
172 Restore definition and/or data of named table only.
173 -T trigger
175 --trigger=trigger
177 Restore named trigger only.
178 -v
180 --verbose
182 Specifies verbose mode.
183 -x
185 --no-privileges
187 --no-acl
189 Prevent restoration of access privileges (grant/revoke com‐
190 mands).
191 --disable-triggers
193 This option is only relevant when performing a data-only
194 restore. It instructs pg_restore to execute commands to tempo‐
195 rarily disable triggers on the target tables while the data is
196 reloaded. Use this if you have referential integrity checks or
197 other triggers on the tables that you do not want to invoke dur‐
198 ing data reload.
199 Presently, the commands emitted for --disable-triggers must be
201 done as superuser. So, you should also specify a superuser name
202 with -S, or preferably run pg_restore as a PostgreSQL superuser.
203 --use-set-session-authorization
205 Output SQL-standard SET SESSION AUTHORIZATION commands instead
206 of ALTER OWNER commands to determine object ownership. This
207 makes the dump more standards compatible, but depending on the
208 history of the objects in the dump, may not restore properly.
209 --no-data-for-failed-tables
211 By default, table data is restored even if the creation command
212 for the table failed (e.g., because it already exists). With
213 this option, data for such a table is skipped. This behavior is
214 useful when the target database may already contain the desired
215 table contents. For example, auxiliary tables for PostgreSQL
216 extensions such as PostGIS may already be loaded in the target
217 database; specifying this option prevents duplicate or obsolete
218 data from being loaded into them.
219 This option is effective only when restoring directly into a
221 database, not when producing SQL script output.
222 pg_restore also accepts the following command line arguments for con‐
224 nection parameters:
225 -h host
227 --host=host
229 Specifies the host name of the machine on which the server is
230 running. If the value begins with a slash, it is used as the
231 directory for the Unix domain socket. The default is taken from
232 the PGHOST environment variable, if set, else a Unix domain
233 socket connection is attempted.
234 -p port
236 --port=port
238 Specifies the TCP port or local Unix domain socket file exten‐
239 sion on which the server is listening for connections. Defaults
240 to the PGPORT environment variable, if set, or a compiled-in
241 default.
242 -U username
244 Connect as the given user
245 -W Force a password prompt. This should happen automatically if the
247 server requires password authentication.
248 -1
250 --single-transaction
252 Execute the restore as a single transaction (that is, wrap the
253 emitted commands in BEGIN/COMMIT). This ensures that either all
254 the commands complete successfully, or no changes are applied.
255 This option implies --exit-on-error.
256
258 PGHOST
259 PGPORT
261 PGUSER Default connection parameters
263 This utility, like most other PostgreSQL utilities, also uses the envi‐
265 ronment variables supported by libpq (see in the documentation).
266
268 When a direct database connection is specified using the -d option,
269 pg_restore internally executes SQL statements. If you have problems
270 running pg_restore, make sure you are able to select information from
271 the database using, for example, psql(1). Also, any default connection
272 settings and environment variables used by the libpq front-end library
273 will apply.
274
276 If your installation has any local additions to the template1 database,
277 be careful to load the output of pg_restore into a truly empty data‐
278 base; otherwise you are likely to get errors due to duplicate defini‐
279 tions of the added objects. To make an empty database without any local
280 additions, copy from template0 not template1, for example:
281 CREATE DATABASE foo WITH TEMPLATE template0;
283 The limitations of pg_restore are detailed below.
286 · When restoring data to a pre-existing table and the option --disable-
288 triggers is used, pg_restore emits commands to disable triggers on
289 user tables before inserting the data then emits commands to re-
290 enable them after the data has been inserted. If the restore is
291 stopped in the middle, the system catalogs may be left in the wrong
292 state.
293 · pg_restore will not restore large objects for a single table. If an
295 archive contains large objects, then all large objects will be
296 restored.
297 See also the pg_dump(1) documentation for details on limitations of
299 pg_dump.
300 Once restored, it is wise to run ANALYZE on each restored table so the
302 optimizer has useful statistics.
303
305 Assume we have dumped a database called mydb into a custom-format dump
306 file:
307 $ pg_dump -Fc mydb > db.dump
309 To drop the database and recreate it from the dump:
312 $ dropdb mydb
314 $ pg_restore -C -d postgres db.dump
315 The database named in the -d switch can be any database existing in the
317 cluster; pg_restore only uses it to issue the CREATE DATABASE command
318 for mydb. With -C, data is always restored into the database name that
319 appears in the dump file.
320 To reload the dump into a new database called newdb:
322 $ createdb -T template0 newdb
324 $ pg_restore -d newdb db.dump
325 Notice we don't use -C, and instead connect directly to the database to
327 be restored into. Also note that we clone the new database from tem‐
328 plate0 not template1, to ensure it is initially empty.
329 To reorder database items, it is first necessary to dump the table of
331 contents of the archive:
332 $ pg_restore -l db.dump > db.list
334 The listing file consists of a header and one line for each item, e.g.,
336 ;
338 ; Archive created at Fri Jul 28 22:28:36 2000
339 ; dbname: mydb
340 ; TOC Entries: 74
341 ; Compression: 0
342 ; Dump Version: 1.4-0
343 ; Format: CUSTOM
344 ;
345 ;
346 ; Selected TOC Entries:
347 ;
348 2; 145344 TABLE species postgres
349 3; 145344 ACL species
350 4; 145359 TABLE nt_header postgres
351 5; 145359 ACL nt_header
352 6; 145402 TABLE species_records postgres
353 7; 145402 ACL species_records
354 8; 145416 TABLE ss_old postgres
355 9; 145416 ACL ss_old
356 10; 145433 TABLE map_resolutions postgres
357 11; 145433 ACL map_resolutions
358 12; 145443 TABLE hs_old postgres
359 13; 145443 ACL hs_old
360 Semicolons start a comment, and the numbers at the start of lines refer
362 to the internal archive ID assigned to each item.
363 Lines in the file can be commented out, deleted, and reordered. For
365 example,
366 10; 145433 TABLE map_resolutions postgres
368 ;2; 145344 TABLE species postgres
369 ;4; 145359 TABLE nt_header postgres
370 6; 145402 TABLE species_records postgres
371 ;8; 145416 TABLE ss_old postgres
372 could be used as input to pg_restore and would only restore items 10
374 and 6, in that order:
375 $ pg_restore -L db.list db.dump
377
380 The pg_restore utility first appeared in PostgreSQL 7.1.
381
383 pg_dump(1), pg_dumpall(1), psql(1)
384Application 2008-06-08 PG_RESTORE(1)