Re: pg_restore --create --no-tablespaces should not issue 'CREATE DATABASE ... TABLESPACE'

On Sat, Dec 16, 2017 at 7:56 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> Per the documentation in
> https://www.postgresql.org/docs/devel/static/app-pgrestore.html:
> Do not output commands to select tablespaces. With this option, all
> objects will be created in whichever tablespace is the default during
> restore.
>
> So it seems to me that this is a justified complain. Thoughts?

I have just filled in my memory holes about pg_dump and pg_restore,
and for tables and indexes we rely on default_tablespace to create
them in the correct location. This way, bypassing the tablespace
location is easy when restoring: if --no-tablespaces is defined, there
is no change of default tablespace. However, default_tablespace does
not take effect for databases and the location is inherited from the
database template. So, pg_dump has to generate a WITH TABLESPACE
clause in the query used to create the database as it is taken
correctly into account.

A workaround is to use pg_dump with --no-tablespace so as the database
is dumped with its tablespace location, and you can use the dump as-is
during restore.

If there is a bug here, I think that it is in the documentation as the
docs of pg_dump and pg_restore are evasive about databases and
tablespace handling as it claims that "all objects" are created with
the default tablespace. So what I would suggest is a doc patch that
does the following:
- Mention in the page of pg_dump, under --no-tablespaces, that for the
archive format this parameter matters for databases.
- Mention in the page of pg_restore that --no-tablespaces has no
effects on databases, and that the option used at the moment of the
dump matters.

Thoughts?
--
Michael