Re: default_tablespace in 8.3 postgresql

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(dot)klaver(at)aklaver(dot)com>
Sent: Wednesday, May 15, 2019 2:11 PM
To: Julie Nishimura; David G. Johnston
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
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(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.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com