Here's the latest pg_dump patch I've been (too sporadically) working on. I abandoned building linked lists and decided to make the backend do all the work, from building the list of good relations, to doing the POSIX regex matching. I've added numerous examples to the docs, but it may still need some more explaining. It should be nearly 100% backwards compatible with any existing scripts that use a single -t as well.
-- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200607162215 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
Index: doc/TODO =================================================================== RCS file: /projects/cvsroot/pgsql/doc/TODO,v retrieving revision 1.1914 diff -u -r1.1914 TODO --- doc/TODO 10 Jul 2006 15:44:31 -0000 1.1914 +++ doc/TODO 17 Jul 2006 02:10:18 -0000 @@ -795,7 +795,7 @@ * pg_dump - o %Allow pg_dump to use multiple -t and -n switches [pg_dump] + o -Allow pg_dump to use multiple -t and -n switches (Greg) http://archives.postgresql.org/pgsql-patches/2006-01/msg00190.php Index: doc/src/sgml/ref/pg_dump.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.86 diff -u -r1.86 pg_dump.sgml --- doc/src/sgml/ref/pg_dump.sgml 13 May 2006 17:10:35 -0000 1.86 +++ doc/src/sgml/ref/pg_dump.sgml 17 Jul 2006 02:10:18 -0000 @@ -398,18 +398,66 @@ <listitem> <para> Dump data for <replaceable class="parameter">table</replaceable> - 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. Specify both - <option>--schema</> and <option>--table</> to select just one 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. The argument can also contain POSIX regular expressions + which may match more than one table. Using a "+" sign at the end of the + name will force a regular expression search. For example, + <option>-t employee</> will dump only tables named exactly "employee", + while <option>-t employee+</> will dump all tables with the letters + "employee" inside of it. </para> + <para> + The options <option>-t</>, <option>-T</>, <option>-n</>, and <option>-N</> + can be used together to achieve a high degree of control over what will get + dumped. Multiple arguments can be used, and are parsed in the order they are + given to build a list of vaid tables and schemas. The schema options are + parsed first to create a list of schemas to dump, and then the table options + are parsed to only find tables in the matching schemas. + </para> + + <title>Examples</title> + + <para>To dump a single table named <literal>pg_class</>: + +<screen> +<prompt>$</prompt> <userinput>pg_dump -t pg_class mydb > db.out</userinput> +</screen> + </para> + + <para>To dump all tables starting with <literal>employee</l> in the + <literal>detroit</> schema, except for the table named <literal>employee_log</literal>: + +<screen> +<prompt>$</prompt> <userinput>pg_dump -n detroit -t ^employee -T employee_log mydb > db.out</userinput> +</screen> + </para> + + <para>To dump all schemas starting with <literal>east</> or <literal>west</> and ending in + <literal>gsm</>, but not schemas that contain the letters <literal>test</>, except for + one named <literal>east_alpha_test_five</l>: + +<screen> +<prompt>$</prompt> <userinput>pg_dump -n "^(east|west).*gsm$" -N test -n east_alpha_test_five mydb > db.out</userinput> +</screen> + </para> + + + <para>To dump all tables except for those beginning with <literal>ts_</literal>: + +<screen> +<prompt>$</prompt> <userinput>pg_dump -T "^ts_" mydb > db.out</userinput> +</screen> + </para> + + <note> <para> In this mode, <application>pg_dump</application> makes no - attempt to dump any other database objects that the selected table + attempt to dump any other database objects that the selected tables may depend upon. Therefore, there is no guarantee - that the results of a single-table dump can be successfully + that the results of a specific-table dump can be successfully restored by themselves into a clean database. </para> </note> @@ -417,6 +465,46 @@ </varlistentry> <varlistentry> + <term><option>-T <replaceable class="parameter">table</replaceable></option></term> + <term><option>--exclude-table=<replaceable class="parameter">table</replaceable></option></term> + <listitem> + <para> + Do not dump any matching <replaceable class="parameter">tables</replaceable>. + More than one option may be used, and POSIX regular expressions are allowed. + See <option>-t</> for examples. + </para> + + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-n <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Dump only the matching <replaceable class="parameter">schemas</replaceable>. + More than one option may be used, and POSIX regular expressions are allowed. + See <option>-t</> for examples. + </para> + + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-N <replaceable class="parameter">schema</replaceable></option></term> + <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Do not dump the matching <replaceable class="parameter">schemas</replaceable>. + More than one option may be used, and POSIX regular expressions are allowed. + See <option>-t</> for examples. + </para> + + </listitem> + </varlistentry> + + <varlistentry> <term><option>-v</></term> <term><option>--verbose</></term> <listitem> Index: src/bin/pg_dump/common.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/common.c,v retrieving revision 1.91 diff -u -r1.91 common.c --- src/bin/pg_dump/common.c 14 Jul 2006 14:52:26 -0000 1.91 +++ src/bin/pg_dump/common.c 17 Jul 2006 02:10:18 -0000 @@ -72,9 +72,7 @@ * Collect information about all potentially dumpable objects */ TableInfo * -getSchemaData(int *numTablesPtr, - const bool schemaOnly, - const bool dataOnly) +getSchemaData(int *numTablesPtr) { NamespaceInfo *nsinfo; AggInfo *agginfo; Index: src/bin/pg_dump/pg_dump.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.441 diff -u -r1.441 pg_dump.c --- src/bin/pg_dump/pg_dump.c 14 Jul 2006 14:52:26 -0000 1.441 +++ src/bin/pg_dump/pg_dump.c 17 Jul 2006 02:10:19 -0000 @@ -91,8 +91,19 @@ /* obsolete as of 7.3: */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ -static char *selectTableName = NULL; /* name of a single table to dump */ -static char *selectSchemaName = NULL; /* name of a single schema to dump */ +/* select and exclude tables and schemas */ +struct _relnameArg { + char *name; /* name of the relation */ + bool action; /* are we including (true) or excluding (false)? */ + struct _relnameArg *next; +}; +typedef struct _relnameArg relnameArg; + +relnameArg *schemaList = NULL; /* List of schemas to include/exclude */ +relnameArg *tableList = NULL; /* List of tables to include/exclude */ + +char *goodSchemas = NULL; /* Final list of schemas to dump by oid */ +char *goodTables = NULL; /* Final list of tables to dump by oid */ char g_opaque_type[10]; /* name for the opaque type */ @@ -180,6 +191,11 @@ int main(int argc, char **argv) { + relnameArg *RelationList = NULL; + PQExpBuffer query = createPQExpBuffer(); + PGresult *res; + relnameArg *newrelname; + relnameArg *thisrelname = NULL; int c; const char *filename = NULL; const char *format = "p"; @@ -194,7 +210,7 @@ int numTables; DumpableObject **dobjs; int numObjs; - int i; + int i,j,needwhere; bool force_password = false; int compressLevel = -1; bool ignore_version = false; @@ -226,9 +242,11 @@ {"no-owner", no_argument, NULL, 'O'}, {"port", required_argument, NULL, 'p'}, {"schema", required_argument, NULL, 'n'}, + {"exclude-schema", required_argument, NULL, 'N'}, {"schema-only", no_argument, NULL, 's'}, {"superuser", required_argument, NULL, 'S'}, {"table", required_argument, NULL, 't'}, + {"exclude-table", required_argument, NULL, 'T'}, {"password", no_argument, NULL, 'W'}, {"username", required_argument, NULL, 'U'}, {"verbose", no_argument, NULL, 'v'}, @@ -281,7 +299,7 @@ } } - while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:", + while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:N:oOp:RsS:t:T:uU:vWxX:Z:", long_options, &optindex)) != -1) { switch (c) @@ -332,8 +350,34 @@ ignore_version = true; break; - case 'n': /* Dump data for this schema only */ - selectSchemaName = strdup(optarg); + case 'n': /* Specifically include matching schemas */ + case 'N': /* Specifically exclude matching schemas */ + case 't': /* Specifically include matching tables */ + case 'T': /* Specifically exclude matching tables */ + + if (strlen(optarg) < 1) { + fprintf(stderr, + _("%s: invalid -%c option\n"), + progname, c); + exit(1); + } + /* Create a struct for this name */ + newrelname = (relnameArg *) malloc (sizeof(relnameArg)); + newrelname->next = NULL; + newrelname->name = strdup(optarg); + newrelname->action = (c=='n' || c=='t') ? true : false; + RelationList = (c=='t' || c=='T') ? tableList : schemaList; + if (RelationList == NULL) { + if (c=='t' || c=='T') + tableList = thisrelname = newrelname; + else + schemaList = thisrelname = newrelname; + } + else { + thisrelname->next = newrelname; + thisrelname = newrelname; + } + break; case 'o': /* Dump oids */ @@ -361,10 +405,6 @@ outputSuperuser = strdup(optarg); break; - case 't': /* Dump data for this table only */ - selectTableName = strdup(optarg); - break; - case 'u': force_password = true; username = simple_prompt("User name: ", 100, true); @@ -449,7 +489,7 @@ exit(1); } - if (selectTableName != NULL || selectSchemaName != NULL) + if (goodTables != NULL || goodSchemas != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) @@ -568,11 +608,135 @@ write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } + + if (schemaList != NULL && g_fout->remoteVersion < 70300) + { + write_msg(NULL, "Postgres must be at least version 7.3 to use schema switches\n"); + exit_nicely(); + } + + resetPQExpBuffer(query); + needwhere = 2; + /* We are going to make the database do all the hard work here */ + for (thisrelname = schemaList; thisrelname; thisrelname = thisrelname->next) { + + if (needwhere) { + /* Special case for when -N is the first argument */ + if (thisrelname == RelationList && !thisrelname->action) + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace EXCEPT\n"); + + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_namespace WHERE"); + } + + appendPQExpBuffer(query, "%s nspname %c ", + needwhere ? "" : " OR", + strpbrk(thisrelname->name,"([{\\.+?")==NULL ? '=' : '~' + ); + appendStringLiteralAH(query, thisrelname->name, g_fout); + + if (thisrelname->next && thisrelname->next->action == thisrelname->action) { + needwhere = 0; + continue; + } + needwhere = 1; + + /* Add the joiner if needed */ + if (thisrelname->next) { + appendPQExpBuffer(query, "\n%s\n", + thisrelname->next->action ? "UNION" : "EXCEPT"); + } + + } + + if (needwhere != 2) { + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + if (PQntuples(res) == 0) + { + write_msg(NULL, "No matching schemas were found\n"); + exit_nicely(); + } + j = 2; + for (i = 0; i < PQntuples(res); i++) { + j += 1+strlen(PQgetvalue(res, i, 0)); + } + goodSchemas = malloc(j); + strcpy(goodSchemas, "0,"); + for (i = 0; i < PQntuples(res); i++) { + strcat(goodSchemas, PQgetvalue(res, i, 0)); + strcat(goodSchemas, ","); + } + strcat(goodSchemas, "0"); + } + + resetPQExpBuffer(query); + needwhere = 2; + for (thisrelname = tableList; thisrelname; thisrelname = thisrelname->next) { + + if (needwhere) { + /* Special case for when -T is the first argument */ + if (thisrelname == RelationList && !thisrelname->action && !strlen(query->data)) + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' EXCEPT\n"); + + appendPQExpBuffer(query, "SELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND ("); + } + + appendPQExpBuffer(query, "%srelname %c ", + needwhere ? "" : " OR ", + strpbrk(thisrelname->name,"([{\\.+?")==NULL ? '=' : '~' + ); + appendStringLiteralAH(query, thisrelname->name, g_fout); + + if (thisrelname->next && thisrelname->next->action == thisrelname->action) { + needwhere = 0; + continue; + } + needwhere = 1; + appendPQExpBuffer(query, ")"); + + /* Add the joiner if needed */ + if (thisrelname->next) { + appendPQExpBuffer(query, "\n%s\n", + thisrelname->next->action ? "UNION" : "EXCEPT"); + } + + } + + if (needwhere != 2) { + + if (goodSchemas != NULL) { + appendPQExpBuffer(query, + "\nINTERSECT\nSELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND relnamespace IN (%s)\n", + goodSchemas); + } + + res = PQexec(g_conn, query->data); + check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK); + if (PQntuples(res) == 0) + { + write_msg(NULL, "No matching tables were found\n"); + exit_nicely(); + } + j = 2; + for (i = 0; i < PQntuples(res); i++) { + j += 1+strlen(PQgetvalue(res, i, 0)); + } + goodTables = malloc(j); + strcpy(goodTables, " "); + for (i = 0; i < PQntuples(res); i++) { + strcat(goodTables, PQgetvalue(res, i, 0)); + strcat(goodTables, " "); + } + } + + destroyPQExpBuffer(query); + /* * Now scan the database and create DumpableObject structs for all the * objects we intend to dump. */ - tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly); + tblinfo = getSchemaData(&numTables); if (!schemaOnly) getTableData(tblinfo, numTables, oids); @@ -628,7 +792,7 @@ dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ - if (!dataOnly && selectTableName == NULL && selectSchemaName == NULL) + if (!dataOnly && goodTables == NULL && goodSchemas == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ @@ -687,28 +851,30 @@ printf(_(" --version output version information, then exit\n")); printf(_("\nOptions controlling the output content:\n")); - printf(_(" -a, --data-only dump only the data, not the schema\n")); - printf(_(" -c, --clean clean (drop) schema prior to create\n")); - printf(_(" -C, --create include commands to create database in dump\n")); - printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n")); - printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n")); - printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); - printf(_(" -n, --schema=SCHEMA dump the named schema only\n")); - printf(_(" -o, --oids include OIDs in dump\n")); - printf(_(" -O, --no-owner skip restoration of object ownership\n" - " in plain text format\n")); - printf(_(" -s, --schema-only dump only the schema, no data\n")); - printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n" - " plain text format\n")); - printf(_(" -t, --table=TABLE dump the named table only\n")); - printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); + printf(_(" -a, --data-only dump only the data, not the schema\n")); + printf(_(" -c, --clean clean (drop) schema prior to create\n")); + printf(_(" -C, --create include commands to create database in dump\n")); + printf(_(" -d, --inserts dump data as INSERT, rather than COPY, commands\n")); + printf(_(" -D, --column-inserts dump data as INSERT commands with column names\n")); + printf(_(" -E, --encoding=ENCODING dump the data in encoding ENCODING\n")); + printf(_(" -n, --schema=SCHEMA dump the named schema only\n")); + printf(_(" -N, --exclude-schema=SCHEMA do NOT dump the named schema\n")); + printf(_(" -o, --oids include OIDs in dump\n")); + printf(_(" -O, --no-owner skip restoration of object ownership\n" + " in plain text format\n")); + printf(_(" -s, --schema-only dump only the schema, no data\n")); + printf(_(" -S, --superuser=NAME specify the superuser user name to use in\n" + " plain text format\n")); + printf(_(" -t, --table=TABLE dump the named table only\n")); + printf(_(" -T, --exclude-table=TABLE do NOT dump the named table\n")); + printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); printf(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n" - " disable dollar quoting, use SQL standard quoting\n")); + " disable dollar quoting, use SQL standard quoting\n")); printf(_(" -X disable-triggers, --disable-triggers\n" - " disable triggers during data-only restore\n")); + " disable triggers during data-only restore\n")); printf(_(" -X use-set-session-authorization, --use-set-session-authorization\n" - " use SESSION AUTHORIZATION commands instead of\n" - " OWNER TO commands\n")); + " use SESSION AUTHORIZATION commands instead of\n" + " OWNER TO commands\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); @@ -724,6 +890,24 @@ void exit_nicely(void) { + /* Free up any relation names */ + relnameArg *nextrelname; + + free(goodTables); + free(goodSchemas); + while (tableList != NULL) { + nextrelname = tableList->next; + free(tableList->name); + free(tableList); + tableList = nextrelname; + } + while (schemaList != NULL) { + nextrelname = schemaList->next; + free(schemaList->name); + free(schemaList); + schemaList = nextrelname; + } + PQfinish(g_conn); if (g_verbose) write_msg(NULL, "*** aborted because of error\n"); @@ -738,18 +922,20 @@ selectDumpableNamespace(NamespaceInfo *nsinfo) { /* - * If a specific table is being dumped, do not dump any complete - * namespaces. If a specific namespace is being dumped, dump just that - * namespace. Otherwise, dump all non-system namespaces. + * If specific tables are being dumped, do not dump any complete + * namespaces. If specific namespaces are being dumped, dump just + * those namespaces. Otherwise, dump all non-system namespaces. */ - if (selectTableName != NULL) + if (goodTables != NULL) nsinfo->dobj.dump = false; - else if (selectSchemaName != NULL) + else if (goodSchemas != NULL) { - if (strcmp(nsinfo->dobj.name, selectSchemaName) == 0) - nsinfo->dobj.dump = true; - else - nsinfo->dobj.dump = false; + char *searchname = NULL; + searchname = malloc(20); + sprintf(searchname, ",%d,", nsinfo->dobj.catId.oid); + if (strstr(goodSchemas, searchname) != NULL) + nsinfo->dobj.dump = true; + free(searchname); } else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || strcmp(nsinfo->dobj.name, "information_schema") == 0) @@ -771,16 +957,17 @@ * dump. */ tbinfo->dobj.dump = false; - if (tbinfo->dobj.namespace->dobj.dump) - tbinfo->dobj.dump = true; - else if (selectTableName != NULL && - strcmp(tbinfo->dobj.name, selectTableName) == 0) + if (tbinfo->dobj.namespace->dobj.dump || goodTables == NULL) { - /* If both -s and -t specified, must match both to dump */ - if (selectSchemaName == NULL) - tbinfo->dobj.dump = true; - else if (strcmp(tbinfo->dobj.namespace->dobj.name, selectSchemaName) == 0) - tbinfo->dobj.dump = true; + tbinfo->dobj.dump = true; + } + else { + char *searchname = NULL; + searchname = malloc(20); + sprintf(searchname, " %d ", tbinfo->dobj.catId.oid); + if (strstr(goodTables, searchname) != NULL) + tbinfo->dobj.dump = true; + free(searchname); } } @@ -1722,25 +1909,6 @@ nsinfo[i].dobj.name); } - /* - * If the user attempted to dump a specific namespace, check to ensure - * that the specified namespace actually exists. - */ - if (selectSchemaName) - { - for (i = 0; i < ntups; i++) - if (strcmp(nsinfo[i].dobj.name, selectSchemaName) == 0) - break; - - /* Didn't find a match */ - if (i == ntups) - { - write_msg(NULL, "specified schema \"%s\" does not exist\n", - selectSchemaName); - exit_nicely(); - } - } - PQclear(res); destroyPQExpBuffer(query); @@ -2879,26 +3047,6 @@ tblinfo[i].dobj.name); } - /* - * If the user is attempting to dump a specific table, check to ensure - * that the specified table actually exists. (This is a bit simplistic - * since we don't fully check the combination of -n and -t switches.) - */ - if (selectTableName) - { - for (i = 0; i < ntups; i++) - if (strcmp(tblinfo[i].dobj.name, selectTableName) == 0) - break; - - /* Didn't find a match */ - if (i == ntups) - { - write_msg(NULL, "specified table \"%s\" does not exist\n", - selectTableName); - exit_nicely(); - } - } - PQclear(res); destroyPQExpBuffer(query); destroyPQExpBuffer(delqry); @@ -5412,7 +5560,7 @@ static bool shouldDumpProcLangs(void) { - if (selectTableName != NULL || selectSchemaName != NULL) + if (goodTables != NULL || goodSchemas != NULL) return false; /* And they're schema not data */ if (dataOnly) Index: src/bin/pg_dump/pg_dump.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v retrieving revision 1.126 diff -u -r1.126 pg_dump.h --- src/bin/pg_dump/pg_dump.h 2 Jul 2006 02:23:21 -0000 1.126 +++ src/bin/pg_dump/pg_dump.h 17 Jul 2006 02:10:19 -0000 @@ -341,9 +341,7 @@ * common utility functions */ -extern TableInfo *getSchemaData(int *numTablesPtr, - const bool schemaOnly, - const bool dataOnly); +extern TableInfo *getSchemaData(int *numTablesPtr); typedef enum _OidOptions {
signature.asc
Description: This is a digitally signed message part
