Postgres Pro
Downloads
Home   >   mailing lists

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

From Julie Nishimura
Subject Re: default_tablespace in 8.3 postgresql
Date
Msg-id BYAPR08MB50145E1F8A325628D62870F5AC090@BYAPR08MB5014.namprd08.prod.outlook.com
Whole thread Raw
In response to Re: default_tablespace in 8.3 postgresql  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: default_tablespace in 8.3 postgresql
Re: default_tablespace in 8.3 postgresql
List pgsql-general
Tree view
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.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> 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.

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: default_tablespace in 8.3 postgresql
Next
From: Rob Sargent
Date:
Subject: Re: default_tablespace in 8.3 postgresql