default_tablespace in 8.3 postgresql

From: Julie Nishimura <juliezain(at)hotmail(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>, pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: default_tablespace in 8.3 postgresql
Date: 2019-05-15 19:39:10
Message-ID: BYAPR08MB50140E0F61E51CAB861B9DEAAC090@BYAPR08MB5014.namprd08.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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???

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-05-15 19:47:48 Re: default_tablespace in 8.3 postgresql
Previous Message Adrian Klaver 2019-05-15 14:20:54 Re: perl path issue