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         |
Thu, 19 Aug 2004 15:32:32 +0200
Richard van den Berg <richard.vandenberg@trust-factory.com>
Added schema selection to pg_restore

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

Fri, 20 Aug 2004 16:20:44 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Added schema selection to pg_restore

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, Pennsylvania 19073

Sat, 4 Jun 2005 19:20:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Added schema selection to pg_restore
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"));
Thu, 9 Jun 2005 14:00:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Added schema selection to pg_restore