From 052f234e53626ca8906a972c89a1b55f67e22beb Mon Sep 17 00:00:00 2001 From: Daniel Gustafsson Date: Fri, 1 Oct 2021 15:03:15 +0200 Subject: [PATCH] Add --filter option for reading object patterns from file This adds the --filter=FILENAME option to pg_dump for specifying a set of inclusion/exclusion patterns in a file. In situations where the wanted filterset exceeds the commandline capabilities, this can be a handy way to still be able to filter objects. The format of the file is line based with each pattern on its own line: When object names contain whitespace the pattern must be quoted. The object_types table, schema, foreign_data and data each correspond to the existing command line parameters -t/--table, -n/--schema, --include-foreign-data and --exclude-table-data. Author: Pavel Stehule Discussion: https://postgr.es/m/CAFj8pRB10wvW0CC9Xq=1XDs=zCQxer3cbLcNZa+qiX4cUH-G_A@mail.gmail.com fixup --- doc/src/sgml/ref/pg_dump.sgml | 88 ++++ src/bin/pg_dump/pg_dump.c | 419 ++++++++++++++++++++ src/bin/pg_dump/t/004_pg_dump_filterfile.pl | 399 +++++++++++++++++++ 3 files changed, 906 insertions(+) create mode 100644 src/bin/pg_dump/t/004_pg_dump_filterfile.pl diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 7682226b99..1c60f24b1d 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -789,6 +789,80 @@ 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: + / 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 | schema | foreign_data | data } 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 + / + + + + + schema: schemas, works like + / + + + + + foreign_data: data on foreign servers, works like + . This keyword can only be + used with the include keyword. + + + + + data: table data, works like + . This keyword can only be + with the exclude keyword. + + + + + + + Lines starting with # are considered comments and + are 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. + + + + + @@ -1122,6 +1196,7 @@ PostgreSQL documentation schema (/) and table (/) qualifier 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 qualifiers find matches, pg_dump will generate an error even without . @@ -1531,6 +1606,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/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index a485fb2d07..796c431aaa 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -55,10 +55,12 @@ #include "catalog/pg_trigger_d.h" #include "catalog/pg_type_d.h" #include "common/connect.h" +#include "common/string.h" #include "dumputils.h" #include "fe_utils/option_utils.h" #include "fe_utils/string_utils.h" #include "getopt_long.h" +#include "lib/stringinfo.h" #include "libpq/libpq-fs.h" #include "parallel.h" #include "pg_backup_db.h" @@ -90,6 +92,28 @@ typedef enum OidOptions zeroAsNone = 4 } OidOptions; +/* + * State data for reading filter items from stream + */ +typedef struct +{ + FILE *fp; + const char *filename; + int lineno; +} FilterStateData; + +/* + * List of objects that can be specified in filter file + */ +typedef enum +{ + FILTER_OBJECT_TYPE_NONE, + FILTER_OBJECT_TYPE_TABLE, + FILTER_OBJECT_TYPE_SCHEMA, + FILTER_OBJECT_TYPE_FOREIGN_DATA, + FILTER_OBJECT_TYPE_DATA +} FilterObjectType; + /* global decls */ static bool dosync = true; /* Issue fsync() to make dump durable on disk. */ @@ -308,6 +332,7 @@ static void appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions, static char *get_synchronized_snapshot(Archive *fout); static void setupDumpWorker(Archive *AHX); static TableInfo *getRootTableInfo(const TableInfo *tbinfo); +static void getFiltersFromFile(const char *filename, DumpOptions *dopt); int @@ -380,6 +405,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, 12}, {"if-exists", no_argument, &dopt.if_exists, 1}, {"inserts", no_argument, NULL, 9}, {"lock-wait-timeout", required_argument, NULL, 2}, @@ -613,6 +639,10 @@ main(int argc, char **argv) optarg); break; + case 12: /* object filters from file */ + getFiltersFromFile(optarg, &dopt); + break; + default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit_nicely(1); @@ -1038,6 +1068,8 @@ help(const char *progname) " access to)\n")); printf(_(" --exclude-table-data=PATTERN do NOT dump data for the specified table(s)\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" @@ -18979,3 +19011,390 @@ appendReloptionsArrayAH(PQExpBuffer buffer, const char *reloptions, if (!res) pg_log_warning("could not parse reloptions array"); } + +/* + * exit_invalid_filter_format - Emit error message, close the file and exit + * + * This is mostly a convenience routine to avoid duplicating file closing code + * in multiple callsites. + */ +static void +exit_invalid_filter_format(FilterStateData *fstate, char *message) +{ + if (fstate->fp != stdin) + { + pg_log_error("invalid format of filter file \"%s\" on line %d: %s", + fstate->filename, + fstate->lineno, + message); + + if (fclose(fstate->fp) != 0) + fatal("could not close filter file \"%s\": %m", fstate->filename); + } + else + pg_log_error("invalid format of filter on line %d: %s", + fstate->lineno, + message); + + exit_nicely(1); +} + +/* + * filter_get_keyword - read the next filter keyword from buffer + * + * Search for keywords (limited to containing ascii alphabetic characters) in + * the passed in line buffer. Returns NULL, when the buffer is empty or first + * char is not alpha. 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 (isascii(*ptr) && isalpha(*ptr)) + { + result = ptr++; + + while (isascii(*ptr) && (isalpha(*ptr) || *ptr == '_')) + ptr++; + + *size = ptr - result; + } + + *line = ptr; + + return result; +} + +/* + * filter_get_pattern - Read an object identifier pattern from the buffer + * + * Parses an object identifier pattern from the passed in buffer and sets + * objname to a string with object identifier pattern. Returns pointer to the + * first character after the pattern. + */ +static char * +filter_get_pattern(FilterStateData *fstate, + char *str, + char **objname) +{ + StringInfoData line; + + /* We only allocate the buffer if we need it */ + line.data = NULL; + + /* Skip whitespace */ + while (isspace(*str)) + str++; + + if (*str == '\0') + exit_invalid_filter_format(fstate, "missing object name pattern"); + + /* + * If the object name pattern has been quoted we must take care parse out + * the entire quoted pattern, which may contain whitespace and can span + * over many lines. + */ + if (*str == '"') + { + PQExpBuffer quoted_name = createPQExpBuffer(); + + appendPQExpBufferChar(quoted_name, '"'); + str++; + + while (1) + { + if (*str == '\0') + { + if (line.data == NULL) + initStringInfo(&line); + + if (!pg_get_line_buf(fstate->fp, &line)) + { + if (ferror(fstate->fp)) + { + pg_log_error("could not read from filter file \"%s\": %m", + fstate->filename); + if (fstate->fp != stdin) + { + if (fclose(fstate->fp) != 0) + fatal("could not close filter file \"%s\": %m", + fstate->filename); + } + + exit_nicely(1); + } + + exit_invalid_filter_format(fstate, "unexpected end of file"); + } + + str = line.data; + (void) pg_strip_crlf(str); + + appendPQExpBufferChar(quoted_name, '\n'); + fstate->lineno++; + } + + if (*str == '"') + { + appendPQExpBufferChar(quoted_name, '"'); + str++; + + if (*str == '"') + { + appendPQExpBufferChar(quoted_name, '"'); + str++; + } + else + break; + } + else if (*str == '\\') + { + str++; + if (*str == 'n') + appendPQExpBufferChar(quoted_name, '\n'); + else if (*str == '\\') + appendPQExpBufferChar(quoted_name, '\\'); + + str++; + } + else + appendPQExpBufferChar(quoted_name, *str++); + } + + *objname = pg_strdup(quoted_name->data); + destroyPQExpBuffer(quoted_name); + } + else + { + char *startptr = str++; + + /* Simple variant, read to EOL or to first whitespace */ + while (*str && !isspace(*str)) + str++; + + *objname = pnstrdup(startptr, str - startptr); + } + + if (line.data != NULL) + pg_free(line.data); + + return str; +} + +/* + * read_filter_item - Read command/type/pattern triplet from 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: + * + * + * + * Where command is "include" or "exclude", and object_type is one of: "table", + * "schema", "foreign_data" or "data". The pattern is either simple without any + * whitespace, or properly quoted in case there is whitespace in the object + * name. The pattern handling follows the same rules as other object include + * and exclude functions; it can 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 errors, the function wont return + * but will exit with an appropriate error message. + */ +static bool +read_filter_item(FilterStateData *fstate, + bool *is_include, + char **objname, + FilterObjectType *objtype) +{ + StringInfoData line; + + initStringInfo(&line); + + if (pg_get_line_buf(fstate->fp, &line)) + { + char *str = line.data; + const char *keyword; + int size; + + fstate->lineno++; + + (void) pg_strip_crlf(str); + + /* 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((const char **) &str, &size); + if (!keyword) + exit_invalid_filter_format(fstate, + "no filtercommand found (expected \"include\" or \"exclude\")"); + + if (size == 7 && pg_strncasecmp(keyword, "include", 7) == 0) + *is_include = true; + else if (size == 7 && pg_strncasecmp(keyword, "exclude", 7) == 0) + *is_include = false; + else + exit_invalid_filter_format(fstate, + "invalid filtercommand (expected \"include\" or \"exclude\")"); + + keyword = filter_get_keyword((const char **) &str, &size); + if (!keyword) + exit_invalid_filter_format(fstate, + "no object type found (expected \"table\", \"schema\", \"foreign_data\" or \"data\")"); + + if (size == 5 && pg_strncasecmp(keyword, "table", 5) == 0) + *objtype = FILTER_OBJECT_TYPE_TABLE; + else if (size == 6 && pg_strncasecmp(keyword, "schema", 6) == 0) + *objtype = FILTER_OBJECT_TYPE_SCHEMA; + else if (size == 12 && pg_strncasecmp(keyword, "foreign_data", 12) == 0) + *objtype = FILTER_OBJECT_TYPE_FOREIGN_DATA; + else if (size == 4 && pg_strncasecmp(keyword, "data", 4) == 0) + *objtype = FILTER_OBJECT_TYPE_DATA; + else + exit_invalid_filter_format(fstate, + "invalid object type (expected \"table\", \"schema\", \"foreign_data\" or \"data\")"); + + str = filter_get_pattern(fstate, str, objname); + + /* + * Look for any content after the object identifier. Comments and + * whitespace are allowed, other content may indicate that the + * user needed to quote the object name so exit with an invalid + * format error. + */ + while (isspace(*str)) + str++; + + if (*str != '\0' && *str != '#') + exit_invalid_filter_format(fstate, + "unexpected extra data after pattern"); + } + else + { + *objname = NULL; + *objtype = FILTER_OBJECT_TYPE_NONE; + } + + free(line.data); + + return true; + } + + if (ferror(fstate->fp)) + { + pg_log_error("could not read from filter file \"%s\": %m", fstate->filename); + + if (fstate->fp != stdin) + { + if (fclose(fstate->fp) != 0) + fatal("could not close filter file \"%s\": %m", fstate->filename); + } + + exit_nicely(1); + } + + free(line.data); + + return false; +} + +/* + * getFiltersFromFile - 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 +getFiltersFromFile(const char *filename, DumpOptions *dopt) +{ + FilterStateData fstate; + bool is_include; + char *objname; + FilterObjectType objtype; + + fstate.filename = filename; + fstate.lineno = 0; + + if (strcmp(filename, "-") != 0) + { + fstate.fp = fopen(filename, "r"); + if (!fstate.fp) + fatal("could not open filter file \"%s\": %m", filename); + } + else + fstate.fp = stdin; + + while (read_filter_item(&fstate, &is_include, &objname, &objtype)) + { + 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_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_DATA) + { + if (is_include) + exit_invalid_filter_format(&fstate, + "include filter is not allowed for this type of object"); + else + simple_string_list_append(&tabledata_exclude_patterns, + objname); + } + else if (objtype == FILTER_OBJECT_TYPE_FOREIGN_DATA) + { + if (is_include) + simple_string_list_append(&foreign_servers_include_patterns, + objname); + else + exit_invalid_filter_format(&fstate, + "exclude filter is not allowed for this type of object"); + } + + if (objname) + free(objname); + } + + if (fstate.fp != stdin) + { + if (fclose(fstate.fp) != 0) + fatal("could not close filter file \"%s\": %m", fstate.filename); + } +} diff --git a/src/bin/pg_dump/t/004_pg_dump_filterfile.pl b/src/bin/pg_dump/t/004_pg_dump_filterfile.pl new file mode 100644 index 0000000000..28ae6f1db8 --- /dev/null +++ b/src/bin/pg_dump/t/004_pg_dump_filterfile.pl @@ -0,0 +1,399 @@ + +# Copyright (c) 2021, PostgreSQL Global Development Group + +use strict; +use warnings; + +use Config; +use PostgresNode; +use TestLib; +use Test::More tests => 49; + +my $tempdir = TestLib::tempdir; +my $inputfile; + +my $node = PostgresNode->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 \"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 ***')"); + +# +# 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|two|three|three_one)/m, + "tables 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 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 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 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 filtercommand/, + "invalid syntax: incorrect filtercommand"); + +# 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/invalid object type/, + "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/unexpected extra data/, + "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"); -- 2.24.3 (Apple Git-128)