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@xxxxxxxxx>
*Sent:* Wednesday, May 15, 2019 1:23 PM
*To:* Julie Nishimura
*Cc:* Adrian Klaver; pgsql-general@xxxxxxxxxxxxxxxxxxxx
*Subject:* Re: default_tablespace in 8.3 postgresql
On Wed, May 15, 2019 at 1:01 PM Julie Nishimura <juliezain@xxxxxxxxxxx
<mailto:juliezain@xxxxxxxxxxx>> 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@xxxxxxxxxxx
