Re: default_tablespace in 8.3 postgresql - Mailing list pgsql-general

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