Re: default_tablespace in 8.3 postgresql - Mailing list pgsql-general
From | Julie Nishimura |
---|---|
Subject | Re: default_tablespace in 8.3 postgresql |
Date | |
Msg-id | BYAPR08MB50141ABB3D3816BDA9D0F583AC090@BYAPR08MB5014.namprd08.prod.outlook.com Whole thread Raw |
In response to | Re: default_tablespace in 8.3 postgresql (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: default_tablespace in 8.3 postgresql
Re: default_tablespace in 8.3 postgresql |
List | pgsql-general |
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...
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
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
> 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
pgsql-general by date: