| |
Data from a SQL dump can be restored to a database in one of two ways. If the dump created by
pg_dump
as a simple, plain-text file, it may be passed through to
psql
directly
as an input file. Alternatively, if another output format was chosen (e.g., the
tar
or
compressed tar
format), the
pg_restore
command must be used.
A database may either be restored from scratch, to an empty database, or to a non-existent database; how you go about
restoring a database depends largely on how it was dumped (e.g., if only data were dumped, or if the commands to create the
database were included).
A plain text output file from
pg_dump
may be passed through to
psql
as an
input file. This executes sequentially each of the dumped SQL instructions. Depending on how the dump was created,
and for what purpose, there are a variety of ways to invoke
psql
with the dump.
If your dump was created with the
-C
flag, the SQL statement to create the database is
included in the dump file itself. This means that, most likely, the database was either dropped, or has not
yet been created on the system on which it is being restored. If the database already exists, it may need to be dropped,
but only do this if you are sure the dump is up to date.
On the other hand, if the
-C
flag was
not
used, you may need to first
create the database before connecting to it and restoring its attributes and data. Remember also that you need to specify
the usual connection parameters to
psql
in order to operate as a user with the
rights to create a database.
Example 9-23 demonstrates recreating the
booktown database with the
booktown.sql
file created in Example 9-20, in the Section called Using pg_dump
" earlier in this chapter. Since the
-C
flag was used in this example, there is no need to create the database first; it can be
created by bootstrapping through the template1 database.
Example 9-23. Recreating the booktown database
[jworsley@booktown ~]$
psql -U manager -f booktown.sql template1
CREATE DATABASE
You are now connected to database booktown as user postgres.
COMMENT
CREATE
CREATE
CHANGE
[...]
As each dumped command in the
booktown.sql
file is processed by PostgreSQL, the resulting
server messages (e.g., CREATE, CHANGE) will be
displayed to
stderr
.
Note: Since
psql
can be used remotely, this same technique may be used across a network,
provided the correct connection parameters are used from an authorized host.
For files created by
pg_dump
with a file format other than plain text, the
pg_restore
command exists to seamlessly restore the dumped database from the tar,
or compressed tar file.
Here is the syntax for the
pg_restore
command:
pg_restore [
options
] [
file
]
In this syntax, if
file
is not specified,
pg_restore
will wait for data
from
stdin
. This means that you may effectively use the < shell
redirection character with the same results. Notice especially in the
options
the
-d
flag. If left unspecified,
pg_restore
will simply display the database
restoration statements to
stdout
(and thus, to the screen) rather than actually restoring a
database.
If you are using the
-C
flag to create a database from scratch, you must still supply the
-d
flag (with the name of an existing database to connect to first, e.g.,
template1) from which to initialize a connection and create the new database. In
such a case, it is not important to which database you initially connect, as it is only a temporary connection until the
new database is created.
Many of the options for
pg_restore
directly mirror those available in
pg_dump
. In some cases, the same option must be supplied in both
pg_dump
and
pg_restore
in order for the desired functionality to be achieved. For example, this is the case with
the
-C
flag. If used with
pg_dump
, but not
pg_restore
,
the CREATE DATABASE command will be ignored by
pg_restore
, even
though the command is present in the dump file.
The following are more detailed explanations of each option:
- -a, --data-only
-
Causes any reference to creation of database schema objects to be ignored, restoring only data records (those with
COPY or INSERT statements).
- -c, - -clean
-
Causes any DROP SQL statements to be executed before creating database objects.
Without the
-c
flag, these statements are ignored, even if they are present in the dump file.
- -C, - -create
-
Causes the CREATE DATABASE SQL statement (if found in the dump file) to be
executed. Without the
-C
flag, the statement is ignored.
- -d
NAME
, - -dbname=
NAME
-
Specifies the database called
NAME
to be connected to for the restoration. If the
-C
flag is used to create a new database, the
-d
flag should
be pointed to template1. If this parameter is not specified, the commands to restore
the database will instead be displayed to
stdout
rather than sent to PostgreSQL.
- -f
FILENAME
, - -file=
FILENAME
-
Indicates that
FILENAME
is the target for the database restoration SQL commands, rather than
a the
postmaster
backend (with the
-d
flag), or
stdout
(the default).
- -F { c | t }, - -format={ c | t }
-
Specifies the format of the input file,
FILENAME
. A value of
c
indicates that it is a compressed and tarred dump, while
t
indicates that it is only
tarred. Note that this option is usually not necessary, as
pg_restore
can judge
what kind of file it is dealing with through its header data.
- -h
HOSTNAME
, - -host=
HOSTNAME
-
Specifies that you should connect to
HOSTNAME
, rather than the localhost.
- -i, - -index
-
Specifies that
only
indices be recreated. Note that due to a bug, the
-i
flag may not work, though the
- -index
flag should (as of PostgreSQL 7.1.x).
- -l, - -list
-
Specifies that only the table of contents of database objects should be output in a comma-delimited, PostgreSQL
specific TOC (table of contents) format. This output can be redirected to a file (either with shell redirection
via the < character, or with the
-f
flag) and later used with the
-L
flag to control what database objects are restored.
- -L
FILENAME
, - -use-list=
FILENAME
-
Indicates use of the PostgreSQL TOC file
FILENAME
to determine which objects should be
restored by
pg_restore
. This file is generated using the
-l
flag. After generating the file, delete lines for objects you do now wish to restore, or preface those lines with a semicolon (;).
When
-L
is used,
pg_restore
only restores those objects listed
in the specified TOC file.
- -N, - -orig-order
-
Causes the restore to occur in the same order that the
pg_dump
originally dumped the objects
in (through the use of the extra TOC information in a
tar
or
gzip
-compressed format). This is not the same as the literal order in which the statements are
placed in the dump file itself, which is the default restore order. This option excludes the use of the
-o
or
-r
options.
If, during a restoration, database objects are created in an incorrect order (e.g., an object which relies on
another existing object is created before the object it relies on), you can re-initialize a database and try this
flag to override the order which was originally chosen by
pg_dump
.
- -o, - -oid-order
-
Causes the restore to occur strictly in the order of OIDs, ascending; this option excludes the use of the
-N
or
-r
options.
- -O, - -no-owner
-
Forces
pg_restore
to ignore any \connect statements which
would be used to enforce ownership.
- -p
PORT
, - -port=
PORT
-
Specifies that the database connection should be made on port
PORT
, rather than the
default port (usually 5432, though it may have been configured differently when PostgreSQL was
compiled, by the
- -with-pgport
flag).
- -P, - -function
-
Specifies that
only
functions are to be recreated. Like the
-i
flag, due to a bug, the
-P
flag may not work, though the
- -function
flag should,
as of PostgreSQL 7.1.x.
- -r, - -rearrange
-
Causes the restore to occur in the order chosen by
pg_dump
at the time of the dump file's
creation. Most of the objects are created in OID order with this option, though statements creating rules and
indices are placed towards the end of the file. This option is the default.
- -R, - -no-reconnect
-
Forces
pg_restore
to ignore
all
\connect
statements (not just those intended to enforce ownership). This cannot be used with the
-C
flag, which requires at least one reconnection after the creation of a new database.
- -s, - -schema-only
-
Causes only the creation of database schema objects, such as tables, views, sequences, and indices. No rows will
be copied or inserted into the tables, and sequences will initialize to their default values. This can be used, for example,
to create an empty production database that matches the structure of a development database.
- -S
NAME
, - -superuser=
NAME
-
Specifies the superuser with username
NAME
to be used in disabling triggers (if necessary, to
recreate a trigger), as well as to set ownership of schema elements.
- -t
NAME
, - -table[=
NAME
]
-
Causes only the table
NAME
to be restored, rather than all database objects.
Specifying just
- -table
causes only tables to be restored.
- -T
NAME
, - -trigger[=
NAME
]
-
Causes only the trigger
NAME
to be restored, rather than all database objects.
Specifying just
- -trigger
causes only triggers to be restored.
- -u, - -password
-
Causes
pg_restore
to provide a prompt for a username and password.
- -v, - -verbose
-
Causes verbose output of each action as it is performed. This output is sent to
stderr
,
rather than
stdout
.
- -x, - -no-acl
-
Suppresses any SQL GRANT or REVOKE statement
in the dump being restored.
Example 9-24 demonstrates a restoration of the booktown
database on a separate machine from which the original was made. It uses the
booktown.sql.tar
file
created in Example 9-21, in the Section called Using pg_dump
," earlier in this chapter, as the source
for the restoration.
Example 9-24. Restore with pg_restore
[jworsley@cmd ~]$
pg_restore -v -C -O -d template1 booktown.sql.tar
Connecting to database for restore
Creating DATABASE booktown
Connecting to new DB 'booktown' as postgres
Connecting to booktown as postgres
Creating COMMENT DATABASE "booktown"
Creating TABLE inventory
[...]
You can see upon examining the
pg_restore
command in Example 9-24 that it
uses the
-v
flag for verbose output as it operates, the
-C
flag to create the
database (as this is a new database on this machine), and the
-O
flag to ignore ownership from the
original database (as the users on another machine are not guaranteed to exist locally). Notice also the
-d
flag is used to connect to the template1 database before creating, and
connecting to, the booktown database.
Note that the use of the
-O
flag can be dangerous if ownership is an important part of the
recreation of a database. It can play a helpful role in moving from a development environment to a production
environment (e.g., if test or development account names were associated with various database objects). However, if a
database is being restored on an existing machine (e.g., from a nightly backup), it is not recommended that the
-O
flag be used.
|
Warning
|
|
Remember that
pg_restore
exists only for files that are output in either
tar
format (
t
), or compressed
tar
format
(
c
). Plain text SQL dumps may be processed with
psql
, as
documented in the Section called Using psql for plain text dumps
," earlier in this chapter.
|
|
|
