Thread: pg_restore --create --no-tablespaces should not issue 'CREATEDATABASE ... TABLESPACE'

Hi everyone at postgres.

Talking about version 9.5/9.6 (recent updates)

I have a problem with pg_dump / pg_restore
around the tablespaces.

Running pg_restore like:

    pg_restore --clean --create --no-tablespaces 

Did fail on CREATE DATABASE (in TABLESPACE ....), 
because that statement needed the tablespace to exists upfront.

---

I'd say this is a bug in pg_restore.

When invoking pg_restore with the option --no-tablespaces
the statement: CREATE DATABASE
should create the database in the current table space,
ignoring any table space designation from the export.

On Sat, Dec 16, 2017 at 6:26 AM, eike <eike@rikart.de> wrote:
> I'd say this is a bug in pg_restore.
>
> When invoking pg_restore with the option --no-tablespaces
> the statement: CREATE DATABASE
> should create the database in the current table space,
> ignoring any table space designation from the export.

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?
-- 
Michael

On Sat, Dec 16, 2017 at 7:56 AM, Michael Paquier
<michael.paquier@gmail.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