From 24e97528631e7e810ce61fc0f5fbcaca0c001c4c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 9 Oct 2006 23:36:59 +0000 Subject: Revise psql pattern-matching switches as per discussion. The rule is now to process all inclusion switches then all exclusion switches, so that the behavior is independent of switch ordering. Use of -T does not cause non-table objects to be suppressed. And the patterns are now interpreted the same way psql's \d commands do it, rather than as pure regex commands; this allows for example -t schema.tab to do what it should have been doing all along. Re-enable the --blobs switch to do something useful, ie, add back blobs into a dump they were otherwise suppressed from. --- doc/src/sgml/ref/pg_dump.sgml | 303 ++++++++++++++++++++++++++---------------- 1 file changed, 186 insertions(+), 117 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index deafd7c9a98..9aa4baf84e7 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -1,5 +1,5 @@ @@ -14,7 +14,7 @@ PostgreSQL documentation pg_dump - extract a PostgreSQL database into a script file or other archive file + extract a PostgreSQL database into a script file or other archive file @@ -126,6 +126,19 @@ PostgreSQL documentation + + + + + + Include large objects in the dump. This is the default behavior + except when + + + @@ -170,12 +183,14 @@ PostgreSQL documentation Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into - non-PostgreSQL databases. Note that + non-PostgreSQL databases. + Also, since this option generates a separate command for each row, + an error in reloading a row causes only that row to be lost rather + than the entire table contents. + Note that the restore may fail altogether if you have rearranged column order. - The option is safer, though even slower. - Also, while this option generates errors for invalid data, - it allows other INSERTs to continue loading - data into the table. + The option is safe against column order changes, + though even slower. @@ -193,9 +208,9 @@ PostgreSQL documentation ...). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. - Also, while this option generates errors for invalid data, - it allows other INSERTs to continue loading - data into the table. + Also, since this option generates a separate command for each row, + an error in reloading a row causes only that row to be lost rather + than the entire table contents. @@ -238,7 +253,7 @@ PostgreSQL documentation plain - Output a plain-text SQL script file (default) + Output a plain-text SQL script file (the default). @@ -248,10 +263,10 @@ PostgreSQL documentation custom - Output a custom archive suitable for input into - pg_restore. This is the most flexible - format in that it allows reordering of loading data as well - as object definitions. This format is also compressed by default. + Output a custom archive suitable for input into + pg_restore. This is the most flexible + format in that it allows reordering of loading data as well + as object definitions. This format is also compressed by default. @@ -261,11 +276,11 @@ PostgreSQL documentation tar - Output a tar archive suitable for input into - pg_restore. Using this archive format - allows reordering and/or exclusion of database objects - at the time the database is restored. It is also possible to limit - which data is reloaded at restore time. + Output a tar archive suitable for input into + pg_restore. Using this archive format + allows reordering and/or exclusion of database objects + at the time the database is restored. It is also possible to limit + which data is reloaded at restore time. @@ -286,9 +301,11 @@ PostgreSQL documentation - pg_dump can handle databases from + pg_dump can dump from servers running previous releases of PostgreSQL, but very old - versions are not supported anymore (currently prior to 7.0). + versions are not supported anymore (currently, those prior to 7.0). + Dumping from a server newer than pg_dump + is likely not to work at all. Use this option if you need to override the version check (and if pg_dump then fails, don't say you weren't warned). @@ -301,20 +318,61 @@ PostgreSQL documentation - Dump the contents of schema - only. If this option is not specified, all non-system schemas - in the target database will be dumped. + Dump only schemas matching schema; this selects both the + schema itself, and all its contained objects. When this option is + not specified, all non-system schemas in the target database will be + dumped. Multiple schemas can be + selected by writing multiple - In this mode, pg_dump makes no - attempt to dump any other database objects that objects in the - selected schema may depend upon. Therefore, there is no - guarantee that the results of a single-schema dump can be - successfully restored by themselves into a clean database. + When + + + + + Non-schema objects such as blobs are not dumped when + + + + + + + + + + Do not dump any schemas matching the schema pattern. The pattern is + interpreted according to the same rules as for + + + When both @@ -340,7 +398,7 @@ PostgreSQL documentation Do not output commands to set ownership of objects to match the original database. By default, pg_dump issues - ALTER OWNER or + ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created database objects. These statements @@ -397,67 +455,47 @@ PostgreSQL documentation - Dump data for table - only. It is possible for there to be multiple tables with the same - name in different schemas; if that is the case, all matching tables - will be dumped. Also, if any POSIX regular expression character appears - in the table name (([{\.?+, the string will be interpreted - as a regular expression. Note that when in regular expression mode, the - string will not be anchored to the start/end unless ^ and - $ are used at the beginning/end of the string. + Dump only tables (or views or sequences) matching table. Multiple tables can be + selected by writing multiple - The options - For example, to dump a single table named pg_class: - - -$ pg_dump -t pg_class mydb > db.out - - - - To dump all tables starting with employee in the - detroit schema, except for the table named employee_log: - - -$ pg_dump -n detroit -t ^employee -T employee_log mydb > db.out - - - - To dump all schemas starting with east or west and ending in - gsm, but not schemas that contain the letters test, except for - one named east_alpha_test_five: - - -$ pg_dump -n "^(east|west).*gsm$" -N test -n east_alpha_test_five mydb > db.out - - - - - To dump all tables except for those beginning with ts_: - - -$ pg_dump -T "^ts_" mydb > db.out - - - - - In this mode, pg_dump makes no - attempt to dump any other database objects that the selected tables - may depend upon. Therefore, there is no guarantee + When + + + + The behavior of the + @@ -466,36 +504,20 @@ PostgreSQL documentation - Do not dump any matching tables. - More than one option can be used, and POSIX regular expressions are handled just - like -t. + Do not dump any tables matching the table pattern. The pattern is + interpreted according to the same rules as for - - - - - - - - - Dump only the matching schemas. - More than one option can be used, and POSIX regular expressions are handled just - like -t. - - - - - - - - - Do not dump the matching schemas. - More than one option can be used, and POSIX regular expressions are handled just - like -t. + When both - @@ -506,7 +528,7 @@ PostgreSQL documentation Specifies verbose mode. This will cause pg_dump to output detailed object - comments and start/stop times to the dump file, and progress + comments and start/stop times to the dump file, and progress messages to standard error. @@ -742,33 +764,80 @@ CREATE DATABASE foo WITH TEMPLATE template0; Examples - To dump a database: + To dump a database called mydb into a SQL-script file: + +$ pg_dump mydb > db.sql + + + + + To reload such a script into a (freshly created) database named + newdb: + -$ pg_dump mydb > db.out +$ psql -d newdb -f db.sql - To reload this database: + To dump a database into a custom-format archive file: + + +$ pg_dump -Fc mydb > db.dump + + + + + To reload an archive file into a (freshly created) database named + newdb: + + +$ pg_restore -d newdb db.dump + + + + + To dump a single table named mytab: + + +$ pg_dump -t mytab mydb > db.sql + + + + + To dump all tables whose names start with emp in the + detroit schema, except for the table named + employee_log: + + +$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql + + + + + To dump all schemas whose names start with east or + west and end in gsm, excluding any schemas whose + names contain the word test: + -$ psql -d database -f db.out +$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql - To dump a database called mydb to a file in custom format: - file: + The same, using regular expression notation to consolidate the switches: -$ pg_dump -Fc mydb > db.out +$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql - To reload this dump into an existing database called newdb: + To dump all database objects except for tables whose names begin with + ts_: -$ pg_restore -d newdb db.out +$ pg_dump -T 'ts_*' mydb > db.sql -- cgit v1.2.3