|
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 <adrian.klaver@xxxxxxxxxxx>
Sent: Wednesday, May 15, 2019 2:11 PM To: Julie Nishimura; David G. Johnston Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx 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@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 |
