From 627c0d4472fc513fbd7a2fed709ec81681f1082e Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 22 Aug 2001 20:23:24 +0000 Subject: Add option to output SET SESSION AUTHORIZATION commands rather than \connect, to avoid possible password prompts and such, at the drawback of having to have superuser access. --- doc/src/sgml/ref/pg_dump.sgml | 189 +++++++++++++++++++++++++++++---------- doc/src/sgml/ref/pg_restore.sgml | 102 ++++++++++++++++----- 2 files changed, 221 insertions(+), 70 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 2eec491cd42..1da554aa70e 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -1,5 +1,5 @@ @@ -43,6 +43,7 @@ Postgres documentation -t table -v -x + -X keyword -Z 0...9 -h host -p port @@ -59,44 +60,56 @@ Postgres documentation - pg_dump is a utility for dumping out a - Postgres database into a script or archive - file containing query commands. The script files are in text format - and can be used to reconstruct the database, even on other machines - and other architectures. - The archive files, new with version 7.1, contain enough information for - to rebuild the database, but also - allow pg_restore to be selective about what is restored, or even to - reorder the items prior to being restored. The archive files are - also designed to be portable across architectures. + pg_dump is a utility for saving a + PostgreSQL database into a script or an + archive file. The script files are in plain text format and + contain the SQL commands required to reconstruct the database to + the state it was in at the time is was saved. They can be used to + reconstruct the database even on other machines and other + architectures, with some modifications even on other RDBMS + products. The alternative archive file formats are meant to be + used with to rebuild the database, + and they also allow pg_restore to be selective + about what is restored, or even to reorder the items prior to being + restored. The archive files are also designed to be portable across + architectures. - pg_dump - will produce the queries necessary to re-generate all - user-defined types, functions, tables, indexes, aggregates, and - operators. In addition, all the data is copied out in text format so - that it can be readily copied in again, as well as imported into tools - for editing. + pg_dump will save the information necessary to + re-generate all user-defined types, functions, tables, indexes, + aggregates, and operators. In addition, all the data is copied out + in text format so that it can be readily copied in again, as well + as imported into tools for editing. pg_dump is useful for dumping out the contents of a database to move from one - Postgres installation to another. After running - pg_dump, - one should examine the output for any warnings, especially - in light of the limitations listed below. + Postgres installation to another. - When used with one of the alternate file formats and combined with - pg_restore, it provides a flexible archival - and transfer mechanism. pg_dump can be used - to backup an entire database, then pg_restore - can be used to examine the archive and/or select which parts of the - database are to be restored. - See the documentation for details. + When used with one of the archive file formats and combined with + pg_restore, it provides a flexible archival and + transfer mechanism. pg_dump can be used to + backup an entire database, then pg_restore can + be used to examine the archive and/or select which parts of the + database are to be restored. See the documentation for details. + + + + While running pg_dump, one should examine the + output for any warnings (printed on standard error), especially in + light of the limitations listed below. + + + + pg_dump makes consistent backups even if the + database is being used concurrently. pg_dump + does not block other users accessing the database (readers or + writers). @@ -141,7 +154,7 @@ Postgres documentation --clean - Dump commands to clean (drop) the schema prior to (the + Output commands to clean (drop) the schema prior to (the commands for) creating it. @@ -162,9 +175,10 @@ Postgres documentation --inserts - Dump data as proper INSERT commands (not - COPY). This will make restoration very - slow. + Dump data as proper INSERT commands (rather + than COPY). This will make restoration very + slow, but it makes the archives more portable to other RDBMS + packages. @@ -189,7 +203,8 @@ Postgres documentation --file=file - Send output to the specified file. + Send output to the specified file. If this is omitted, the + standard output is used. @@ -199,7 +214,8 @@ Postgres documentation --format=format - Format can be one of the following: + Selects the format of the output. + format can be one of the following: @@ -289,7 +305,10 @@ Postgres documentation --oids - Dump object identifiers (OIDs) for every table. + Dump object identifiers (OIDs) for every + table. Use this option if your application references the oid + columns in some way (e.g., in a foreign key constraint). + Otherwise, this option should not be used. @@ -299,11 +318,22 @@ Postgres documentation --no-owner - In plain text output mode, do not set object ownership to - match the original database. Typically, - pg_dump issues - (psql-specific) \connect - statements to set ownership of schema elements. + In plain text output mode, do not output commands to set the + object ownership to match the original database. Typically, + pg_dump issues + (psql-specific) \connect + statements to set ownership of schema elements. See also + under and . Note that + does not prevent all reconnections to the + database, only the ones that are exclusively used for + ownership adjustments. + + + + This option is only meaningful for the plain text format. For + the other formats, you need to specify the option when you + call pg_restore. @@ -313,8 +343,27 @@ Postgres documentation --no-reconnect - In plain text output mode, prohibit pg_dump - from issuing any \connect statements. + In plain text output mode, prohibit pg_dump + from outputting a script that would require reconnections to + the database while being restored. An average restoration + script usually has to reconnect several times as different + users to set the original ownerships of the objects. This + option is a rather blunt instrument because it makes + pg_dump lose this ownership information, + unless you use the option. + + + + One possible reason why reconnections during restore might not + be desired is if the access to the database requires manual + interaction (e.g., passwords). + + + + This option is only meaningful for the plain text format. For + the other formats, you need to specify the option when you + call pg_restore. @@ -334,8 +383,10 @@ Postgres documentation --superuser=username - Specify the superuser user name to use when disabling triggers and/or - setting ownership of schema elements. + The scripts or archives created by pg_dump + need to have superuser access in certain cases, such as when + disabling triggers or setting ownership of schema elements. + This option specifies the user name to use for those cases. @@ -366,8 +417,42 @@ Postgres documentation --no-acl - Prevent dumping of access privileges (grant/revoke commands) - and table ownership information. + Prevent dumping of access privileges (grant/revoke commands). + + + + + + -X use-set-session-authorization + --use-set-session-authorization + + + Normally, if a (plain text mode) script generated by + pg_dump must alter the current database + user (e.g., to set correct object ownerships), it uses the + \connect command. + This command actually opens a new connection, which might + require manual interaction (e.g., passwords). If you use the + , then + pg_dump will instead output commands. This has + the same effect, but it requires that the user restoring the + database from the generated script be a database superuser. + This option effectively overrides the + option. + + + + Since is a + standard SQL command, whereas \connect only + works in , this option also enhances + the theoretical portability of the output script. + + + + This option is only meaningful for the plain text format. For + the other formats, you need to specify the option when you + call pg_restore. @@ -442,7 +527,6 @@ Postgres documentation - Diagnostics @@ -551,6 +635,17 @@ connectDBStart() -- connect() failed: No such file or directory + + History + + + The pg_dump utility first appeared in + Postgres95 release 0.02. The + non-plain-text output formats were introduced in + PostgreSQL 7.1. + + + See Also diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 0acb3fb1512..22f264ceac1 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -1,4 +1,4 @@ - + @@ -44,6 +44,7 @@ -T trigger -v -x + -X keyword -h host -p port -U username @@ -60,38 +61,54 @@ pg_restore is a utility for restoring a - Postgres database dumped by - in one of the non-plain-text formats. + Postgres database from an archive + created by in one of the non-plain-text + formats. - The archive files, new with the 7.1 release, contain enough information for - pg_restore to rebuild the database, but also allow - pg_restore to be selective about what is restored, - or even to reorder the items prior to being restored. The archive files are designed - to be portable across architectures. pg_dump will - produce the queries necessary to re-generate all user-defined types, functions, - tables, indexes, aggregates, and operators. In addition, all the data is copied - out (in text format for scripts) so that it can be readily copied in again. + The archive files contain information for + pg_restore to rebuild the database, but also + allow pg_restore to be selective about what is + restored, or even to reorder the items prior to being restored. The + archive files are designed to be portable across architectures. It + will issue the commands necessary to re-generate all user-defined + types, functions, tables, indexes, aggregates, and operators, as + well as the data in the tables. - pg_restore reads the archive file and outputs the appropriate - SQL in the required order based on the command parameters. Obviously, it can not restore - information that is not present in the dump file; so if the dump is made using the - dump data as INSERTs option, pg_restore will not be able to - load the data using COPY statements. + pg_restore can operate in two modes: If a + database name is specified, the archive is restored directly into + the database. Otherwise, a script containing the SQL commands + necessary to rebuild the database is created (and written to a file + or standard output), similar to the ones created by the + pg_dump plain text format. Some of the options + controlling the script output are therefore analogous to + pg_dump. - The most flexible output file format is the custom format (). It allows for - selection and reordering of all archived items, and is compressed by default. The tar - format () is not compressed and it is not possible to reorder - data when loading, but it is otherwise quite flexible. + Obviously, pg_restore cannot restore information + that is not present in the archive file; for instance, if the + archive was made using the dump data as + INSERTs option, + pg_restore will not be able to load the data + using COPY statements. - To reorder the items, it is first necessary to dump the contents of the archive: + The most flexible output file format is the custom + format (). It allows for selection and + reordering of all archived items, and is compressed by default. The + tar format () is not + compressed and it is not possible to reorder data when loading, but + it is otherwise quite flexible. + + + + To reorder the items, it is first necessary to dump the table of + contents of the archive: $ pg_restore archive.file -l > archive.list @@ -346,8 +363,20 @@ --no-reconnect - Prohibit pg_restore from issuing any \connect - statements or reconnecting to the database if directly connected. + While restoring an archive, pg_restore + typically has to reconnect to the database several times with + different user names to set the correct ownership of the + created objects. If this is undesriable (e.g., because manual + interaction (passwords) would be necessary for each + reconnection), this option prevents + pg_restore from issuing any reconnection + requests. (A connection request while in plain text mode, not + connected to a database, is made by putting out a \connect command.) + However, this option is a rather blunt instrument because it + makes pg_restore lose all object ownership + information, unless you use the + option. @@ -414,6 +443,25 @@ + + -X use-set-session-authorization + --use-set-session-authorization + + + Normally, if restoring an archive requires altering the + current database user (e.g., to set correct object + ownerships), a new connection to the database must be openend, + which might require manual interaction (e.g., passwords). If + you use the , + then pg_restore will instead use the command. This has + the same effect, but it requires that the user restoring the + archive is a database superuser. This option effectively + overrides the option. + + + + @@ -592,6 +640,14 @@ connectDBStart() -- connect() failed: No such file or directory + + History + + + The pg_restore utility first appeared in + PostgreSQL 7.1. + + See Also -- cgit v1.2.3