Re: Option to dump foreign data in pg_dump

pá 28. 6. 2019 v 17:30 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Daniel Gustafsson <daniel(at)yesql(dot)se> writes:
> >> On 28 Jun 2019, at 16:49, Luis Carril <luis(dot)carril(at)swarm64(dot)com> wrote:
> >> pg_dump ignores the dumping of data in foreign tables
> >> on purpose, this patch makes it optional as the user maybe
> >> wants to manage the data in the foreign servers directly from
> >> Postgres. Opinions?
>
> > Wouldn’t that have the potential to make restores awkward for FDWs that
> aren’t
> > writeable?
>
> Yeah, I think the feature as-proposed is a shotgun that's much more likely
> to cause problems than solve them. Almost certainly, what people would
> really need is the ability to dump individual foreign tables' data not
> everything. (I also note that the main reason for "dump everything",
> namely to get a guaranteed-consistent snapshot, isn't really valid for
> foreign tables anyhow.)
>

I agree so major usage is dumping data. But can be interesting some
transformation from foreign table to classic table (when schema was created
by IMPORT FOREIGN SCHEMA).

> I'm tempted to suggest that the way to approach this is to say that if you
> explicitly select some foreign table(s) with "-t", then we'll dump their
> data, unless you suppress that with "-s". No new switch needed.
>
> Another way of looking at it, which responds more directly to Daniel's
> point about non-writable FDWs, could be to have a switch that says "dump
> foreign tables' data if their FDW is one of these".
>

Restoring content of FDW table via pg_restore or psql can be dangerous -
there I see a risk, and can be nice to allow it only with some form of
safeguard.

I think so important questions is motivation for dumping FDW - a) clonning
(has sense for me and it is safe), b) real backup (requires writeable FDW)
- has sense too, but I see a possibility of unwanted problems.

Regards

Pavel

>
> regards, tom lane
>
>
>