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@xxxxxxxxxxx>
*Sent:* Wednesday, May 15, 2019 12:47 PM
*To:* Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; 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@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
