Re: default_tablespace in 8.3 postgresql - Mailing list pgsql-general

On 5/15/19 1:01 PM, Julie Nishimura wrote:
> 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...

I don't have an instance of 8.3 around so I can't test the above. On my 
11 instance I could not get the template1 to change tablespaces.

Can you confirm that template1 actually is set to 'vol4'?

I am with David I am not sure that even if it was changed that it would 
affect objects created in the past.

Using tablepaces is one of those actions where explicit is better then 
implicit. Whenever possible use the TABLESPACE clause to the object 
CREATE/ALTER command to be certain of where the object is going.


> 
> Thanks
> 
> 
> 
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Wednesday, May 15, 2019 12:47 PM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.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.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com