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 @@ Dump data for table - only. It is possible for there to be - multiple tables with the same name in different schemas; if that - is the case, all matching tables will be dumped. Specify both - + + The options + + Examples + + To dump a single table named pg_class: + + +$ pg_dump -t pg_class mydb > db.out + + + + To dump all tables starting with employee in the + detroit schema, except for the table named employee_log: + + +$ pg_dump -n detroit -t ^employee -T employee_log mydb > db.out + + + + To dump all schemas starting with east or west and ending in + gsm, but not schemas that contain the letters test, except for + one named east_alpha_test_five: + + +$ pg_dump -n "^(east|west).*gsm$" -N test -n east_alpha_test_five mydb > db.out + + + + + To dump all tables except for those beginning with ts_: + + +$ pg_dump -T "^ts_" mydb > db.out + + + + In this mode, pg_dump makes no - attempt to dump any other database objects that the selected 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. @@ -417,6 +465,46 @@ + + + + + Do not dump any matching tables. + More than one option may be used, and POSIX regular expressions are allowed. + See + + + + + + + + + + + Dump only the matching schemas. + More than one option may be used, and POSIX regular expressions are allowed. + See + + + + + + + + + + Do not dump the matching schemas. + More than one option may be used, and POSIX regular expressions are allowed. + See + + + + + 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 {