Re: Using ALTER TABLESPACE in pg_dump

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> To solve this, we should dump the table definition as a format string and
> dump the tablespace clause (ie. ' TABLESPACE ts') as a separate part of the
> table definition TOC entry. If the user wants the tablespace to be dumped,
> then we substitute the tablespace clause, otherwise a blank string. This
> could be a useful general approach in the future.

I think the tricky part of that would be inserting the tablespace clause
in the right place; for CREATE INDEX this seems to require nontrivial
parsing. (Both the index column definitions and the WHERE clause could
be arbitrarily complicated expressions.) If we can get around that part
then this wouldn't be too hard.

> Also, I like the option of a soft-tablespace option, but also liked the
> idea of the fake/logical/virtual tablespaces someone suggested earlier; if
> restoring into a database without a required tablespace, then create a
> virtual tablespace that points to pg_default.

Given that tablespaces are fundamentally only directories, there isn't
any particularly strong reason to not just make a real tablespace. You
aren't going to constrain space allocation or anything by having another
directory in/alongside $PGDATA. So I think the "virtual tablespace"
idea is basically pointless.

The real crux of all this, I think, is "what if I want to restore as
a non-superuser, and so I don't have privilege to create tablespaces
to match what the dump wants?" The soft-failure option provides an
answer here, but creating either real or virtual tablespaces wouldn't
fly. A "--notablespace" option in pg_restore would solve it too, but
only if you'd done an -Fc or -Ft dump; with a plain text dump you
still got trouble.

regards, tom lane