Обсуждение: default_tablespace in 8.3 postgresql
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';
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';
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
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, 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
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com> wrote:
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.
This seems unlikely to be the case - changing template1 should not be affecting other existing databases.
David J.
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
This puzzles me too! I found that bizarre myself. What is even more interesting, we have about 80 databases, and all of them now have default_tablespace=vol4, except only one - "control" database. The only explanation I would have that all of those databases which have tablespace vol4 as default were copied from template1 without explicitly specified tablespace name...
postgres=# select * from pg_database limit 10;
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl
-----------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+---------------------------+-----------------------------------------------------
template0 | 10 | 0 | t | f | -1 | 11510 | 378 | 1663 | | {=c/postgres,postgres=CTc/postgres}
postgres | 10 | 0 | f | t | -1 | 11510 | 7554523 | 1663 | |
control | 16389 | 6 | f | t | -1 | 11510 | 7554887 | 16384 | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
template1 | 10 | 0 | t | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
conversion_alerts_fs | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}
conversion_feasibility_too | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} |
conversion_feasibility_tool | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} |
custom_searches_au | 16393 | 6 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build} I've found that very bizarre myself, that default_tablespace would be changed for already existing databases without me altering it explicitly...
Sigh
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com> wrote:
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.
This seems unlikely to be the case - changing template1 should not be affecting other existing databases.
David J.
On 5/15/19 2:57 PM, Julie Nishimura wrote:
P {margin-top:0;margin-bottom:0;} This puzzles me too! I found that bizarre myself. What is even more interesting, we have about 80 databases, and all of them now have default_tablespace=vol4, except only one - "control" database. The only explanation I would have that all of those databases which have tablespace vol4 as default were copied from template1 without explicitly specified tablespace name...postgres=# select * from pg_database limit 10;datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl-----------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+---------------------------+-----------------------------------------------------template0 | 10 | 0 | t | f | -1 | 11510 | 378 | 1663 | | {=c/postgres,postgres=CTc/postgres}postgres | 10 | 0 | f | t | -1 | 11510 | 7554523 | 1663 | |control | 16389 | 6 | f | t | -1 | 11510 | 7554887 | 16384 | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}template1 | 10 | 0 | t | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}conversion_alerts_fs | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}conversion_feasibility_too | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} |conversion_feasibility_tool | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} |custom_searches_au | 16393 | 6 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}I've found that very bizarre myself, that default_tablespace would be changed for already existing databases without me altering it explicitly...SighFrom: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresqlOn Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com> wrote: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.This seems unlikely to be the case - changing template1 should not be affecting other existing databases.David J.
Previous actions may have altered (accidentally) the default table space for those databases but this doesn't show that the tables of old are now on vol4 does it?. Would have taken some time to move 80 non-trivial databases.
On 5/15/19 1:57 PM, Julie Nishimura wrote:
> This puzzles me too! I found that bizarre myself. What is even more
> interesting, we have about 80 databases, and all of them now have
> default_tablespace=vol4, except only one - "control" database. The only
> explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name...
>
> postgres=# select * from pg_database limit 10;
> datname | datdba | encoding | datistemplate |
> datallowconn | datconnlimit | datlastsysoid | datfrozenxid |
> dattablespace | datconfig | datacl
>
-----------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+---------------------------+-----------------------------------------------------
> template0 | 10 | 0 | t | f
> | -1 | 11510 | 378 | 1663
> | | {=c/postgres,postgres=CTc/postgres}
> postgres | 10 | 0 | f | t
> | -1 | 11510 | 7554523 | 1663
> | |
> control | 16389 | 6 | f | t
> | -1 | 11510 | 7554887 | 16384
> | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
> template1 | 10 | 0 | t | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
> conversion_alerts_fs | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
> conversion_feasibility_too | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> conversion_feasibility_tool | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> custom_searches_au | 16393 | 6 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
>
> I've found that very bizarre myself, that default_tablespace would be
> changed for already existing databases without me altering it explicitly...
> Sigh
Can we see an example of the script you used to modify the databases?
> ------------------------------------------------------------------------
> *From:* David G. Johnston <david.g.johnston@gmail.com>
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com
> <mailto:juliezain@hotmail.com>> wrote:
>
> 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.
>
>
> This seems unlikely to be the case - changing template1 should not be
> affecting other existing databases.
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Sure, thank you guys!
CREATE TABLESPACE vol4
OWNER postgres
LOCATION '/data/vol4';ALTER DATABASE tables_ericb
SET default_tablespace = 'vol4';
SET default_tablespace = 'vol4';
ALTER DATABASE conversion_feasibility_too
SET default_tablespace = 'vol4';
SET default_tablespace = 'vol4';
... and so on for 60 dbs...
but not for all 70+
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, May 15, 2019 2:11 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 2:11 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 1:57 PM, Julie Nishimura wrote:
> This puzzles me too! I found that bizarre myself. What is even more
> interesting, we have about 80 databases, and all of them now have
> default_tablespace=vol4, except only one - "control" database. The only
> explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name...
>
> postgres=# select * from pg_database limit 10;
> datname | datdba | encoding | datistemplate |
> datallowconn | datconnlimit | datlastsysoid | datfrozenxid |
> dattablespace | datconfig | datacl
> -----------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+---------------------------+-----------------------------------------------------
> template0 | 10 | 0 | t | f
> | -1 | 11510 | 378 | 1663
> | | {=c/postgres,postgres=CTc/postgres}
> postgres | 10 | 0 | f | t
> | -1 | 11510 | 7554523 | 1663
> | |
> control | 16389 | 6 | f | t
> | -1 | 11510 | 7554887 | 16384
> | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
> template1 | 10 | 0 | t | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
> conversion_alerts_fs | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
> conversion_feasibility_too | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> conversion_feasibility_tool | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> custom_searches_au | 16393 | 6 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
>
> I've found that very bizarre myself, that default_tablespace would be
> changed for already existing databases without me altering it explicitly...
> Sigh
Can we see an example of the script you used to modify the databases?
> ------------------------------------------------------------------------
> *From:* David G. Johnston <david.g.johnston@gmail.com>
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com
> <mailto:juliezain@hotmail.com>> wrote:
>
> 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.
>
>
> This seems unlikely to be the case - changing template1 should not be
> affecting other existing databases.
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
> This puzzles me too! I found that bizarre myself. What is even more
> interesting, we have about 80 databases, and all of them now have
> default_tablespace=vol4, except only one - "control" database. The only
> explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name...
>
> postgres=# select * from pg_database limit 10;
> datname | datdba | encoding | datistemplate |
> datallowconn | datconnlimit | datlastsysoid | datfrozenxid |
> dattablespace | datconfig | datacl
> -----------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+---------------------------+-----------------------------------------------------
> template0 | 10 | 0 | t | f
> | -1 | 11510 | 378 | 1663
> | | {=c/postgres,postgres=CTc/postgres}
> postgres | 10 | 0 | f | t
> | -1 | 11510 | 7554523 | 1663
> | |
> control | 16389 | 6 | f | t
> | -1 | 11510 | 7554887 | 16384
> | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}
> template1 | 10 | 0 | t | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}
> conversion_alerts_fs | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
> conversion_feasibility_too | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> conversion_feasibility_tool | 16393 | 0 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> custom_searches_au | 16393 | 6 | f | t
> | -1 | 11510 | 7554847 | 16384
> | {default_tablespace=vol4} |
> {=T/build,build=CTc/build,tableau_readonly=c/build}
>
> I've found that very bizarre myself, that default_tablespace would be
> changed for already existing databases without me altering it explicitly...
> Sigh
Can we see an example of the script you used to modify the databases?
> ------------------------------------------------------------------------
> *From:* David G. Johnston <david.g.johnston@gmail.com>
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com
> <mailto:juliezain@hotmail.com>> wrote:
>
> 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.
>
>
> This seems unlikely to be the case - changing template1 should not be
> affecting other existing databases.
>
> David J.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/15/19 2:15 PM, Julie Nishimura wrote: > Sure, thank you guys! > > CREATE TABLESPACE vol4 > OWNER postgres > LOCATION '/data/vol4'; > > ALTER DATABASE tables_ericb > SET default_tablespace = 'vol4'; > ALTER DATABASE conversion_feasibility_too > SET default_tablespace = 'vol4'; > > ... and so on for 60 dbs... > > but not for all 70+ But it was not for all 70+. The control db still is still set at vol1. Are there others not set to vol4? -- Adrian Klaver adrian.klaver@aklaver.com
Adrian, only first 3 (which are template0, postgres, control). Rest of them are set to vol4.
The only explanation I would have that all of those databases which have tablespace vol4 as default were copied from template1 without explicitly specified tablespace name, and "control" was created with the explicit tablespace in it?... I dont have any other good explanations...
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, May 15, 2019 2:18 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 2:18 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 2:15 PM, Julie Nishimura wrote:
> Sure, thank you guys!
>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE tables_ericb
> SET default_tablespace = 'vol4';
> ALTER DATABASE conversion_feasibility_too
> SET default_tablespace = 'vol4';
>
> ... and so on for 60 dbs...
>
> but not for all 70+
But it was not for all 70+. The control db still is still set at vol1.
Are there others not set to vol4?
--
Adrian Klaver
adrian.klaver@aklaver.com
> Sure, thank you guys!
>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE tables_ericb
> SET default_tablespace = 'vol4';
> ALTER DATABASE conversion_feasibility_too
> SET default_tablespace = 'vol4';
>
> ... and so on for 60 dbs...
>
> but not for all 70+
But it was not for all 70+. The control db still is still set at vol1.
Are there others not set to vol4?
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/15/19 2:45 PM, Julie Nishimura wrote: > Adrian, only first 3 (which are template0, postgres, control). Rest of > them are set to vol4. Do the script(s) reflect this? In other words did you have a script for each and every database and was the default_tablespace set different in some of them? > > The only explanation I would have that all of those databases which have > tablespace vol4 as default were copied from template1 without explicitly > specified tablespace name, and "control" was created with the explicit > tablespace in it?... I dont have any other good explanations... > > ------------------------------------------------------------------------ > *From:* Adrian Klaver <adrian.klaver@aklaver.com> > *Sent:* Wednesday, May 15, 2019 2:18 PM > *To:* Julie Nishimura; David G. Johnston > *Cc:* pgsql-general@lists.postgresql.org > *Subject:* Re: default_tablespace in 8.3 postgresql > On 5/15/19 2:15 PM, Julie Nishimura wrote: >> Sure, thank you guys! >> >> CREATE TABLESPACE vol4 >> OWNER postgres >> LOCATION '/data/vol4'; >> >> ALTER DATABASE tables_ericb >> SET default_tablespace = 'vol4'; >> ALTER DATABASE conversion_feasibility_too >> SET default_tablespace = 'vol4'; >> >> ... and so on for 60 dbs... >> >> but not for all 70+ > > But it was not for all 70+. The control db still is still set at vol1. > > Are there others not set to vol4? > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- Adrian Klaver adrian.klaver@aklaver.com
No, I did not have them at all in my script. I did not touch neither these three dbs, nor like some others that I did not intend to alter, but they are altered regardless. Or unless this is what pg_database reports back to me... What is even more interesting, if I use PGAdmin for one of the dbs that I was not intended to alter and leave it on vol3, this is what it shows to me now. Please note, it shows both - "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, May 15, 2019 2:48 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 2:48 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 2:45 PM, Julie Nishimura wrote:
> Adrian, only first 3 (which are template0, postgres, control). Rest of
> them are set to vol4.
Do the script(s) reflect this?
In other words did you have a script for each and every database and was
the default_tablespace set different in some of them?
>
> The only explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name, and "control" was created with the explicit
> tablespace in it?... I dont have any other good explanations...
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Wednesday, May 15, 2019 2:18 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On 5/15/19 2:15 PM, Julie Nishimura wrote:
>> Sure, thank you guys!
>>
>> CREATE TABLESPACE vol4
>> OWNER postgres
>> LOCATION '/data/vol4';
>>
>> ALTER DATABASE tables_ericb
>> SET default_tablespace = 'vol4';
>> ALTER DATABASE conversion_feasibility_too
>> SET default_tablespace = 'vol4';
>>
>> ... and so on for 60 dbs...
>>
>> but not for all 70+
>
> But it was not for all 70+. The control db still is still set at vol1.
>
> Are there others not set to vol4?
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
> Adrian, only first 3 (which are template0, postgres, control). Rest of
> them are set to vol4.
Do the script(s) reflect this?
In other words did you have a script for each and every database and was
the default_tablespace set different in some of them?
>
> The only explanation I would have that all of those databases which have
> tablespace vol4 as default were copied from template1 without explicitly
> specified tablespace name, and "control" was created with the explicit
> tablespace in it?... I dont have any other good explanations...
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Wednesday, May 15, 2019 2:18 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On 5/15/19 2:15 PM, Julie Nishimura wrote:
>> Sure, thank you guys!
>>
>> CREATE TABLESPACE vol4
>> OWNER postgres
>> LOCATION '/data/vol4';
>>
>> ALTER DATABASE tables_ericb
>> SET default_tablespace = 'vol4';
>> ALTER DATABASE conversion_feasibility_too
>> SET default_tablespace = 'vol4';
>>
>> ... and so on for 60 dbs...
>>
>> but not for all 70+
>
> But it was not for all 70+. The control db still is still set at vol1.
>
> Are there others not set to vol4?
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
Вложения
On 5/15/19 3:01 PM, Julie Nishimura wrote: > No, I did not have them at all in my script. I did not touch neither > these three dbs, nor like some others that I did not intend to alter, > but they are altered regardless. Or unless this is what pg_database > reports back to me... What is even more interesting, if I use PGAdmin > for one of the dbs that I was not intended to alter and leave it on > vol3, this is what it shows to me now. Please note, it shows both - > "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm... One is from the postgresql.conf setting(vol3). The other is from the database default_tablespace setting. > > > > > ------------------------------------------------------------------------ > *From:* Adrian Klaver <adrian.klaver@aklaver.com> > *Sent:* Wednesday, May 15, 2019 2:48 PM > *To:* Julie Nishimura; David G. Johnston > *Cc:* pgsql-general@lists.postgresql.org > *Subject:* Re: default_tablespace in 8.3 postgresql > On 5/15/19 2:45 PM, Julie Nishimura wrote: >> Adrian, only first 3 (which are template0, postgres, control). Rest of >> them are set to vol4. > > Do the script(s) reflect this? > > In other words did you have a script for each and every database and was > the default_tablespace set different in some of them? > >> >> The only explanation I would have that all of those databases which have >> tablespace vol4 as default were copied from template1 without explicitly >> specified tablespace name, and "control" was created with the explicit >> tablespace in it?... I dont have any other good explanations... >> >> ------------------------------------------------------------------------ >> *From:* Adrian Klaver <adrian.klaver@aklaver.com> >> *Sent:* Wednesday, May 15, 2019 2:18 PM >> *To:* Julie Nishimura; David G. Johnston >> *Cc:* pgsql-general@lists.postgresql.org >> *Subject:* Re: default_tablespace in 8.3 postgresql >> On 5/15/19 2:15 PM, Julie Nishimura wrote: >>> Sure, thank you guys! >>> >>> CREATE TABLESPACE vol4 >>> OWNER postgres >>> LOCATION '/data/vol4'; >>> >>> ALTER DATABASE tables_ericb >>> SET default_tablespace = 'vol4'; >>> ALTER DATABASE conversion_feasibility_too >>> SET default_tablespace = 'vol4'; >>> >>> ... and so on for 60 dbs... >>> >>> but not for all 70+ >> >> But it was not for all 70+. The control db still is still set at vol1. >> >> Are there others not set to vol4? >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- Adrian Klaver adrian.klaver@aklaver.com
that makes sense, thanks!
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, May 15, 2019 3:15 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 3:15 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 3:01 PM, Julie Nishimura wrote:
> No, I did not have them at all in my script. I did not touch neither
> these three dbs, nor like some others that I did not intend to alter,
> but they are altered regardless. Or unless this is what pg_database
> reports back to me... What is even more interesting, if I use PGAdmin
> for one of the dbs that I was not intended to alter and leave it on
> vol3, this is what it shows to me now. Please note, it shows both -
> "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...
One is from the postgresql.conf setting(vol3).
The other is from the database default_tablespace setting.
>
>
>
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Wednesday, May 15, 2019 2:48 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On 5/15/19 2:45 PM, Julie Nishimura wrote:
>> Adrian, only first 3 (which are template0, postgres, control). Rest of
>> them are set to vol4.
>
> Do the script(s) reflect this?
>
> In other words did you have a script for each and every database and was
> the default_tablespace set different in some of them?
>
>>
>> The only explanation I would have that all of those databases which have
>> tablespace vol4 as default were copied from template1 without explicitly
>> specified tablespace name, and "control" was created with the explicit
>> tablespace in it?... I dont have any other good explanations...
>>
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
>> *Sent:* Wednesday, May 15, 2019 2:18 PM
>> *To:* Julie Nishimura; David G. Johnston
>> *Cc:* pgsql-general@lists.postgresql.org
>> *Subject:* Re: default_tablespace in 8.3 postgresql
>> On 5/15/19 2:15 PM, Julie Nishimura wrote:
>>> Sure, thank you guys!
>>>
>>> CREATE TABLESPACE vol4
>>> OWNER postgres
>>> LOCATION '/data/vol4';
>>>
>>> ALTER DATABASE tables_ericb
>>> SET default_tablespace = 'vol4';
>>> ALTER DATABASE conversion_feasibility_too
>>> SET default_tablespace = 'vol4';
>>>
>>> ... and so on for 60 dbs...
>>>
>>> but not for all 70+
>>
>> But it was not for all 70+. The control db still is still set at vol1.
>>
>> Are there others not set to vol4?
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
> No, I did not have them at all in my script. I did not touch neither
> these three dbs, nor like some others that I did not intend to alter,
> but they are altered regardless. Or unless this is what pg_database
> reports back to me... What is even more interesting, if I use PGAdmin
> for one of the dbs that I was not intended to alter and leave it on
> vol3, this is what it shows to me now. Please note, it shows both -
> "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...
One is from the postgresql.conf setting(vol3).
The other is from the database default_tablespace setting.
>
>
>
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Sent:* Wednesday, May 15, 2019 2:48 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On 5/15/19 2:45 PM, Julie Nishimura wrote:
>> Adrian, only first 3 (which are template0, postgres, control). Rest of
>> them are set to vol4.
>
> Do the script(s) reflect this?
>
> In other words did you have a script for each and every database and was
> the default_tablespace set different in some of them?
>
>>
>> The only explanation I would have that all of those databases which have
>> tablespace vol4 as default were copied from template1 without explicitly
>> specified tablespace name, and "control" was created with the explicit
>> tablespace in it?... I dont have any other good explanations...
>>
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian.klaver@aklaver.com>
>> *Sent:* Wednesday, May 15, 2019 2:18 PM
>> *To:* Julie Nishimura; David G. Johnston
>> *Cc:* pgsql-general@lists.postgresql.org
>> *Subject:* Re: default_tablespace in 8.3 postgresql
>> On 5/15/19 2:15 PM, Julie Nishimura wrote:
>>> Sure, thank you guys!
>>>
>>> CREATE TABLESPACE vol4
>>> OWNER postgres
>>> LOCATION '/data/vol4';
>>>
>>> ALTER DATABASE tables_ericb
>>> SET default_tablespace = 'vol4';
>>> ALTER DATABASE conversion_feasibility_too
>>> SET default_tablespace = 'vol4';
>>>
>>> ... and so on for 60 dbs...
>>>
>>> but not for all 70+
>>
>> But it was not for all 70+. The control db still is still set at vol1.
>>
>> Are there others not set to vol4?
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/15/19 3:01 PM, Julie Nishimura wrote: > No, I did not have them at all in my script. I did not touch neither > these three dbs, nor like some others that I did not intend to alter, > but they are altered regardless. Or unless this is what pg_database > reports back to me... What is even more interesting, if I use PGAdmin > for one of the dbs that I was not intended to alter and leave it on > vol3, this is what it shows to me now. Please note, it shows both - > "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm... > You didn't happen to do a: ALTER ROLE some_role SET default_tablespace = 'vol4'; Do: select rolname, rolconfig from pg_roles; -- Adrian Klaver adrian.klaver@aklaver.com
No, Rob, old existing tables are still on other volumes (the are almost 20 tb), only new tables are now created on new volume
From: Rob Sargent <robjsargent@gmail.com>
Sent: Wednesday, May 15, 2019 2:02 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 2:02 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 2:57 PM, Julie Nishimura wrote:
This puzzles me too! I found that bizarre myself. What is even more interesting, we have about 80 databases, and all of them now have default_tablespace=vol4, except only one - "control" database. The only explanation I would have that all of those databases which have tablespace vol4 as default were copied from template1 without explicitly specified tablespace name...postgres=# select * from pg_database limit 10;datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig | datacl-----------------------------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+---------------------------+-----------------------------------------------------template0 | 10 | 0 | t | f | -1 | 11510 | 378 | 1663 | | {=c/postgres,postgres=CTc/postgres}postgres | 10 | 0 | f | t | -1 | 11510 | 7554523 | 1663 | |control | 16389 | 6 | f | t | -1 | 11510 | 7554887 | 16384 | {default_tablespace=vol1} | {=T/dba,dba=CTc/dba,oper=c/dba,nagios=c/dba}template1 | 10 | 0 | t | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=c/postgres,postgres=CTc/postgres}conversion_alerts_fs | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}conversion_feasibility_too | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} |conversion_feasibility_tool | 16393 | 0 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} |custom_searches_au | 16393 | 6 | f | t | -1 | 11510 | 7554847 | 16384 | {default_tablespace=vol4} | {=T/build,build=CTc/build,tableau_readonly=c/build}I've found that very bizarre myself, that default_tablespace would be changed for already existing databases without me altering it explicitly...SighFrom: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresqlOn Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@hotmail.com> wrote: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.This seems unlikely to be the case - changing template1 should not be affecting other existing databases.David J.
Previous actions may have altered (accidentally) the default table space for those databases but this doesn't show that the tables of old are now on vol4 does it?. Would have taken some time to move 80 non-trivial databases.
Nope, I did not...
pg_roles only shows "rolconfig" as {default_transaction_read_only=true} for some users...
The only other commands I ran between creation of tablespace and alter dbs are:
grant all on tablespace vol4 to public;
grant create on tablespace vol4 to public;
grant create on tablespace vol4 to public;
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, May 15, 2019 3:22 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 3:22 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 3:01 PM, Julie Nishimura wrote:
> No, I did not have them at all in my script. I did not touch neither
> these three dbs, nor like some others that I did not intend to alter,
> but they are altered regardless. Or unless this is what pg_database
> reports back to me... What is even more interesting, if I use PGAdmin
> for one of the dbs that I was not intended to alter and leave it on
> vol3, this is what it shows to me now. Please note, it shows both -
> "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...
>
You didn't happen to do a:
ALTER ROLE some_role SET default_tablespace = 'vol4';
Do:
select rolname, rolconfig from pg_roles;
--
Adrian Klaver
adrian.klaver@aklaver.com
> No, I did not have them at all in my script. I did not touch neither
> these three dbs, nor like some others that I did not intend to alter,
> but they are altered regardless. Or unless this is what pg_database
> reports back to me... What is even more interesting, if I use PGAdmin
> for one of the dbs that I was not intended to alter and leave it on
> vol3, this is what it shows to me now. Please note, it shows both -
> "Default tablespace" (as vol3), and "default_tablespace" (as vol4)... Hmm...
>
You didn't happen to do a:
ALTER ROLE some_role SET default_tablespace = 'vol4';
Do:
select rolname, rolconfig from pg_roles;
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/15/19 3:28 PM, Julie Nishimura wrote:
> Nope, I did not...
>
> pg_roles only shows "rolconfig" as {default_transaction_read_only=true}
> for some users...
>
> The only other commands I ran between creation of tablespace and alter
> dbs are:
>
> grant all on tablespace vol4 to public;
> grant create on tablespace vol4 to public;
>
The only thing I have left, assuming it will not interfere is:
BEGIN;
ALTER DATABASE template1
SET default_tablespace = default;
Check pg_database
ROLLBACK;
That may help confirm whether template1 is the culprit. More for
completeness sake as 8.3 is 6 years past EOL, so there is not much that
can be done about it.
--
Adrian Klaver
adrian.klaver@aklaver.com
Guys, thank you everybody who tried to solve this mystery! It is solved now. I went through the logs and found those "alter database" commands, based on host and time, I've just found shell script which was executing from crontab and setting all databases (except system ones and control) default_tablespace to the emptiest volume...
Thanks everybody...
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, May 15, 2019 3:35 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
Sent: Wednesday, May 15, 2019 3:35 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general@lists.postgresql.org
Subject: Re: default_tablespace in 8.3 postgresql
On 5/15/19 3:28 PM, Julie Nishimura wrote:
> Nope, I did not...
>
> pg_roles only shows "rolconfig" as {default_transaction_read_only=true}
> for some users...
>
> The only other commands I ran between creation of tablespace and alter
> dbs are:
>
> grant all on tablespace vol4 to public;
> grant create on tablespace vol4 to public;
>
The only thing I have left, assuming it will not interfere is:
BEGIN;
ALTER DATABASE template1
SET default_tablespace = default;
Check pg_database
ROLLBACK;
That may help confirm whether template1 is the culprit. More for
completeness sake as 8.3 is 6 years past EOL, so there is not much that
can be done about it.
--
Adrian Klaver
adrian.klaver@aklaver.com
> Nope, I did not...
>
> pg_roles only shows "rolconfig" as {default_transaction_read_only=true}
> for some users...
>
> The only other commands I ran between creation of tablespace and alter
> dbs are:
>
> grant all on tablespace vol4 to public;
> grant create on tablespace vol4 to public;
>
The only thing I have left, assuming it will not interfere is:
BEGIN;
ALTER DATABASE template1
SET default_tablespace = default;
Check pg_database
ROLLBACK;
That may help confirm whether template1 is the culprit. More for
completeness sake as 8.3 is 6 years past EOL, so there is not much that
can be done about it.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 5/15/19 5:29 PM, Julie Nishimura wrote: > Guys, thank you everybody who tried to solve this mystery! It is solved > now. I went through the logs and found those "alter database" commands, > based on host and time, I've just found shell script which was executing > from crontab and setting all databases (except system ones and control) > default_tablespace to the emptiest volume... Aah, that explains it. Good to find the cause. > > Thanks everybody... > -- Adrian Klaver adrian.klaver@aklaver.com