From e86d4dca9b4754185a1d559fdea7bf4ee2de8b1a Mon Sep 17 00:00:00 2001 From: "okbob@github.com" Date: Thu, 16 Mar 2023 08:18:08 +0100 Subject: [PATCH 1/3] possibility to read options for dump from file --- doc/src/sgml/ref/pg_dump.sgml | 110 +++ doc/src/sgml/ref/pg_dumpall.sgml | 22 + doc/src/sgml/ref/pg_restore.sgml | 25 + src/bin/pg_dump/Makefile | 5 +- src/bin/pg_dump/filter.c | 489 ++++++++++++++ src/bin/pg_dump/filter.h | 56 ++ src/bin/pg_dump/meson.build | 2 + src/bin/pg_dump/pg_dump.c | 117 ++++ src/bin/pg_dump/pg_dumpall.c | 61 +- src/bin/pg_dump/pg_restore.c | 114 ++++ src/bin/pg_dump/t/005_pg_dump_filterfile.pl | 701 ++++++++++++++++++++ src/tools/msvc/Mkvcbuild.pm | 1 + 12 files changed, 1700 insertions(+), 3 deletions(-) create mode 100644 src/bin/pg_dump/filter.c create mode 100644 src/bin/pg_dump/filter.h create mode 100644 src/bin/pg_dump/t/005_pg_dump_filterfile.pl diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index d6b1faa8042..5672fbb273b 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -829,6 +829,102 @@ 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. + 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 } { table | table_and_children | schema | foreign_data | table_data | table_data_and_children } 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: + + + + table: tables, works like + / + + + + + table_and_children: tables, works like + /, except that + it also includes any partitions or inheritance child + tables of the table(s) matching the + pattern. + + + + + schema: schemas, works like + / + + + + + foreign_data: data on foreign servers, works like + . This keyword can only be + used with the include keyword. + + + + + table_data: table data, works like + . This keyword can only be + used with the exclude keyword. + + + + + table_data_and_children: table data of any + partitions or inheritance child, works like + . This keyword can only be + used with the exclude keyword. + + + + + + + + Lines starting with # are considered comments and + ignored. Comments can be placed after filter as well. Blank lines + are also ignored. See for how to + perform quoting in patterns. + + + + Example files are listed below in the + section. + + + + + @@ -1159,6 +1255,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 . @@ -1581,6 +1678,19 @@ CREATE DATABASE foo WITH TEMPLATE template0; $ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql + + + + To dump all tables with names starting 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 e219a79858e..ca2f93652cd 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -122,6 +122,28 @@ PostgreSQL documentation + + + + + Specify a filename from which to read patterns for databases excluded + from dump. The patterns are interpretted according to the same rules + as . + To read from STDIN, use - as the + filename. The option can be specified in + conjunction with the above listed options 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 + + + + + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 47bd7dbda06..ffeb564c520 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -188,6 +188,31 @@ PostgreSQL documentation + + + + + Specify a filename from which to read patterns for objects excluded + or included from restore. The patterns are interpretted according to the + same rules as , , + , , + or . + 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 + + + + + diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile index eb8f59459a1..bff55b6b1a2 100644 --- a/src/bin/pg_dump/Makefile +++ b/src/bin/pg_dump/Makefile @@ -30,6 +30,7 @@ OBJS = \ compress_lz4.o \ compress_none.o \ dumputils.o \ + filter.o \ parallel.o \ pg_backup_archiver.o \ pg_backup_custom.o \ @@ -47,8 +48,8 @@ pg_dump: pg_dump.o common.o pg_dump_sort.o $(OBJS) | submake-libpq submake-libpg pg_restore: pg_restore.o $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils $(CC) $(CFLAGS) pg_restore.o $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) -pg_dumpall: pg_dumpall.o dumputils.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils - $(CC) $(CFLAGS) pg_dumpall.o dumputils.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) +pg_dumpall: pg_dumpall.o dumputils.o filter.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils + $(CC) $(CFLAGS) pg_dumpall.o dumputils.o filter.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) install: all installdirs $(INSTALL_PROGRAM) pg_dump$(X) '$(DESTDIR)$(bindir)'/pg_dump$(X) diff --git a/src/bin/pg_dump/filter.c b/src/bin/pg_dump/filter.c new file mode 100644 index 00000000000..971b3d93eb1 --- /dev/null +++ b/src/bin/pg_dump/filter.c @@ -0,0 +1,489 @@ +/*------------------------------------------------------------------------- + * + * Implementation of simple filter file parser + * + * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/bin/pg_dump/filter.c + * + *------------------------------------------------------------------------- + */ +#include "postgres_fe.h" + +#include "common/fe_memutils.h" +#include "common/logging.h" +#include "common/string.h" +#include "filter.h" +#include "lib/stringinfo.h" +#include "pqexpbuffer.h" + +#define is_keyword_str(cstr, str, bytes) \ + ((strlen(cstr) == (bytes)) && (pg_strncasecmp((cstr), (str), (bytes)) == 0)) + +/* + * Following routines are called from pg_dump, pg_dumpall and pg_restore. + * Unfortunately, the implementation of exit_nicely in pg_dump and pg_restore is + * different from the one in pg_dumpall, so instead of calling exit_nicely we + * have to return some error flag (in this case NULL), and exit_nicelly will be + * executed from caller's routine. + */ + +/* + * Opens filter's file and initialize fstate structure. + * Returns true on success. + */ +bool +filter_init(FilterStateData *fstate, const char *filename) +{ + fstate->filename = filename; + fstate->lineno = 0; + initStringInfo(&fstate->linebuff); + + if (strcmp(filename, "-") != 0) + { + fstate->fp = fopen(filename, "r"); + if (!fstate->fp) + { + pg_log_error("could not open filter file \"%s\": %m", filename); + return false; + } + } + else + fstate->fp = stdin; + + fstate->is_error = false; + + return true; +} + +/* + * Release allocated resources for the given filter. + */ +void +filter_free(FilterStateData *fstate) +{ + free(fstate->linebuff.data); + fstate->linebuff.data = NULL; + + if (fstate->fp && fstate->fp != stdin) + { + if (fclose(fstate->fp) != 0) + pg_log_error("could not close filter file \"%s\": %m", fstate->filename); + + fstate->fp = NULL; + } +} + +/* + * Translate FilterObjectType enum to string. It is designed for formatting + * of error message in log_unsupported_filter_object_type routine. + */ +static const char * +filter_object_type_name(FilterObjectType fot) +{ + switch (fot) + { + case FILTER_OBJECT_TYPE_NONE: + return "comment or empty line"; + case FILTER_OBJECT_TYPE_TABLE_DATA: + return "table data"; + case FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN: + return "table_data_and_children"; + case FILTER_OBJECT_TYPE_DATABASE: + return "database"; + case FILTER_OBJECT_TYPE_FOREIGN_DATA: + return "foreign data"; + case FILTER_OBJECT_TYPE_FUNCTION: + return "function"; + case FILTER_OBJECT_TYPE_INDEX: + return "index"; + case FILTER_OBJECT_TYPE_SCHEMA: + return "schema"; + case FILTER_OBJECT_TYPE_TABLE: + return "table"; + case FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN: + return "table_and_children"; + case FILTER_OBJECT_TYPE_TRIGGER: + return "trigger"; + } + + /* should never get here */ + pg_unreachable(); +} + +/* + * Emit error message "invalid format in filter file ..." + * + * This is mostly a convenience routine to avoid duplicating file closing code + * in multiple callsites. + */ +void +log_invalid_filter_format(FilterStateData *fstate, char *message) +{ + if (fstate->fp != stdin) + { + pg_log_error("invalid format in filter file \"%s\" on line %d: %s", + fstate->filename, + fstate->lineno, + message); + } + else + pg_log_error("invalid format in filter on line %d: %s", + fstate->lineno, + message); + + fstate->is_error = true; +} + +/* + * Emit error message "The application doesn't support filter for object type ..." + * + * This is mostly a convenience routine to avoid duplicating file closing code + * in multiple callsites. + */ +void +log_unsupported_filter_object_type(FilterStateData *fstate, + const char *appname, + FilterObjectType fot) +{ + PQExpBuffer str = createPQExpBuffer(); + + printfPQExpBuffer(str, + "\"%s\" doesn't support filter for object type \"%s\".", + appname, + filter_object_type_name(fot)); + + log_invalid_filter_format(fstate, str->data); +} + +/* + * filter_get_keyword - read the next filter keyword from buffer + * + * Search for keywords (limited to ascii alphabetic characters) in + * the passed in line buffer. Returns NULL when the buffer is empty or first + * char is not alpha. The char '_' is allowed too (exclude first position). + * The length of the found keyword is returned in the size parameter. + */ +static const char * +filter_get_keyword(const char **line, int *size) +{ + const char *ptr = *line; + const char *result = NULL; + + /* Set returnlength preemptively in case no keyword is found */ + *size = 0; + + /* Skip initial whitespace */ + while (isspace(*ptr)) + ptr++; + + if (isalpha(*ptr)) + { + result = ptr++; + + while (isalpha(*ptr) || *ptr == '_') + ptr++; + + *size = ptr - result; + } + + *line = ptr; + + return result; +} + +/* + * read_quoted_pattern - read quoted possibly multi lined string. + * + * Returns pointer to next char after ending double quotes or NULL on error. + */ +static const char * +read_quoted_string(FilterStateData *fstate, + const char *str, + PQExpBuffer pattern) +{ + appendPQExpBufferChar(pattern, '"'); + str++; + + while (1) + { + /* + * We can ignore \r or \n chars because the string is read by + * pg_get_line_buf, so these chars should be just trailing chars. + */ + if (*str == '\r' || *str == '\n') + { + str++; + continue; + } + + if (*str == '\0') + { + Assert(fstate->linebuff.data); + + if (!pg_get_line_buf(fstate->fp, &fstate->linebuff)) + { + if (ferror(fstate->fp)) + { + pg_log_error("could not read from filter file \"%s\": %m", + fstate->filename); + fstate->is_error = true; + } + else + log_invalid_filter_format(fstate, "unexpected end of file"); + + return NULL; + } + + str = fstate->linebuff.data; + + appendPQExpBufferChar(pattern, '\n'); + fstate->lineno++; + } + + if (*str == '"') + { + appendPQExpBufferChar(pattern, '"'); + str++; + + if (*str == '"') + { + appendPQExpBufferChar(pattern, '"'); + str++; + } + else + break; + } + else if (*str == '\\') + { + str++; + if (*str == 'n') + appendPQExpBufferChar(pattern, '\n'); + else if (*str == '\\') + appendPQExpBufferChar(pattern, '\\'); + + str++; + } + else + appendPQExpBufferChar(pattern, *str++); + } + + return str; +} + +/* + * read_pattern - reads on object pattern from input + * + * This function will parse any valid identifier (quoted or not, qualified or + * not), which can also includes the full signature for routines. + * Note that this function takes special care to sanitize the detected + * identifier (removing extraneous whitespaces or other unnecessary + * characters). This is necessary as most backup/restore filtering functions + * only recognize identifiers if they are written exactly way as they are + * regenerated. + * Returns a pointer to next character after the found identifier, or NULL on + * error. + */ +static const char * +read_pattern(FilterStateData *fstate, const char *str, PQExpBuffer pattern) +{ + bool skip_space = true; + bool found_space = false; + + /* Skip initial whitespace */ + while (isspace(*str)) + str++; + + if (*str == '\0') + { + log_invalid_filter_format(fstate, "missing object name pattern"); + return NULL; + } + + while (*str && *str != '#') + { + while (*str && !isspace(*str) && !strchr("#,.()\"", *str)) + { + /* + * Append space only when it is allowed, and when it was found + * in original string. + */ + if (!skip_space && found_space) + { + appendPQExpBufferChar(pattern, ' '); + skip_space = true; + } + + appendPQExpBufferChar(pattern, *str++); + } + + skip_space = false; + + if (*str == '"') + { + if (found_space) + appendPQExpBufferChar(pattern, ' '); + + str = read_quoted_string(fstate, str, pattern); + if (!str) + return NULL; + } + else if (*str == ',') + { + appendPQExpBufferStr(pattern, ", "); + skip_space = true; + str++; + } + else if (*str && strchr(".()", *str)) + { + appendPQExpBufferChar(pattern, *str++); + skip_space = true; + } + + found_space = false; + + /* skip ending whitespaces */ + while (isspace(*str)) + { + found_space = true; + str++; + } + } + + return str; +} + +/* + * filter_read_item - Read command/type/pattern triplet from a filter file + * + * This will parse one filter item from the filter file, and while it is a + * row based format a pattern may span more than one line due to how object + * names can be constructed. The expected format of the filter file is: + * + * + * + * command can be "include" or "exclude" + * object_type can one of: "table", "schema", "foreign_data", "table_data", + * "database", "function", "trigger" or "index" + * pattern can be any possibly-quoted and possibly-qualified identifier. It + * follows the same rules as other object include and exclude functions so it + * can also use wildcards. + * + * Returns true when one filter item was successfully read and parsed. When + * object name contains \n chars, then more than one line from input file can + * be processed. Returns false when the filter file reaches EOF. In case of + * error, the function will emit an appropriate error message before returning + * false. + */ +bool +filter_read_item(FilterStateData *fstate, + bool *is_include, + char **objname, + FilterObjectType *objtype) +{ + Assert(!fstate->is_error); + + if (pg_get_line_buf(fstate->fp, &fstate->linebuff)) + { + const char *str = fstate->linebuff.data; + const char *keyword; + int size; + PQExpBufferData pattern; + + fstate->lineno++; + + /* Skip initial white spaces */ + while (isspace(*str)) + str++; + + /* + * Skip empty lines or lines where the first non-whitespace character + * is a hash indicating a comment. + */ + if (*str != '\0' && *str != '#') + { + /* + * First we expect sequence of two keywords, {include|exclude} + * followed by the object type to operate on. + */ + keyword = filter_get_keyword(&str, &size); + if (!keyword) + { + log_invalid_filter_format(fstate, + "no filter command found (expected \"include\" or \"exclude\")"); + return false; + } + + if (is_keyword_str("include", keyword, size)) + *is_include = true; + else if (is_keyword_str("exclude", keyword, size)) + *is_include = false; + else + { + log_invalid_filter_format(fstate, + "invalid filter command (expected \"include\" or \"exclude\")"); + return false; + } + + keyword = filter_get_keyword(&str, &size); + if (!keyword) + { + log_invalid_filter_format(fstate, "missing filter object type"); + return false; + } + + if (is_keyword_str("table_data", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_TABLE_DATA; + else if (is_keyword_str("table_data_and_children", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN; + else if (is_keyword_str("database", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_DATABASE; + else if (is_keyword_str("foreign_data",keyword, size)) + *objtype = FILTER_OBJECT_TYPE_FOREIGN_DATA; + else if (is_keyword_str("function", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_FUNCTION; + else if (is_keyword_str("index", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_INDEX; + else if (is_keyword_str("schema", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_SCHEMA; + else if (is_keyword_str("table", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_TABLE; + else if (is_keyword_str("table_and_children", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN; + else if (is_keyword_str("trigger", keyword, size)) + *objtype = FILTER_OBJECT_TYPE_TRIGGER; + else + { + PQExpBuffer str = createPQExpBuffer(); + + printfPQExpBuffer(str, "unsupported filter object type: \"%.*s\"", size, keyword); + log_invalid_filter_format(fstate, str->data); + return false; + } + + initPQExpBuffer(&pattern); + + str = read_pattern(fstate, str, &pattern); + if (!str) + return false; + + *objname = pattern.data; + } + else + { + *objname = NULL; + *objtype = FILTER_OBJECT_TYPE_NONE; + } + + return true; + } + + if (ferror(fstate->fp)) + { + pg_log_error("could not read from filter file \"%s\": %m", fstate->filename); + fstate->is_error = true; + } + + return false; +} diff --git a/src/bin/pg_dump/filter.h b/src/bin/pg_dump/filter.h new file mode 100644 index 00000000000..28c5c9c8346 --- /dev/null +++ b/src/bin/pg_dump/filter.h @@ -0,0 +1,56 @@ +/*------------------------------------------------------------------------- + * + * filter.h + * Common header file for the parser of filter file + * + * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/bin/pg_dump/filter.h + * + *------------------------------------------------------------------------- + */ +#ifndef FILTER_H +#define FILTER_H + +#include "lib/stringinfo.h" + +/* + * State data for reading filter items from stream + */ +typedef struct +{ + FILE *fp; + const char *filename; + int lineno; + StringInfoData linebuff; + bool is_error; +} FilterStateData; + +/* + * List of objects that can be specified in filter file + */ +typedef enum +{ + FILTER_OBJECT_TYPE_NONE, + FILTER_OBJECT_TYPE_TABLE_DATA, + FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN, + FILTER_OBJECT_TYPE_DATABASE, + FILTER_OBJECT_TYPE_FOREIGN_DATA, + FILTER_OBJECT_TYPE_FUNCTION, + FILTER_OBJECT_TYPE_INDEX, + FILTER_OBJECT_TYPE_SCHEMA, + FILTER_OBJECT_TYPE_TABLE, + FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN, + FILTER_OBJECT_TYPE_TRIGGER +} FilterObjectType; + +extern bool filter_init(FilterStateData *fstate, const char *filename); +extern void filter_free(FilterStateData *fstate); + +extern void log_invalid_filter_format(FilterStateData *fstate, char *message); +extern void log_unsupported_filter_object_type(FilterStateData *fstate, + const char *appname, FilterObjectType fot); +extern bool filter_read_item(FilterStateData *fstate, bool *is_include, char **objname, FilterObjectType *objtype); + +#endif diff --git a/src/bin/pg_dump/meson.build b/src/bin/pg_dump/meson.build index b2fb7ac77fd..0a626e6cc6f 100644 --- a/src/bin/pg_dump/meson.build +++ b/src/bin/pg_dump/meson.build @@ -6,6 +6,7 @@ pg_dump_common_sources = files( 'compress_lz4.c', 'compress_none.c', 'dumputils.c', + 'filter.c', 'parallel.c', 'pg_backup_archiver.c', 'pg_backup_custom.c', @@ -97,6 +98,7 @@ tests += { 't/002_pg_dump.pl', 't/003_pg_dump_with_server.pl', 't/004_pg_dump_parallel.pl', + 't/005_pg_dump_filterfile.pl', 't/010_dump_connstr.pl', ], }, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d62780a0880..c7cc4b15736 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -59,6 +59,7 @@ #include "dumputils.h" #include "fe_utils/option_utils.h" #include "fe_utils/string_utils.h" +#include "filter.h" #include "getopt_long.h" #include "libpq/libpq-fs.h" #include "parallel.h" @@ -326,6 +327,7 @@ static char *get_synchronized_snapshot(Archive *fout); static void setupDumpWorker(Archive *AH); static TableInfo *getRootTableInfo(const TableInfo *tbinfo); static bool forcePartitionRootLoad(const TableInfo *tbinfo); +static void read_dump_filters(const char *filename, DumpOptions *dopt); int @@ -404,6 +406,7 @@ main(int argc, char **argv) {"enable-row-security", no_argument, &dopt.enable_row_security, 1}, {"exclude-table-data", required_argument, NULL, 4}, {"extra-float-digits", required_argument, NULL, 8}, + {"filter", required_argument, NULL, 15}, {"if-exists", no_argument, &dopt.if_exists, 1}, {"inserts", no_argument, NULL, 9}, {"lock-wait-timeout", required_argument, NULL, 2}, @@ -656,6 +659,10 @@ main(int argc, char **argv) optarg); break; + case 15: /* object filters from file */ + read_dump_filters(optarg, &dopt); + break; + default: /* getopt_long already emitted a complaint */ pg_log_error_hint("Try \"%s --help\" for more information.", progname); @@ -1102,6 +1109,8 @@ help(const char *progname) " do NOT dump data for the specified table(s),\n" " including child and partition tables\n")); printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n")); + printf(_(" --filter=FILENAME dump objects and data based on the filter expressions\n" + " in specified file\n")); printf(_(" --if-exists use IF EXISTS when dropping objects\n")); printf(_(" --include-foreign-data=PATTERN\n" " include data of foreign tables on foreign\n" @@ -18475,3 +18484,111 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions, if (!res) pg_log_warning("could not parse %s array", "reloptions"); } + +/* + * read_dump_filters - retrieve object identifer patterns from file + * + * Parse the specified filter file for include and exclude patterns, and add + * them to the relevant lists. If the filename is "-" then filters will be + * read from STDIN rather than a file. + */ +static void +read_dump_filters(const char *filename, DumpOptions *dopt) +{ + FilterStateData fstate; + bool is_include; + char *objname; + FilterObjectType objtype; + + if (!filter_init(&fstate, filename)) + exit_nicely(1); + + while (filter_read_item(&fstate, &is_include, &objname, &objtype)) + { + /* ignore comments and empty lines */ + if (objtype == FILTER_OBJECT_TYPE_NONE) + continue; + + if (objtype == FILTER_OBJECT_TYPE_TABLE_DATA) + { + if (is_include) + { + log_invalid_filter_format(&fstate, + "\"include\" table data filter is not allowed"); + break; + } + else + simple_string_list_append(&tabledata_exclude_patterns, + objname); + } + else if (objtype == FILTER_OBJECT_TYPE_TABLE_DATA_AND_CHILDREN) + { + if (is_include) + { + log_invalid_filter_format(&fstate, + "\"include\" table data and children filter is not allowed"); + break; + } + else + simple_string_list_append(&tabledata_exclude_patterns_and_children, + objname); + } + else if (objtype == FILTER_OBJECT_TYPE_FOREIGN_DATA) + { + if (is_include) + simple_string_list_append(&foreign_servers_include_patterns, + objname); + else + { + log_invalid_filter_format(&fstate, + "\"exclude\" foreign data filter is not allowed"); + break; + } + } + else if (objtype == FILTER_OBJECT_TYPE_SCHEMA) + { + if (is_include) + { + simple_string_list_append(&schema_include_patterns, + objname); + dopt->include_everything = false; + } + else + simple_string_list_append(&schema_exclude_patterns, + objname); + } + else if (objtype == FILTER_OBJECT_TYPE_TABLE) + { + if (is_include) + { + simple_string_list_append(&table_include_patterns, objname); + dopt->include_everything = false; + } + else + simple_string_list_append(&table_exclude_patterns, objname); + } + else if (objtype == FILTER_OBJECT_TYPE_TABLE_AND_CHILDREN) + { + if (is_include) + { + simple_string_list_append(&table_include_patterns_and_children, objname); + dopt->include_everything = false; + } + else + simple_string_list_append(&table_exclude_patterns_and_children, objname); + } + else + { + log_unsupported_filter_object_type(&fstate, "pg_dump", objtype); + break; + } + + if (objname) + free(objname); + } + + filter_free(&fstate); + + if (fstate.is_error) + exit_nicely(1); +} diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index cd421c59443..1bfa1c89051 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -26,6 +26,7 @@ #include "common/string.h" #include "dumputils.h" #include "fe_utils/string_utils.h" +#include "filter.h" #include "getopt_long.h" #include "pg_backup.h" @@ -81,6 +82,7 @@ static PGresult *executeQuery(PGconn *conn, const char *query); static void executeCommand(PGconn *conn, const char *query); static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns, SimpleStringList *names); +static void read_dumpall_filters(const char *filename, SimpleStringList *patterns); static char pg_dump_bin[MAXPGPATH]; static const char *progname; @@ -158,6 +160,7 @@ main(int argc, char *argv[]) {"disable-triggers", no_argument, &disable_triggers, 1}, {"exclude-database", required_argument, NULL, 6}, {"extra-float-digits", required_argument, NULL, 5}, + {"filter", required_argument, NULL, 8}, {"if-exists", no_argument, &if_exists, 1}, {"inserts", no_argument, &inserts, 1}, {"lock-wait-timeout", required_argument, NULL, 2}, @@ -360,6 +363,10 @@ main(int argc, char *argv[]) appendShellString(pgdumpopts, optarg); break; + case 8: + read_dumpall_filters(optarg, &database_exclude_patterns); + break; + default: /* getopt_long already emitted a complaint */ pg_log_error_hint("Try \"%s --help\" for more information.", progname); @@ -653,6 +660,7 @@ help(void) printf(_(" --disable-triggers disable triggers during data-only restore\n")); printf(_(" --exclude-database=PATTERN exclude databases whose name matches PATTERN\n")); printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n")); + printf(_(" --filter=FILENAME exclude databases specified in filter file\n")); printf(_(" --if-exists use IF EXISTS when dropping objects\n")); printf(_(" --inserts dump data as INSERT commands, rather than COPY\n")); printf(_(" --load-via-partition-root load partitions via the root table\n")); @@ -1908,7 +1916,6 @@ executeCommand(PGconn *conn, const char *query) PQclear(res); } - /* * dumpTimestamp */ @@ -1932,3 +1939,55 @@ hash_string_pointer(char *s) return hash_bytes(ss, strlen(s)); } + +/* + * read_dumpall_filters - retrieve database identifer patterns from file + * + * Parse the specified filter file for include and exclude patterns, and add + * them to the relevant lists. If the filename is "-" then filters will be + * read from STDIN rather than a file. + * + * In this moment only excluded databases can be filtered. + */ +static void +read_dumpall_filters(const char *filename, SimpleStringList *pattern) +{ + FilterStateData fstate; + bool is_include; + char *objname; + FilterObjectType objtype; + + if (!filter_init(&fstate, filename)) + exit_nicely(1); + + while (filter_read_item(&fstate, &is_include, &objname, &objtype)) + { + if (objtype == FILTER_OBJECT_TYPE_NONE) + continue; + + if (objtype == FILTER_OBJECT_TYPE_DATABASE) + { + if (!is_include) + simple_string_list_append(pattern, objname); + else + { + log_invalid_filter_format(&fstate, + "\"include\" database filter is not allowed"); + break; + } + } + else + { + log_unsupported_filter_object_type(&fstate, "pg_dumpall", objtype); + break; + } + + if (objname) + free(objname); + } + + filter_free(&fstate); + + if (fstate.is_error) + exit_nicely(1); +} diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index 049a1006347..1ebf573ac48 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -47,11 +47,13 @@ #include "dumputils.h" #include "fe_utils/option_utils.h" +#include "filter.h" #include "getopt_long.h" #include "parallel.h" #include "pg_backup_utils.h" static void usage(const char *progname); +static void read_restore_filters(const char *filename, RestoreOptions *dopt); int main(int argc, char **argv) @@ -123,6 +125,7 @@ main(int argc, char **argv) {"no-publications", no_argument, &no_publications, 1}, {"no-security-labels", no_argument, &no_security_labels, 1}, {"no-subscriptions", no_argument, &no_subscriptions, 1}, + {"filter", required_argument, NULL, 4}, {NULL, 0, NULL, 0} }; @@ -286,6 +289,10 @@ main(int argc, char **argv) set_dump_section(optarg, &(opts->dumpSections)); break; + case 4: + read_restore_filters(optarg, opts); + break; + default: /* getopt_long already emitted a complaint */ pg_log_error_hint("Try \"%s --help\" for more information.", progname); @@ -463,6 +470,7 @@ usage(const char *progname) printf(_(" -1, --single-transaction restore as a single transaction\n")); printf(_(" --disable-triggers disable triggers during data-only restore\n")); printf(_(" --enable-row-security enable row security\n")); + printf(_(" --filter=FILE restore objects based on filter expressions\n")); printf(_(" --if-exists use IF EXISTS when dropping objects\n")); printf(_(" --no-comments do not restore comments\n")); printf(_(" --no-data-for-failed-tables do not restore data of tables that could not be\n" @@ -494,3 +502,109 @@ usage(const char *progname) printf(_("Report bugs to <%s>.\n"), PACKAGE_BUGREPORT); printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL); } + +/* + * read_restore_filters - retrieve object identifer patterns from file + * + * Parse the specified filter file for include and exclude patterns, and add + * them to the relevant lists. If the filename is "-" then filters will be + * read from STDIN rather than a file. + */ +static void +read_restore_filters(const char *filename, RestoreOptions *opts) +{ + FilterStateData fstate; + bool is_include; + char *objname; + FilterObjectType objtype; + + if (!filter_init(&fstate, filename)) + exit_nicely(1); + + while (filter_read_item(&fstate, &is_include, &objname, &objtype)) + { + /* ignore comments or empty lines */ + if (objtype == FILTER_OBJECT_TYPE_NONE) + continue; + + if (objtype == FILTER_OBJECT_TYPE_FUNCTION) + { + if (is_include) + { + opts->selTypes = 1; + opts->selFunction = 1; + simple_string_list_append(&opts->functionNames, objname); + } + else + { + log_invalid_filter_format(&fstate, + "\"exclude\" function filter is not allowed"); + break; + } + } + else if (objtype == FILTER_OBJECT_TYPE_INDEX) + { + if (is_include) + { + opts->selTypes = 1; + opts->selIndex = 1; + simple_string_list_append(&opts->indexNames, objname); + } + else + { + log_invalid_filter_format(&fstate, + "\"exclude\" type filter is not allowed"); + break; + } + } + else if (objtype == FILTER_OBJECT_TYPE_SCHEMA) + { + if (is_include) + simple_string_list_append(&opts->schemaNames, objname); + else + simple_string_list_append(&opts->schemaExcludeNames, objname); + } + else if (objtype == FILTER_OBJECT_TYPE_TABLE) + { + if (is_include) + { + opts->selTypes = 1; + opts->selTable = 1; + simple_string_list_append(&opts->tableNames, objname); + } + else + { + log_invalid_filter_format(&fstate, + "\"exclude\" table filter is not allowed"); + break; + } + } + else if (objtype == FILTER_OBJECT_TYPE_TRIGGER) + { + if (is_include) + { + opts->selTypes = 1; + opts->selTrigger = 1; + simple_string_list_append(&opts->triggerNames, objname); + } + else + { + log_invalid_filter_format(&fstate, + "\"exclude\" trigger filter is not allowed"); + break; + } + } + else + { + log_unsupported_filter_object_type(&fstate, "pg_restore", objtype); + break; + } + + if (objname) + free(objname); + } + + filter_free(&fstate); + if (fstate.is_error) + exit_nicely(1); +} diff --git a/src/bin/pg_dump/t/005_pg_dump_filterfile.pl b/src/bin/pg_dump/t/005_pg_dump_filterfile.pl new file mode 100644 index 00000000000..ea1702263a6 --- /dev/null +++ b/src/bin/pg_dump/t/005_pg_dump_filterfile.pl @@ -0,0 +1,701 @@ + +# Copyright (c) 2021, PostgreSQL Global Development Group + +use strict; +use warnings; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More tests => 96; + +my $tempdir = PostgreSQL::Test::Utils::tempdir;; +my $inputfile; + +my $node = PostgreSQL::Test::Cluster->new('main'); +my $port = $node->port; +my $backupdir = $node->backup_dir; +my $plainfile = "$backupdir/plain.sql"; + +$node->init; +$node->start; + +# Generate test objects +$node->safe_psql('postgres', 'CREATE FOREIGN DATA WRAPPER dummy;'); +$node->safe_psql('postgres', + 'CREATE SERVER dummyserver FOREIGN DATA WRAPPER dummy;'); + +$node->safe_psql('postgres', "CREATE TABLE table_one(a varchar)"); +$node->safe_psql('postgres', "CREATE TABLE table_two(a varchar)"); +$node->safe_psql('postgres', "CREATE TABLE table_three(a varchar)"); +$node->safe_psql('postgres', "CREATE TABLE table_three_one(a varchar)"); +$node->safe_psql('postgres', "CREATE TABLE footab(a varchar)"); +$node->safe_psql('postgres', "CREATE TABLE bootab() inherits (footab)"); +$node->safe_psql( + 'postgres', "CREATE TABLE \"strange aaa +name\"(a varchar)"); +$node->safe_psql( + 'postgres', "CREATE TABLE \" +t +t +\"(a int)"); + +$node->safe_psql('postgres', + "INSERT INTO table_one VALUES('*** TABLE ONE ***')"); +$node->safe_psql('postgres', + "INSERT INTO table_two VALUES('*** TABLE TWO ***')"); +$node->safe_psql('postgres', + "INSERT INTO table_three VALUES('*** TABLE THREE ***')"); +$node->safe_psql('postgres', + "INSERT INTO table_three_one VALUES('*** TABLE THREE_ONE ***')"); +$node->safe_psql('postgres', "INSERT INTO bootab VALUES(10)"); + +$node->safe_psql('postgres', "CREATE DATABASE sourcedb"); +$node->safe_psql('postgres', "CREATE DATABASE targetdb"); + +$node->safe_psql('sourcedb', 'CREATE FUNCTION foo1(a int) RETURNS int AS $$ select $1 $$ LANGUAGE sql'); +$node->safe_psql('sourcedb', 'CREATE FUNCTION foo2(a int) RETURNS int AS $$ select $1 $$ LANGUAGE sql'); +$node->safe_psql('sourcedb', 'CREATE FUNCTION foo3(a double precision, b int) RETURNS double precision AS $$ select $1 + $2 $$ LANGUAGE sql'); +$node->safe_psql('sourcedb', 'CREATE FUNCTION foo_trg() RETURNS trigger AS $$ BEGIN RETURN NEW; END $$ LANGUAGE plpgsql'); +$node->safe_psql('sourcedb', 'CREATE SCHEMA s1'); +$node->safe_psql('sourcedb', 'CREATE SCHEMA s2'); +$node->safe_psql('sourcedb', 'CREATE TABLE s1.t1(a int)'); +$node->safe_psql('sourcedb', 'CREATE SEQUENCE s1.s1'); +$node->safe_psql('sourcedb', 'CREATE TABLE s2.t2(a int)'); +$node->safe_psql('sourcedb', 'CREATE TABLE t1(a int, b int)'); +$node->safe_psql('sourcedb', 'CREATE TABLE t2(a int, b int)'); +$node->safe_psql('sourcedb', 'CREATE INDEX t1_idx1 ON t1(a)'); +$node->safe_psql('sourcedb', 'CREATE INDEX t1_idx2 ON t1(b)'); +$node->safe_psql('sourcedb', 'CREATE TRIGGER trg1 BEFORE INSERT ON t1 EXECUTE FUNCTION foo_trg()'); +$node->safe_psql('sourcedb', 'CREATE TRIGGER trg2 BEFORE INSERT ON t1 EXECUTE FUNCTION foo_trg()'); + +# +# Test interaction of correctly specified filter file +# +my ($cmd, $stdout, $stderr, $result); + +# Empty filterfile +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "\n # a comment and nothing more\n\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "filter file without patterns"); + +my $dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "table one dumped"); +ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "table two dumped"); +ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "table three dumped"); +ok($dump =~ qr/^CREATE TABLE public\.table_three_one/m, "table three one dumped"); + +# Test various combinations of whitespace, comments and correct filters +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile " include table table_one #comment\n"; +print $inputfile "include table table_two\n"; +print $inputfile "# skip this line\n"; +print $inputfile "\n"; +print $inputfile "\t\n"; +print $inputfile " \t# another comment\n"; +print $inputfile "exclude table_data table_one\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with filter patterns as well as comments and whitespace"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "dumped table one"); +ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "dumped table two"); +ok($dump !~ qr/^CREATE TABLE public\.table_three/m, "table three not dumped"); +ok($dump !~ qr/^CREATE TABLE public\.table_three_one/m, + "table three_one not dumped"); +ok( $dump !~ qr/^COPY public\.table_one/m, + "content of table one is not included"); +ok($dump =~ qr/^COPY public\.table_two/m, "content of table two is included"); + +# Test dumping tables specified by qualified names +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table public.table_one\n"; +print $inputfile "include table \"public\".\"table_two\"\n"; +print $inputfile "include table \"public\". table_three\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "filter file without patterns"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "dumped table one"); +ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "dumped table two"); +ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "dumped table three"); + +# Test dumping all tables except one +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude table table_one\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with exclusion of a single table"); + +$dump = slurp_file($plainfile); + +ok($dump !~ qr/^CREATE TABLE public\.table_one/m, "table one not dumped"); +ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "dumped table two"); +ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "dumped table three"); +ok($dump =~ qr/^CREATE TABLE public\.table_three_one/m, + "dumped table three_one"); + +# Test dumping tables with a wildcard pattern +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table table_thre*\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with wildcard in pattern"); + +$dump = slurp_file($plainfile); + +ok($dump !~ qr/^CREATE TABLE public\.table_one/m, "table one not dumped"); +ok($dump !~ qr/^CREATE TABLE public\.table_two/m, "table two not dumped"); +ok($dump =~ qr/^CREATE TABLE public\.table_three/m, "dumped table three"); +ok($dump =~ qr/^CREATE TABLE public\.table_three_one/m, + "dumped table three_one"); + +# Test dumping table with multiline quoted tablename +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table \"strange aaa +name\""; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with multiline names requiring quoting"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public.\"strange aaa/m, + "dump table with new line in name"); + +# Test excluding multiline quoted tablename from dump +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude table \"strange aaa\\nname\""; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with filter"); + +$dump = slurp_file($plainfile); + +ok($dump !~ qr/^CREATE TABLE public.\"strange aaa/m, + "dump table with new line in name"); + +# Test excluding an entire schema +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude schema public\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "exclude the public schema"); + +$dump = slurp_file($plainfile); + +ok($dump !~ qr/^CREATE TABLE/m, "no table dumped"); + +# Test including and excluding an entire schema by multiple filterfiles +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include schema public\n"; +close $inputfile; + +open my $alt_inputfile, '>', "$tempdir/inputfile2.txt" + or die "unable to open filterfile for writing"; +print $alt_inputfile "exclude schema public\n"; +close $alt_inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "--filter=$tempdir/inputfile2.txt", 'postgres' + ], + "exclude the public schema with multiple filters"); + +$dump = slurp_file($plainfile); + +ok($dump !~ qr/^CREATE TABLE/m, "no table dumped"); + +# Test dumping a table with a single leading newline on a row +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table \" +t +t +\""; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public.\"\nt\nt\n\" \($/ms, + "dump table with multiline strange name"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table \"\\nt\\nt\\n\""; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump tables with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public.\"\nt\nt\n\" \($/ms, + "dump table with multiline strange name"); + +######################################### +# Test foreign_data + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include foreign_data doesnt_exists\n"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + qr/pg_dump: error: no matching foreign servers were found for pattern/, + "dump nonexisting foreign server"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile, "include foreign_data dummyserver\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "dump foreign_data with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE SERVER dummyserver/m, "dump foreign server"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude foreign_data dummy*\n"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + qr/"exclude" foreign data filter is not allowed/, + "erroneously exclude foreign server"); + +######################################### +# Test broken input format + +# Test invalid filter command +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "k"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + qr/invalid filter command/, + "invalid syntax: incorrect filter command"); + +# Test invalid object type +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include xxx"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + qr/unsupported filter object type: "xxx"/, + "invalid syntax: invalid object type specified, should be table, schema, foreign_data or data" +); + +# Test missing object identifier pattern +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + qr/missing object name/, + "invalid syntax: missing object identifier pattern"); + +# Test adding extra content after the object identifier pattern +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table table one"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + qr/no matching tables were found/, + "invalid syntax: extra content after object identifier pattern"); + +######################################### +# Combined with --strict-names + +# First ensure that a matching filter works +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table table_one\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + '--strict-names', 'postgres' + ], + "strict names with matching mattern"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.table_one/m, "no table dumped"); + +# Now append a pattern to the filter file which doesn't resolve +open $inputfile, '>>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table table_nonexisting_name"; +close $inputfile; + +command_fails_like( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + '--strict-names', 'postgres' + ], + qr/no matching tables were found/, + "inclusion of non-existing objects with --strict names"); + +######################################### +# pg_dumpall tests + +########################### +# Test dumping all tables except one +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude database postgres\n"; +close $inputfile; + +command_ok( + [ + 'pg_dumpall', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt" + ], + "dump tables with exclusion of a database"); + +$dump = slurp_file($plainfile); + +ok($dump !~ qr/^\\connect postgres/m, "database postgres is not dumped"); +ok($dump =~ qr/^\\connect template1/m, "database template1 is dumped"); + +# Test invalid filter command +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "k"; +close $inputfile; + +command_fails_like( + [ + 'pg_dumpall', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt" + ], + qr/invalid filter command/, + "invalid syntax: incorrect filter command"); + +# Test invalid object type +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude xxx"; +close $inputfile; + +command_fails_like( + [ + 'pg_dumpall', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt" + ], + qr/unsupported filter object type: "xxx"/, + "invalid syntax: exclusion of non-existing object type" +); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude table foo"; +close $inputfile; + +command_fails_like( + [ + 'pg_dumpall', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt" + ], + qr/"pg_dumpall" doesn't support filter for object type "table"/, + "invalid syntax: exclusion of unsupported object type" +); + +######################################### +# pg_restore tests + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', "$tempdir/filter_test.dump", + "-Fc", 'postgres' + ], + "dump all tables"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include table table_two"; +close $inputfile; + +command_ok( + [ + 'pg_restore', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "-Fc", "$tempdir/filter_test.dump" + ], + "restore tables with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.table_two/m, "wanted table restored"); +ok($dump !~ qr/^CREATE TABLE public\.table_one/m, "unwanted table is not restored"); + +######################################### +# test restore of other objects + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', "$tempdir/filter_test.dump", + "-Fc", 'sourcedb' + ], + "dump all objects from sourcedb"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include function foo1(integer)"; +close $inputfile; + +command_ok( + [ + 'pg_restore', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "-Fc", "$tempdir/filter_test.dump" + ], + "restore function with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE FUNCTION public\.foo1/m, "wanted function restored"); +ok($dump !~ qr/^CREATE TABLE public\.foo2/m, "unwanted function is not restored"); + +# this should be white space tolerant (against the -P argument) + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include function foo3 ( double precision , integer) "; +close $inputfile; + +command_ok( + [ + 'pg_restore', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "-Fc", "$tempdir/filter_test.dump" + ], + "restore function with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE FUNCTION public\.foo3/m, "wanted function restored"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include index t1_idx1\n"; + +# attention! this hit pg_restore bug - correct name of trigger is "trg1" +# not "t1 trg1". Should be fixed when pg_restore will be fixed +print $inputfile "include trigger t1 trg1\n"; +close $inputfile; + +command_ok( + [ + 'pg_restore', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "-Fc", "$tempdir/filter_test.dump" + ], + "restore function with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE INDEX t1_idx1/m, "wanted index restored"); +ok($dump !~ qr/^CREATE INDEX t2_idx2/m, "unwanted index are not restored"); +ok($dump =~ qr/^CREATE TRIGGER trg1/m, "wanted trigger restored"); +ok($dump !~ qr/^CREATE TRIGGER trg2/m, "unwanted trigger is not restored"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "include schema s1\n"; +close $inputfile; + +command_ok( + [ + 'pg_restore', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "-Fc", "$tempdir/filter_test.dump" + ], + "restore function with filter"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE s1\.t1/m, "wanted table from schema restored"); +ok($dump =~ qr/^CREATE SEQUENCE s1\.s1/m, "wanted sequence from schema restored"); +ok($dump !~ qr/^CREATE TABLE s2\t2/m, "unwanted table is not restored"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; +print $inputfile "exclude schema s1\n"; +close $inputfile; + +command_ok( + [ + 'pg_restore', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", + "-Fc", "$tempdir/filter_test.dump" + ], + "restore function with filter"); + +$dump = slurp_file($plainfile); + +ok($dump !=~ qr/^CREATE TABLE s1\.t1/m, "unwanted table from schema is not restored"); +ok($dump !=~ qr/^CREATE SEQUENCE s1\.s1/m, "unwanted sequence from schema is not restored"); +ok($dump =~ qr/^CREATE TABLE s2\.t2/m, "wanted table restored"); +ok($dump =~ qr/^CREATE TABLE public\.t1/m, "wanted table restored"); + +######################################### +# test of supported syntax + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; + +print $inputfile "include table_and_children footab\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "filter file without patterns"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.bootab/m, "dumped children table"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; + +print $inputfile "exclude table_and_children footab\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "filter file without patterns"); + +$dump = slurp_file($plainfile); + +ok($dump !=~ qr/^CREATE TABLE public\.bootab/m, "exclude dumped children table"); + +open $inputfile, '>', "$tempdir/inputfile.txt" + or die "unable to open filterfile for writing"; + +print $inputfile "exclude table_data_and_children footab\n"; +close $inputfile; + +command_ok( + [ + 'pg_dump', '-p', $port, '-f', $plainfile, + "--filter=$tempdir/inputfile.txt", 'postgres' + ], + "filter file without patterns"); + +$dump = slurp_file($plainfile); + +ok($dump =~ qr/^CREATE TABLE public\.bootab/m, "dumped children table"); +ok($dump !=~ qr/^COPY public\.bootab/m, "exclude dumped children table"); diff --git a/src/tools/msvc/Mkvcbuild.pm b/src/tools/msvc/Mkvcbuild.pm index 0c89c8e5297..0acd9fc89e9 100644 --- a/src/tools/msvc/Mkvcbuild.pm +++ b/src/tools/msvc/Mkvcbuild.pm @@ -437,6 +437,7 @@ sub mkvcbuild $pgdumpall->AddIncludeDir('src/backend'); $pgdumpall->AddFile('src/bin/pg_dump/pg_dumpall.c'); $pgdumpall->AddFile('src/bin/pg_dump/dumputils.c'); + $pgdumpall->AddFile('src/bin/pg_dump/filter.c'); $pgdumpall->AddLibrary('ws2_32.lib'); my $pgrestore = AddSimpleFrontend('pg_dump', 1); -- 2.34.1