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 <david(dot)g(dot)johnston(at)gmail(dot)com>
> *Sent:* Wednesday, May 15, 2019 1:23 PM
> *To:* Julie Nishimura
> *Cc:* Adrian Klaver; pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* Re: default_tablespace in 8.3 postgresql
> On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain(at)hotmail(dot)com
> <mailto:juliezain(at)hotmail(dot)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.