Re: BUG #6581: pg_dumpall --no-tablespaces option still sets default tablespace

On Wed, Apr 11, 2012 at 2:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> keith(at)omniti(dot)com writes:
>> Using the --no-tablespaces option for pg_dumpall correctly stops tablespace
>> creation and each individual object from having its tablespace set. But if
>> the original database had its default tablespace changed, this is still
>> added at the beginning of the dump file:
>
>> ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace';
>
> Well, what the --no-tablespaces option does is suppress actions that are
> related to tablespaces.  However, ALTER DATABASE SET options are just
> dumped out by generic code (likewise for ALTER ROLE SET, function SET
> options, yadda yadda).  I guess we could insert a hack that looked for
> tablespace-related options and didn't print them, but it seems like it
> would be a wart not a feature.
>
> Also, the purpose of the option is to let you reload the dump into an
> installation that hasn't got the same (or any) tablespaces.  ALTER SET
> commands won't prevent that, they'll at worst result in some NOTICEs
> during the reload.
>
> In short, I'm not real sure this should be considered a bug.
>
>                        regards, tom lane

Agreed it's not a serious bug and doesn't stop the restore during a
basic replay with psql. But besides the warning during restore, it
also causes warnings after the reload when you connect to the
database.

postgres=# \c dbname
WARNING: invalid value for parameter "default_tablespace": "alldata1"
DETAIL: Tablespace "alldata1" does not exist.
You are now connected to database "dbname" as user "postgres".
dbname=#

Again, easily fixed with running another ALTER after the database is
up and running. But figured I'd report it.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
443.325.1357 x251