default_tablespace in 8.3 postgresql

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

default_tablespace in 8.3 postgresql

Julie Nishimura
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

Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
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 <[hidden email]>
Sent: Wednesday, May 15, 2019 12:47 PM
To: Julie Nishimura; [hidden email]; 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

David G Johnston
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
In reply to this post by Julie Nishimura
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 12:47 PM
> *To:* Julie Nishimura; [hidden email]; 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
> [hidden email]


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
In reply to this post by David G Johnston
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 <[hidden email]>
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; [hidden email]
Subject: Re: default_tablespace in 8.3 postgresql
 
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <[hidden email]> 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.

lup
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

lup


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...
Sigh

From: David G. Johnston [hidden email]
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; [hidden email]
Subject: Re: default_tablespace in 8.3 postgresql
 
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
In reply to this post by Julie Nishimura
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; [hidden email]
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <[hidden email]
> <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
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+


From: Adrian Klaver <[hidden email]>
Sent: Wednesday, May 15, 2019 2:11 PM
To: Julie Nishimura; David G. Johnston
Cc: [hidden email]
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; [hidden email]
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <[hidden email]
> <[hidden email]>> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
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 <[hidden email]>
Sent: Wednesday, May 15, 2019 2:18 PM
To: Julie Nishimura; David G. Johnston
Cc: [hidden email]
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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 2:18 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* [hidden email]
> *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
> [hidden email]


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
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 <[hidden email]>
Sent: Wednesday, May 15, 2019 2:48 PM
To: Julie Nishimura; David G. Johnston
Cc: [hidden email]
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 2:18 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* [hidden email]
> *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
> [hidden email]


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 2:48 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* [hidden email]
> *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 <[hidden email]>
>> *Sent:* Wednesday, May 15, 2019 2:18 PM
>> *To:* Julie Nishimura; David G. Johnston
>> *Cc:* [hidden email]
>> *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
>> [hidden email]
>
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
that makes sense, thanks!


From: Adrian Klaver <[hidden email]>
Sent: Wednesday, May 15, 2019 3:15 PM
To: Julie Nishimura; David G. Johnston
Cc: [hidden email]
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 <[hidden email]>
> *Sent:* Wednesday, May 15, 2019 2:48 PM
> *To:* Julie Nishimura; David G. Johnston
> *Cc:* [hidden email]
> *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 <[hidden email]>
>> *Sent:* Wednesday, May 15, 2019 2:18 PM
>> *To:* Julie Nishimura; David G. Johnston
>> *Cc:* [hidden email]
>> *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
>> [hidden email]
>
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
In reply to this post by Julie Nishimura
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
In reply to this post by lup
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 <[hidden email]>
Sent: Wednesday, May 15, 2019 2:02 PM
To: [hidden email]
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...
Sigh

From: David G. Johnston [hidden email]
Sent: Wednesday, May 15, 2019 1:23 PM
To: Julie Nishimura
Cc: Adrian Klaver; [hidden email]
Subject: Re: default_tablespace in 8.3 postgresql
 
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
In reply to this post by Adrian Klaver-4
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;


From: Adrian Klaver <[hidden email]>
Sent: Wednesday, May 15, 2019 3:22 PM
To: Julie Nishimura; David G. Johnston
Cc: [hidden email]
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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: default_tablespace in 8.3 postgresql

Julie Nishimura
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 <[hidden email]>
Sent: Wednesday, May 15, 2019 3:35 PM
To: Julie Nishimura; David G. Johnston
Cc: [hidden email]
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
[hidden email]
12