Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

Restoring a Database

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).

Using psql for plain text dumps

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

You are now connected to database booktown as user postgres.


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.

Using pg_restore for tarred and compressed dumps

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 [ 
 ] [ 

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.


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.


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.


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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire