Обсуждение: enhancement request for pg_restore

PostgreSQL 9.4.x

I love using the PostgreSQL database.  It is wonderful.

Please consider adding to pg_restore the command line option to exclude one or more tables similar to the pg_dump
option.

Example:

--exclude-table=table

Why?  I like to restore all tables within a particular namespace from our production database to our test database.
Butthere are many times when you have some tables that hold data you don't necessarily need information from.  In those
casesI would exclude those tables.  I would then do a second pg_restore so I could get the schema for those particular
tables.

Example of how this would work:

pg_restore -U someuser -h localhost -d mydatabase --schema=myschema  --exclude-table=myschema.table1
--exclude-table=myschema.table2--format=custom  /my/backup/to/restore 

pg_restore -U someuser -h localhost -d mydatabase --schema-only  -- table=myschema.table1  --table=myschema.table2
--format=custom /my/backup/to/restore 

Thanks for considering this enhancement to pg_restore.

Lance Campbell
Web Services
University of Illinois

Hi Lance,

On 2015-08-13 15:10:21 +0000, Campbell, Lance wrote:
> PostgreSQL 9.4.x
>
> I love using the PostgreSQL database.  It is wonderful.

Good to hear!

> Please consider adding to pg_restore the command line option to exclude one or more tables similar to the pg_dump
option.
>
> Example:
>
> --exclude-table=table
>
> Why?  I like to restore all tables within a particular namespace from
> our production database to our test database.  But there are many
> times when you have some tables that hold data you don't necessarily
> need information from.  In those cases I would exclude those tables.
> I would then do a second pg_restore so I could get the schema for
> those particular tables.

You can kind of do this today using pg_restore's --list parameter to
dump the list of objects in the archive, then remove the objects you
don't need/want, and then use --use-list to restore only the remaining
objects.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2015-08-13 15:10:21 +0000, Campbell, Lance wrote:
>> Please consider adding to pg_restore the command line option to exclude one or more tables similar to the pg_dump
option.

> You can kind of do this today using pg_restore's --list parameter to
> dump the list of objects in the archive, then remove the objects you
> don't need/want, and then use --use-list to restore only the remaining
> objects.

IIRC, the key reason why this is less than trivial is that pg_dump relies
on the server to identify which table names match the supplied pattern(s);
it basically does a "select from pg_class where relname ~ 'pattern'" kind
of query.  Short of porting the server's regex engine into pg_restore,
it would be hard to be 100% compatible with that definition.

Perhaps it's not necessary to implement wildcards in the pg_restore
case, or maybe it would be Close Enough if we relied on libc regexes,
but in any case it's not quite as simple as it might sound.

So basically this is unlikely to happen until somebody who wants the
feature bad enough spends the time to write and defend a patch.

            regards, tom lane