Re: [HACKERS] Added schema selection to pg_restore - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: [HACKERS] Added schema selection to pg_restore
Date
Msg-id 200506091800.j59I03Y16605@candle.pha.pa.us
Whole thread Raw
In response to Added schema selection to pg_restore  (Richard van den Berg <richard.vandenberg@trust-factory.com>)
List pgsql-patches
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.

I have applied your patch with minor wording changes --- new version
attached.  It will appear in 8.1.

--
  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
Index: doc/src/sgml/ref/pg_restore.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.51
diff -c -c -r1.51 pg_restore.sgml
*** doc/src/sgml/ref/pg_restore.sgml    29 May 2005 03:32:18 -0000    1.51
--- doc/src/sgml/ref/pg_restore.sgml    9 Jun 2005 17:55:54 -0000
***************
*** 240,245 ****
--- 240,257 ----
       </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 only definitions and/or data in the named schema. Not to be
+         confused with the <option>-s</option> option.  This can be combined with
+         <option>-t</option> 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: /cvsroot/pgsql/src/bin/pg_dump/pg_backup.h,v
retrieving revision 1.34
diff -c -c -r1.34 pg_backup.h
*** src/bin/pg_dump/pg_backup.h    6 Nov 2004 19:36:01 -0000    1.34
--- src/bin/pg_dump/pg_backup.h    9 Jun 2005 17:55:55 -0000
***************
*** 98,103 ****
--- 98,104 ----
      char       *indexNames;
      char       *functionNames;
      char       *tableNames;
+     char       *schemaNames;
      char       *triggerNames;

      int            useDB;
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.109
diff -c -c -r1.109 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c    17 May 2005 17:30:29 -0000    1.109
--- src/bin/pg_dump/pg_backup_archiver.c    9 Jun 2005 17:55:56 -0000
***************
*** 1997,2002 ****
--- 1997,2010 ----
      /* Check if tablename only is wanted */
      if (ropt->selTypes)
      {
+         if (ropt->schemaNames)
+         {
+             /* If no namespace is specified, it means all. */
+             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: /cvsroot/pgsql/src/bin/pg_dump/pg_restore.c,v
retrieving revision 1.70
diff -c -c -r1.70 pg_restore.c
*** src/bin/pg_dump/pg_restore.c    29 Apr 2005 07:08:06 -0000    1.70
--- src/bin/pg_dump/pg_restore.c    9 Jun 2005 17:55:57 -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:ef: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:ef:F:h:iI:lL:n:Op:P:RsS:t:T:uU:vWxX:",
                              cmdopts, NULL)) != -1)
      {
          switch (c)
***************
*** 223,228 ****
--- 224,234 ----
                  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);
***************
*** 375,380 ****
--- 381,387 ----
      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 only objects in this schema\n"));
      printf(_("  -O, --no-owner           skip restoration of object ownership\n"));
      printf(_("  -P, --function=NAME(args)\n"
               "                           restore named function\n"));

pgsql-patches by date:

Previous
From: Tom Lane
Date:
Subject: Re: final light versions of Oracle compatibility (SQLSTATE, GREATEST,
Next
From: Tom Lane
Date:
Subject: Broken code in recent troff commit