From a5cf808be55bcc68c3917c380f95122436af1be1 Mon Sep 17 00:00:00 2001 From: Daniel Gustafsson Date: Wed, 29 Nov 2023 14:56:24 +0100 Subject: Read include/exclude commands for dump/restore from file When there is a need to filter multiple tables with include and/or exclude options it's quite possible to run into the limitations of the commandline. This adds a --filter=FILENAME feature to pg_dump, pg_dumpall and pg_restore which is used to supply a file containing object exclude/include commands which work just like their commandline counterparts. The format of the file is one command per row like: can be "include" or "exclude", can be table_data, index table_data_and_children, database, extension, foreign_data, function, table schema, table_and_children or trigger. This patch has gone through many revisions and design changes over a long period of time, the list of reviewers reflect reviewers of some version of the patch, not necessarily the final version. Patch by Pavel Stehule with some additional hacking by me. Author: Pavel Stehule Reviewed-by: Justin Pryzby Reviewed-by: vignesh C Reviewed-by: Dean Rasheed Reviewed-by: Tomas Vondra Reviewed-by: Julien Rouhaud Reviewed-by: Erik Rijkers Discussion: https://postgr.es/m/CAFj8pRB10wvW0CC9Xq=1XDs=zCQxer3cbLcNZa+qiX4cUH-G_A@mail.gmail.com --- doc/src/sgml/ref/pg_dump.sgml | 117 +++++++++++++++++++++++++++++++++++++++ doc/src/sgml/ref/pg_dumpall.sgml | 31 +++++++++++ doc/src/sgml/ref/pg_restore.sgml | 80 ++++++++++++++++++++++++++ 3 files changed, 228 insertions(+) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 8695571045b..0e5ba4f7125 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -836,6 +836,109 @@ PostgreSQL documentation + + + + + Specify a filename from which to read patterns for objects to include + or exclude from the dump. The patterns are interpreted according to the + same rules as the corresponding options: + /, + , + or + for tables, + / for schemas, + for data on foreign servers and + , + for table data, + / for extensions. + To read from STDIN, use - as the + filename. The option can be specified in + conjunction with the above listed options for including or excluding + objects, and can also be specified more than once for multiple filter + files. + + + + The file lists one object pattern per row, with the following format: + +{ include | exclude } { extension | foreign_data | table | table_and_children | table_data | table_data_and_children | schema } PATTERN + + + + + The first keyword specifies whether the objects matched by the pattern + are to be included or excluded. The second keyword specifies the type + of object to be filtered using the pattern: + + + + extension: extensions, works like the + option. This keyword can only be + used with the include keyword. + + + + + foreign_data: data on foreign servers, works like + the option. This keyword can + only be used with the include keyword. + + + + + table: tables, works like the + / option. + + + + + table_and_children: tables including any partitions + or inheritance child tables, works like the + option. + + + + + table_data: table data of any tables matching + pattern, works like the + option. This keyword can only + be used with the exclude keyword. + + + + + table_data_and_children: table data of any tables + matching pattern as well as any partitions + or inheritance children of the table(s), works like the + option. This + keyword can only be used with the exclude keyword. + + + + + schema: schemas, works like the + / option. + + + + + + + Lines starting with # are considered comments and + ignored. Comments can be placed after an object pattern row as well. + Blank lines are also ignored. See + for how to perform quoting in patterns. + + + + Example files are listed below in the + section. + + + + + @@ -1168,6 +1271,7 @@ PostgreSQL documentation schema (/) and table (/) pattern match at least one extension/schema/table in the database to be dumped. + This also applies to filters used with . Note that if none of the extension/schema/table patterns find matches, pg_dump will generate an error even without . @@ -1611,6 +1715,19 @@ CREATE DATABASE foo WITH TEMPLATE template0; $ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql + + + + To dump all tables whose names start with mytable, except + for table mytable2, specify a filter file + filter.txt like: + +include table mytable* +exclude table mytable2 + + + +$ pg_dump --filter=filter.txt mydb > db.sql diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index d31585216c6..4d7c0464687 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -125,6 +125,37 @@ PostgreSQL documentation + + + + + Specify a filename from which to read patterns for databases excluded + from the dump. The patterns are interpreted according to the same rules + as . + To read from STDIN, use - as the + filename. The option can be specified in + conjunction with for excluding + databases, and can also be specified more than once for multiple filter + files. + + + + The file lists one database pattern per row, with the following format: + +exclude database PATTERN + + + + + Lines starting with # are considered comments and + ignored. Comments can be placed after an object pattern row as well. + Blank lines are also ignored. See + for how to perform quoting in patterns. + + + + + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 374d8d8715c..1a23874da68 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -190,6 +190,86 @@ PostgreSQL documentation + + + + + Specify a filename from which to read patterns for objects excluded + or included from restore. The patterns are interpreted according to the + same rules as + / for including objects in schemas, + /for excluding objects in schemas, + / for restoring named functions, + / for restoring named indexes, + / for restoring named tables + or / for restoring triggers. + To read from STDIN, use - as the + filename. The option can be specified in + conjunction with the above listed options for including or excluding + objects, and can also be specified more than once for multiple filter + files. + + + + The file lists one database pattern per row, with the following format: + +{ include | exclude } { function | index | schema | table | trigger } PATTERN + + + + + The first keyword specifies whether the objects matched by the pattern + are to be included or excluded. The second keyword specifies the type + of object to be filtered using the pattern: + + + + function: functions, works like the + / option. This keyword + can only be used with the include keyword. + + + + + index: indexes, works like the + / option. This keyword + can only be used with the include keyword. + + + + + schema: schemas, works like the + / and + / options. + + + + + table: tables, works like the + / option. This keyword + can only be used with the include keyword. + + + + + trigger: triggers, works like the + / option. This keyword + can only be used with the include keyword. + + + + + + + Lines starting with # are considered comments and + ignored. Comments can be placed after an object pattern row as well. + Blank lines are also ignored. See + for how to perform quoting in patterns. + + + + + -- cgit v1.2.3