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
