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.klaver@aklaver.com