Index: doc/src/sgml/ref/pg_dump.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.86 diff -c -c -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 1 Aug 2006 04:53:52 -0000 *************** *** 398,415 **** 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 ! In this mode, pg_dump makes no ! attempt to dump any other database objects that the selected table may depend upon. Therefore, there is no guarantee ! that the results of a single-table dump can be successfully restored by themselves into a clean database. --- 398,460 ---- 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. Also, if any POSIX regular expression character appears ! in the table name (([{\.?+, the string will be interpreted ! as a regular expression. Note that when in regular expression mode, the ! string will not be anchored to the start/end unless ^ and ! $ are used at the beginning/end of the string. + + The options + + For 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 tables may depend upon. Therefore, there is no guarantee ! that the results of a specific-table dump can be successfully restored by themselves into a clean database. *************** *** 417,422 **** --- 462,505 ---- + + + + + Do not dump any matching tables. + More than one option may be used, and POSIX regular expressions are handled just + like -t. + + + + + + + + + + Dump only the matching schemas. + More than one option may be used, and POSIX regular expressions are handled just + like -t. + + + + + + + + + + + Do not dump the matching schemas. + More than one option may be used, and POSIX regular expressions are handled just + like -t. + + + + + + Index: src/bin/pg_dump/common.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/common.c,v retrieving revision 1.91 diff -c -c -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 1 Aug 2006 04:53:54 -0000 *************** *** 72,80 **** * Collect information about all potentially dumpable objects */ TableInfo * ! getSchemaData(int *numTablesPtr, ! const bool schemaOnly, ! const bool dataOnly) { NamespaceInfo *nsinfo; AggInfo *agginfo; --- 72,78 ---- * Collect information about all potentially dumpable objects */ TableInfo * ! getSchemaData(int *numTablesPtr) { NamespaceInfo *nsinfo; AggInfo *agginfo; Index: src/bin/pg_dump/pg_dump.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v retrieving revision 1.442 diff -c -c -r1.442 pg_dump.c *** src/bin/pg_dump/pg_dump.c 27 Jul 2006 19:52:06 -0000 1.442 --- src/bin/pg_dump/pg_dump.c 1 Aug 2006 04:53:59 -0000 *************** *** 91,98 **** /* 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 */ char g_opaque_type[10]; /* name for the opaque type */ --- 91,109 ---- /* obsolete as of 7.3: */ static Oid g_last_builtin_oid; /* value of the last builtin oid */ ! /* select and exclude tables and schemas */ ! struct _relnameArg { ! char *name; /* name of the relation */ ! bool is_include; /* include/exclude? */ ! 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,185 **** --- 191,201 ---- 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,200 **** int numTables; DumpableObject **dobjs; int numObjs; ! int i; bool force_password = false; int compressLevel = -1; bool ignore_version = false; --- 210,217 ---- int numTables; DumpableObject **dobjs; int numObjs; ! int i, j; ! bool include_exclude_switch; bool force_password = false; int compressLevel = -1; bool ignore_version = false; *************** *** 226,234 **** --- 243,253 ---- {"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,287 **** } } ! while ((c = getopt_long(argc, argv, "abcCdDE:f:F:h:in:oOp:RsS:t:uU:vWxX:Z:", long_options, &optindex)) != -1) { switch (c) --- 300,306 ---- } } ! 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,339 **** ignore_version = true; break; ! case 'n': /* Dump data for this schema only */ ! selectSchemaName = strdup(optarg); break; case 'o': /* Dump oids */ --- 351,386 ---- ignore_version = true; break; ! 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->is_include = (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,370 **** 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); --- 408,413 ---- *************** *** 449,455 **** exit(1); } ! if (selectTableName != NULL || selectSchemaName != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) --- 492,498 ---- exit(1); } ! if (goodTables != NULL || goodSchemas != NULL) outputBlobs = false; if (dumpInserts == true && oids == true) *************** *** 568,578 **** write_msg(NULL, "last built-in OID is %u\n", g_last_builtin_oid); } /* * Now scan the database and create DumpableObject structs for all the * objects we intend to dump. */ ! tblinfo = getSchemaData(&numTables, schemaOnly, dataOnly); if (!schemaOnly) getTableData(tblinfo, numTables, oids); --- 611,763 ---- 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(); + } + + /* Check schema selection flags */ + resetPQExpBuffer(query); + include_exclude_switch = true; + for (thisrelname = schemaList; thisrelname; thisrelname = thisrelname->next) + { + if (include_exclude_switch) + { + /* Special case for when -N is the first argument */ + if (thisrelname == RelationList && !thisrelname->is_include) + 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 ", include_exclude_switch ? "" : " OR", + /* any meta-characters? */ + strpbrk(thisrelname->name,"([{\\.?+") == NULL ? '=' : '~'); + appendStringLiteralAH(query, thisrelname->name, g_fout); + + if (thisrelname->next && thisrelname->next->is_include == thisrelname->is_include) + include_exclude_switch = false; + else + { + include_exclude_switch = true; + + /* Add the joiner if needed */ + if (thisrelname->next) + appendPQExpBuffer(query, "\n%s\n", + thisrelname->next->is_include ? "UNION" : "EXCEPT"); + } + } + + /* Construct OID list of matching schemas */ + if (schemaList) + { + 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(); + } + + for (i = 0, j = strlen(" 0 "); i < PQntuples(res); i++) + j += strlen(PQgetvalue(res, i, 0)) + 1; + + /* + * Need to use comma separators so it can be used by IN. zero + * is a dummy placeholder. + */ + goodSchemas = malloc(j + 1); + strcpy(goodSchemas, " "); + for (i = 0; i < PQntuples(res); i++) + { + strcat(goodSchemas, PQgetvalue(res, i, 0)); + strcat(goodSchemas, " "); + } + strcat(goodSchemas, "0 "); + } + + /* Check table selection flags */ + resetPQExpBuffer(query); + include_exclude_switch = true; + for (thisrelname = tableList; thisrelname; thisrelname = thisrelname->next) + { + if (include_exclude_switch) + { + /* Special case for when -T is the first argument */ + if (thisrelname == RelationList && !thisrelname->is_include && !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 ", include_exclude_switch ? "" : " OR ", + /* any meta-characters? */ + strpbrk(thisrelname->name,"([{\\.?+") == NULL ? '=' : '~'); + appendStringLiteralAH(query, thisrelname->name, g_fout); + + if (thisrelname->next && thisrelname->next->is_include == thisrelname->is_include) + include_exclude_switch = false; + else + { + include_exclude_switch = true; + appendPQExpBuffer(query, ")"); + + /* Add the joiner if needed */ + if (thisrelname->next) + appendPQExpBuffer(query, "\n%s\n", thisrelname->next->is_include ? + "UNION" : "EXCEPT"); + } + } + + /* Construct OID list of matching tables */ + if (tableList) + { + if (goodSchemas != NULL) + { + char *goodSchemas_commas = strdup(goodSchemas + 1), *p; + + /* Construct SQL string by making all spaces commas, skip first/last. */ + for (p = goodSchemas_commas; *(p+1); p++) + { + if (*p == ' ') + *p = ','; + } + *p = '\0'; + + appendPQExpBuffer(query, + "\nINTERSECT\nSELECT oid FROM pg_catalog.pg_class WHERE relkind='r' AND relnamespace IN (%s)\n", + goodSchemas_commas); + } + + 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(); + } + + for (i = 0, j = strlen(" "); i < PQntuples(res); i++) + j += strlen(PQgetvalue(res, i, 0)) + 1; + + goodTables = malloc(j + 1); + 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); if (!schemaOnly) getTableData(tblinfo, numTables, oids); *************** *** 628,634 **** dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ ! if (!dataOnly && selectTableName == NULL && selectSchemaName == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ --- 813,819 ---- dumpStdStrings(g_fout); /* The database item is always next, unless we don't want it at all */ ! if (!dataOnly && goodTables == NULL && goodSchemas == NULL) dumpDatabase(g_fout); /* Now the rearrangeable objects. */ *************** *** 687,714 **** 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(_(" -X disable-dollar-quoting, --disable-dollar-quoting\n" ! " disable dollar quoting, use SQL standard quoting\n")); printf(_(" -X disable-triggers, --disable-triggers\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")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); --- 872,901 ---- 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(_(" -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")); printf(_(" -X disable-triggers, --disable-triggers\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")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n")); *************** *** 724,729 **** --- 911,936 ---- 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,755 **** 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 (selectTableName != NULL) nsinfo->dobj.dump = false; ! else if (selectSchemaName != NULL) { ! if (strcmp(nsinfo->dobj.name, selectSchemaName) == 0) nsinfo->dobj.dump = true; ! else ! nsinfo->dobj.dump = false; } else if (strncmp(nsinfo->dobj.name, "pg_", 3) == 0 || strcmp(nsinfo->dobj.name, "information_schema") == 0) --- 945,964 ---- selectDumpableNamespace(NamespaceInfo *nsinfo) { /* ! * 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 (goodTables != NULL) nsinfo->dobj.dump = false; ! else if (goodSchemas != NULL) { ! 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,786 **** * 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 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; } } --- 980,995 ---- * dump. */ tbinfo->dobj.dump = false; ! if (tbinfo->dobj.namespace->dobj.dump || goodTables == NULL) 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,1746 **** 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); --- 1931,1936 ---- *************** *** 2905,2930 **** 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); --- 3095,3100 ---- *************** *** 5438,5444 **** static bool shouldDumpProcLangs(void) { ! if (selectTableName != NULL || selectSchemaName != NULL) return false; /* And they're schema not data */ if (dataOnly) --- 5608,5614 ---- static bool shouldDumpProcLangs(void) { ! 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: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v retrieving revision 1.127 diff -c -c -r1.127 pg_dump.h *** src/bin/pg_dump/pg_dump.h 27 Jul 2006 19:52:06 -0000 1.127 --- src/bin/pg_dump/pg_dump.h 1 Aug 2006 04:53:59 -0000 *************** *** 340,348 **** * common utility functions */ ! extern TableInfo *getSchemaData(int *numTablesPtr, ! const bool schemaOnly, ! const bool dataOnly); typedef enum _OidOptions { --- 340,346 ---- * common utility functions */ ! extern TableInfo *getSchemaData(int *numTablesPtr); typedef enum _OidOptions {