Re: default_tablespace in 8.3 postgresql

Adrian, thanks for your reply.
This is very interesting behavior...
Those databases have been created long time ago, only some tables in them were created today.
So, make it more clear: lets say we have 5 user databases, 5 tables in each db, + template0, template1, . I have modified 3 user databases and template1 to use new tablespace. However, since I have modified template1 (and most likely, those databases were copied from it in the past), it changed default_parameter for them as well. Interesting...

Thanks

________________________________
From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Sent: Wednesday, May 15, 2019 12:47 PM
To: Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)org; pgsql-general
Subject: Re: default_tablespace in 8.3 postgresql

On 5/15/19 12:39 PM, Julie Nishimura wrote:
> Hello, I have created new tablespace on a new drive yesterday and
> modified some of existing databases to use this tablespace as default,
> like this:
> ALTER DATABASE xxx
> SET default_tablespace = 'vol4';
>
> So, this command did not physically move any existing tables/indices on
> the database xxx, but all new objects are currently being created on
> this tablespace (this was expected).
>
> Please note, I also altered "template1" database to use new volume, to
> ensure all new databases will be created there by default.
>
> ALTER DATABASE template1
> SET default_tablespace = 'vol4';
>
> However, I have not yet modified postgresql.conf, and our settings for
> default_tablespace and temp_tablespaces still set to use "vol3", because
> I was planning to leave some of databases out of vol4 (newer
> tablespace), since I did NOT altered their default_tablespace, and it
> was previously set to vol3.
>
> Today, checking which tables were created where, I noticed, that all new
> tables, including tables from those databases which I did not alter to
> use new volume as default_tablespace, have been created on new volume 4.
> If I select from pg_database, I can see all my user databases have
> default_tablespace as vol4 (newer tablespace), even though I did not
> modify it. So, my question is, could altering "template1" database
> change default tablespace for all existing databases on a server???

https://www.postgresql.org/docs/8.3/manage-ag-tablespaces.html

"If a database is created without specifying a tablespace for it, it
uses the same tablespace as the template database it is copied from."

>
> PS. I could not make a mistake altering all, since I scripted my
> commands before, eliminating some of databases, and I do not see it in
> my script. Any thoughts?
>
> Thank you
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com