Thread: Added schema selection to pg_restore
Since I needed this feature badly, I added the -n / --schema switch to pg_restore. It restores the given schemaname only. It can be used in conjunction with the -t and other switches to make the selection very fine grained. This patches works for me, but it could use more testing. Please Cc me in the discussion, as I am not on these lists. I used the current CVS tree at :pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot as a starting point. Sincerely, -- Richard van den Berg, CISSP Trust Factory B.V. | http://www.trust-factory.com/ Bazarstraat 44a | Phone: +31 70 3620684 NL-2518AK The Hague | Fax : +31 70 3603009 The Netherlands | Index: doc/src/sgml/ref/pg_restore.sgml =================================================================== RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.47 diff -c -r1.47 pg_restore.sgml *** doc/src/sgml/ref/pg_restore.sgml 13 Jul 2004 02:59:49 -0000 1.47 --- doc/src/sgml/ref/pg_restore.sgml 19 Aug 2004 13:22:27 -0000 *************** *** 228,233 **** --- 228,244 ---- </varlistentry> <varlistentry> + <term><option>-n <replaceable class="parameter">namespace</replaceable></option></term> + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> + <listitem> + <para> + Restore definition and/or data of named schema only. Not to be + confused with the --schema-only option. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><option>-O</option></term> <term><option>--no-owner</option></term> <listitem> Index: src/bin/pg_dump/pg_backup.h =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup.h,v retrieving revision 1.31 diff -c -r1.31 pg_backup.h *** src/bin/pg_dump/pg_backup.h 13 Jul 2004 03:00:17 -0000 1.31 --- src/bin/pg_dump/pg_backup.h 19 Aug 2004 13:22:27 -0000 *************** *** 94,99 **** --- 94,100 ---- char *indexNames; char *functionNames; char *tableNames; + char *schemaNames; char *triggerNames; int useDB; Index: src/bin/pg_dump/pg_backup_archiver.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v retrieving revision 1.92 diff -c -r1.92 pg_backup_archiver.c *** src/bin/pg_dump/pg_backup_archiver.c 13 Aug 2004 21:37:28 -0000 1.92 --- src/bin/pg_dump/pg_backup_archiver.c 19 Aug 2004 13:22:28 -0000 *************** *** 1927,1932 **** --- 1927,1940 ---- /* Check if tablename only is wanted */ if (ropt->selTypes) { + if (ropt->schemaNames) + { + /* No namespace but namespace requested means we will not include it */ + if (!te->namespace) + return 0; + if(strcmp(ropt->schemaNames, te->namespace) != 0) + return 0; + } if ((strcmp(te->desc, "TABLE") == 0) || (strcmp(te->desc, "TABLE DATA") == 0)) { if (!ropt->selTable) Index: src/bin/pg_dump/pg_restore.c =================================================================== RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_restore.c,v retrieving revision 1.59 diff -c -r1.59 pg_restore.c *** src/bin/pg_dump/pg_restore.c 13 Jul 2004 03:00:17 -0000 1.59 --- src/bin/pg_dump/pg_restore.c 19 Aug 2004 13:22:28 -0000 *************** *** 103,108 **** --- 103,109 ---- {"no-reconnect", 0, NULL, 'R'}, {"port", 1, NULL, 'p'}, {"password", 0, NULL, 'W'}, + {"schema", 1, NULL, 'n'}, {"schema-only", 0, NULL, 's'}, {"superuser", 1, NULL, 'S'}, {"table", 1, NULL, 't'}, *************** *** 141,147 **** } } ! while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:Op:P:RsS:t:T:uU:vWxX:", cmdopts, NULL)) != -1) { switch (c) --- 142,148 ---- } } ! while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:n:Op:P:RsS:t:T:uU:vWxX:", cmdopts, NULL)) != -1) { switch (c) *************** *** 220,225 **** --- 221,231 ---- opts->tableNames = strdup(optarg); break; + case 'n': /* Dump data for this schema only */ + opts->selTypes = 1; + opts->schemaNames = strdup(optarg); + break; + case 'u': opts->requirePassword = true; opts->username = simple_prompt("User name: ", 100, true); *************** *** 371,376 **** --- 377,383 ---- printf(_(" -I, --index=NAME restore named index\n")); printf(_(" -L, --use-list=FILENAME use specified table of contents for ordering\n" " output from this file\n")); + printf(_(" -n, --schema=NAME restore named schema\n")); printf(_(" -O, --no-owner do not output commands to set object ownership\n")); printf(_(" -P, --function=NAME(args)\n" " restore named function\n"));
This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Richard van den Berg wrote:
> Since I needed this feature badly, I added the -n / --schema switch to
> pg_restore. It restores the given schemaname only. It can be used in
> conjunction with the -t and other switches to make the selection very
> fine grained.
>
> This patches works for me, but it could use more testing.
>
> Please Cc me in the discussion, as I am not on these lists.
>
> I used the current CVS tree at
> :pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot as a starting
> point.
>
> Sincerely,
>
> --
> Richard van den Berg, CISSP
>
> Trust Factory B.V. | http://www.trust-factory.com/
> Bazarstraat 44a | Phone: +31 70 3620684
> NL-2518AK The Hague | Fax : +31 70 3603009
> The Netherlands |
> Index: doc/src/sgml/ref/pg_restore.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v
> retrieving revision 1.47
> diff -c -r1.47 pg_restore.sgml
> *** doc/src/sgml/ref/pg_restore.sgml 13 Jul 2004 02:59:49 -0000 1.47
> --- doc/src/sgml/ref/pg_restore.sgml 19 Aug 2004 13:22:27 -0000
> ***************
> *** 228,233 ****
> --- 228,244 ----
> </varlistentry>
>
> <varlistentry>
> + <term><option>-n <replaceable class="parameter">namespace</replaceable></option></term>
> + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
> + <listitem>
> + <para>
> + Restore definition and/or data of named schema only. Not to be
> + confused with the --schema-only option.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> <term><option>-O</option></term>
> <term><option>--no-owner</option></term>
> <listitem>
> Index: src/bin/pg_dump/pg_backup.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup.h,v
> retrieving revision 1.31
> diff -c -r1.31 pg_backup.h
> *** src/bin/pg_dump/pg_backup.h 13 Jul 2004 03:00:17 -0000 1.31
> --- src/bin/pg_dump/pg_backup.h 19 Aug 2004 13:22:27 -0000
> ***************
> *** 94,99 ****
> --- 94,100 ----
> char *indexNames;
> char *functionNames;
> char *tableNames;
> + char *schemaNames;
> char *triggerNames;
>
> int useDB;
> Index: src/bin/pg_dump/pg_backup_archiver.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v
> retrieving revision 1.92
> diff -c -r1.92 pg_backup_archiver.c
> *** src/bin/pg_dump/pg_backup_archiver.c 13 Aug 2004 21:37:28 -0000 1.92
> --- src/bin/pg_dump/pg_backup_archiver.c 19 Aug 2004 13:22:28 -0000
> ***************
> *** 1927,1932 ****
> --- 1927,1940 ----
> /* Check if tablename only is wanted */
> if (ropt->selTypes)
> {
> + if (ropt->schemaNames)
> + {
> + /* No namespace but namespace requested means we will not include it */
> + if (!te->namespace)
> + return 0;
> + if(strcmp(ropt->schemaNames, te->namespace) != 0)
> + return 0;
> + }
> if ((strcmp(te->desc, "TABLE") == 0) || (strcmp(te->desc, "TABLE DATA") == 0))
> {
> if (!ropt->selTable)
> Index: src/bin/pg_dump/pg_restore.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_restore.c,v
> retrieving revision 1.59
> diff -c -r1.59 pg_restore.c
> *** src/bin/pg_dump/pg_restore.c 13 Jul 2004 03:00:17 -0000 1.59
> --- src/bin/pg_dump/pg_restore.c 19 Aug 2004 13:22:28 -0000
> ***************
> *** 103,108 ****
> --- 103,109 ----
> {"no-reconnect", 0, NULL, 'R'},
> {"port", 1, NULL, 'p'},
> {"password", 0, NULL, 'W'},
> + {"schema", 1, NULL, 'n'},
> {"schema-only", 0, NULL, 's'},
> {"superuser", 1, NULL, 'S'},
> {"table", 1, NULL, 't'},
> ***************
> *** 141,147 ****
> }
> }
>
> ! while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:Op:P:RsS:t:T:uU:vWxX:",
> cmdopts, NULL)) != -1)
> {
> switch (c)
> --- 142,148 ----
> }
> }
>
> ! while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:n:Op:P:RsS:t:T:uU:vWxX:",
> cmdopts, NULL)) != -1)
> {
> switch (c)
> ***************
> *** 220,225 ****
> --- 221,231 ----
> opts->tableNames = strdup(optarg);
> break;
>
> + case 'n': /* Dump data for this schema only */
> + opts->selTypes = 1;
> + opts->schemaNames = strdup(optarg);
> + break;
> +
> case 'u':
> opts->requirePassword = true;
> opts->username = simple_prompt("User name: ", 100, true);
> ***************
> *** 371,376 ****
> --- 377,383 ----
> printf(_(" -I, --index=NAME restore named index\n"));
> printf(_(" -L, --use-list=FILENAME use specified table of contents for ordering\n"
> " output from this file\n"));
> + printf(_(" -n, --schema=NAME restore named schema\n"));
> printf(_(" -O, --no-owner do not output commands to set object ownership\n"));
> printf(_(" -P, --function=NAME(args)\n"
> " restore named function\n"));
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --------------------------------------------------------------------------- Richard van den Berg wrote: > Since I needed this feature badly, I added the -n / --schema switch to > pg_restore. It restores the given schemaname only. It can be used in > conjunction with the -t and other switches to make the selection very > fine grained. > > This patches works for me, but it could use more testing. > > Please Cc me in the discussion, as I am not on these lists. > > I used the current CVS tree at > :pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot as a starting > point. > > Sincerely, > > -- > Richard van den Berg, CISSP > > Trust Factory B.V. | http://www.trust-factory.com/ > Bazarstraat 44a | Phone: +31 70 3620684 > NL-2518AK The Hague | Fax : +31 70 3603009 > The Netherlands | > Index: doc/src/sgml/ref/pg_restore.sgml > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v > retrieving revision 1.47 > diff -c -r1.47 pg_restore.sgml > *** doc/src/sgml/ref/pg_restore.sgml 13 Jul 2004 02:59:49 -0000 1.47 > --- doc/src/sgml/ref/pg_restore.sgml 19 Aug 2004 13:22:27 -0000 > *************** > *** 228,233 **** > --- 228,244 ---- > </varlistentry> > > <varlistentry> > + <term><option>-n <replaceable class="parameter">namespace</replaceable></option></term> > + <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term> > + <listitem> > + <para> > + Restore definition and/or data of named schema only. Not to be > + confused with the --schema-only option. > + </para> > + </listitem> > + </varlistentry> > + > + <varlistentry> > <term><option>-O</option></term> > <term><option>--no-owner</option></term> > <listitem> > Index: src/bin/pg_dump/pg_backup.h > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup.h,v > retrieving revision 1.31 > diff -c -r1.31 pg_backup.h > *** src/bin/pg_dump/pg_backup.h 13 Jul 2004 03:00:17 -0000 1.31 > --- src/bin/pg_dump/pg_backup.h 19 Aug 2004 13:22:27 -0000 > *************** > *** 94,99 **** > --- 94,100 ---- > char *indexNames; > char *functionNames; > char *tableNames; > + char *schemaNames; > char *triggerNames; > > int useDB; > Index: src/bin/pg_dump/pg_backup_archiver.c > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v > retrieving revision 1.92 > diff -c -r1.92 pg_backup_archiver.c > *** src/bin/pg_dump/pg_backup_archiver.c 13 Aug 2004 21:37:28 -0000 1.92 > --- src/bin/pg_dump/pg_backup_archiver.c 19 Aug 2004 13:22:28 -0000 > *************** > *** 1927,1932 **** > --- 1927,1940 ---- > /* Check if tablename only is wanted */ > if (ropt->selTypes) > { > + if (ropt->schemaNames) > + { > + /* No namespace but namespace requested means we will not include it */ > + if (!te->namespace) > + return 0; > + if(strcmp(ropt->schemaNames, te->namespace) != 0) > + return 0; > + } > if ((strcmp(te->desc, "TABLE") == 0) || (strcmp(te->desc, "TABLE DATA") == 0)) > { > if (!ropt->selTable) > Index: src/bin/pg_dump/pg_restore.c > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_restore.c,v > retrieving revision 1.59 > diff -c -r1.59 pg_restore.c > *** src/bin/pg_dump/pg_restore.c 13 Jul 2004 03:00:17 -0000 1.59 > --- src/bin/pg_dump/pg_restore.c 19 Aug 2004 13:22:28 -0000 > *************** > *** 103,108 **** > --- 103,109 ---- > {"no-reconnect", 0, NULL, 'R'}, > {"port", 1, NULL, 'p'}, > {"password", 0, NULL, 'W'}, > + {"schema", 1, NULL, 'n'}, > {"schema-only", 0, NULL, 's'}, > {"superuser", 1, NULL, 'S'}, > {"table", 1, NULL, 't'}, > *************** > *** 141,147 **** > } > } > > ! while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:Op:P:RsS:t:T:uU:vWxX:", > cmdopts, NULL)) != -1) > { > switch (c) > --- 142,148 ---- > } > } > > ! while ((c = getopt_long(argc, argv, "acCd:f:F:h:iI:lL:n:Op:P:RsS:t:T:uU:vWxX:", > cmdopts, NULL)) != -1) > { > switch (c) > *************** > *** 220,225 **** > --- 221,231 ---- > opts->tableNames = strdup(optarg); > break; > > + case 'n': /* Dump data for this schema only */ > + opts->selTypes = 1; > + opts->schemaNames = strdup(optarg); > + break; > + > case 'u': > opts->requirePassword = true; > opts->username = simple_prompt("User name: ", 100, true); > *************** > *** 371,376 **** > --- 377,383 ---- > printf(_(" -I, --index=NAME restore named index\n")); > printf(_(" -L, --use-list=FILENAME use specified table of contents for ordering\n" > " output from this file\n")); > + printf(_(" -n, --schema=NAME restore named schema\n")); > printf(_(" -O, --no-owner do not output commands to set object ownership\n")); > printf(_(" -P, --function=NAME(args)\n" > " restore named function\n")); > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073