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
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

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

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using pg_dump

The pg_dump (which is short for "PostgreSQL dump") application is run from a command line, and creates a list of SQL commands. These commands, executed in the order provided, re-create the database from scratch.

Here is the syntax for pg_dump :

  pg_dump [ 
options
 ] 
dbname

In this syntax, dbname is the name of the database that you want to "dump" SQL instructions for. The available options are similar in format to those of the other database management utilities included with PostgreSQL, such as createdb . The most common flag specified in the options to pg_dump is the -f flag, which specifies the file to store the dumped SQL statements within.

Note: If the -f flag is not specified to pg_dump , the dumped SQL will be written to stdout rather than stored in a file.

The complete list of pg_dump options follow:

-a, - -data-only

Forces only COPY or INSERT SQL statements to be dumped (depending on whether or not the -d flag is used). This results in a backup of data, and not database objects (or schema ). If the -d flag is not passed along with this flag, the dumped COPY commands are used to copy all data from stdin (i.e., the rows are stored literally within the dumped file as COPY commands from stdin ). Otherwise, each row is represented as sequential INSERT statements.

-b, - -blobs

Causes any large objects to be dumped as well as normal data. This option also requires that the -F flag be provided with either the t or c format. By default, large object data is not dumped.

-c, - -clean

Specifies that SQL statements to drop all existing objects will precede the SQL statements to create those objects. This option is useful in re-initializing an existing database, rather than dropping it and recreating it from scratch.

-C, - -create

Specifies that the SQL statement to create the database (CREATE DATABASE) should be included in the dump.

-d, - -inserts

Causes INSERT statements to be dumped for each row of data, rather than the default COPY statements. This can be safer, as a single corrupted row will cause a COPY statement to fail, though it is a much slower process to add a single row at a time during restoration.

-D, - -attribute-inserts

Like the -d flag, causes INSERT statements to be dumped; however, with this flag, each INSERT statement is created with an explicit column target list in parentheses, immediately preceding the VALUES.

-f FILENAME , - -file=FILENAME

Directs the output of pg_dump to a file named FILENAME , rather than to stdout . The user executing pg_dump must have system permissions to write to this file.

-F { c | t | p }, - -format { c | t | p }

Determines the file format of the output:

c ( gzip compressed)

A format of c creates a gzip -compressed tar file (i.e., a .tar.gz file).

t ( tar )

A value of t creates a tar file (i.e., a .tar file).

p (plain text)

The default value of p causes plain text output.

Note that pg_restore is typically used to handle files created with the c or t ( gzip -compressed or tar ) formats.

-h HOSTNAME , - -host= HOSTNAME

Specifies that HOSTNAME should be connected to, rather than the localhost. Use this when the target database is on another server.

-i, - -ignore-version

Overrides the check between the version of pg_dump and the version of PostgreSQL running. This option is not recommended unless there is no other alternative, as it most likely will produce errors due to changes in the system catalogs between versions. Normally, you should use the version of pg_dump matching the database that you are backing up.

-n, - -no-quotes

Suppresses any double-quotes surrounding identifiers unless there are normally illegal characters in the identifier (e.g., spaces, or uppercase characters).

-N, - -quotes

Explicitly indicates that double-quotes should be used around all identifiers. This has been the default behavior of pg_dump since PostgreSQL 6.4.

-o, - -oid

Causes OIDs (object identifiers) for each row of data to be dumped as well. This can be vital if any of your applications based around the data in PostgreSQL use OIDs in any kind of meaningful or associative way.

-O, - -no-owner

Causes ownership to not be taken into account in the dump. A restore with suppressed ownership will cause all re-created objects to belong to the user performing the restore.

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

-R, - -no-reconnect

Suppresses any \connect statements, which are usually used to enforce currently reflected ownerships when a backup is restored. This is similar in practice to the -O flag, but also precludes the ability to use the -C flag, as a reconnect is required after creation of a new database.

-s, - -schema-only

Causes only the schema-related (database objects such as tables, sequences, indices and views) SQL statements to be dumped, ignoring re-creation of the data. This can be useful in moving a general database structure from a development machine to a production machine.

-t TABLE , - -table= TABLE

Causes only TABLE to be dumped from the specified database, rather than all tables.

-u, - -password

Provides a prompt for a username and password. As of PostgreSQL 7.1.x, this is the only method to provide an alternate username. If the user's password is unset (NULL), you may simply press enter when prompted for it.

-v, - -verbose

Causes verbose output from the pg_dump functions being performed to be displayed to stderr (not stdout ).

-x, - -no-acl

Suppresses any GRANT or REVOKE statements, which are usually used to preserve the rights set at the time of the dump. Use this flag if you do not wish to enforce any existing rights or restrictions when re-creating a database from this dump.

-Z, - -compress { 0 - 9 }

Sets the degree of compression (0 for the least compression, 9 for the most) when used with the -F c argument.

Any system user may run pg_dump by default, but the user with which you connect to PostgreSQL must have SELECT rights for every object in the database being dumped. Example 9-20 demonstrates the use of pg_dump on the booktown database, with the manager PostgreSQL user. The -C flag passed causes the CREATE DATABASE command to be included in the SQL dump as well. This command is not always included, as you may prefer to create the database beforehand with non-standard options.

Example 9-20. Using pg_dump

[[email protected] ~]$ 
pg_dump -u -C -f booktown.sql booktown

Username: 
manager

Password:

[[email protected] ~]$ 
ls -l booktown.sql

-rw-rw-r- -    1 jworsley jworsley    46542 Sep 13 16:42 booktown.sql

Note that since pg_dump provides the standard connection options for specifying a host connection (e.g., the -h , -u and -p flags), it can be used to perform remote backups from any host allowed to make a remote connection (according to the pg_hba.conf file: see Chapter 8 for more on this subject). Example 9-21 shows a user on a remote server specifying a connection to the booktown.commandprompt.com server, in order to create a backup file in compressed format (with the -F c flag) called booktown.sql.tar.gz .

Example 9-21. Using pg_dump remotely

[[email protected] ~]$ 
pg_dump -u -h booktown.commandprompt.com \

> 
                        -F c -f booktown.sql.tar.gz booktown

Username: 
manager

Password:

[[email protected] ~]$ 
ls -l booktown.sql.tar.gz

-rw-rw-r- -    1 jworsley jworsley   45909 Sep 13 17:12 booktown.sql.tar.gz

If you wish to use large objects in your dumped file, it is necessary that you use either the tar ( t ) or gzip -compressed ( c ) format, since the plain-text format cannot include large objects. Otherwise, normal plain-text formatted dumps are suitable for most users.

Creating a dump in tar format can often result in a backup file more than twice the size of its plain-text counterpart, even without large objects. This is because the tar format involves a hierarchy of TOC (table of contents) .dat files which represent information on how to be used by the corresponding pg_restore command; thus, more disk space is taken up by the extra instructions. Since tar is not a compression utility, the gzip -compressed ( c ) format exists as well to compress the tarred file into gzipped format automatically.

Databases - Practical PostgreSQL
Previous Page Home Next Page

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