Re: pg_dump enhancement proposal

Mark Hammonds <mhammonds@omniti.com> writes:
> 1. View Based Exports

> In addition to being able to dump specific tables, it seems useful to  
> be able to quickly specify and dump entire views.

Isn't this pretty much the same thing as your #2?  A view in PG isn't
materialized, it's nothing but a custom SELECT.

> 2.  Custom Query Exports

> In my use of mysqldump, I found one feature very useful: the ability  
> to execute a custom SELECT. . .WHERE statement and then dump only the  
> results. This feature currently provides MySQL users with the ability  
> to quickly and easily export very granular data subsets, and I see no  
> reason why PostgreSQL users wouldn't benefit from the same capability.  
> While it is true that this functionality can already be achieved in  
> PostgreSQL using Copy, it seems to me that it would logically fit well  
> as an extension to pg_dump, especially since many beginning and even  
> some intermediate PostgreSQL users aren't aware of the alternatives.

As you say, we already have this using COPY, and I don't agree that
it would be a good idea to plaster it into pg_dump as well.  pg_dump
is intended for dumping and restoring data, not for ETL-type tasks.
Furthermore, pg_dump is a overly complex beast already --- much more
so than one could wish, for a tool that is absolutely fundamental to
database reliability.  Putting requirements on it that are well outside
its charter seems like a short route to maintenance disaster.

There has been some occasional chatter about developing one or more
tools focused on ETL rather than dump/restore, and my thought is that
this idea would fit better there.  An ETL tool would not have the
kind of requirements pg_dump has for coping with multiple server
versions and knowing everything there is to know about database
contents, so it seems like it could address new areas of functionality
without a complexity explosion.

You might want to check the archives for previous discussions ---
I think the last go-round started with someone wanting to add an
arbitrary WHERE filter to pg_dump dumps.
        regards, tom lane