On 2/19/19 5:02 PM, Julie Nishimura
wrote:
Hello,
we are almost out of space on our main data
directory, and about to introduce new directory to
our cluster. We cannot use multiple physical disks
as a single volume, so we are thinking about
creation new tablespace.
Our
current data_directory shows as follows:
/data/postgresql/9.6/main
postgres=#
SELECT spcname FROM pg_tablespace;
spcname
------------
pg_default
pg_global
(2 rows)
We
also have 36 existing databases on this cluster.
If
we add new directory, will it be enough to execute
the following commands in order to force new data
there:
CREATE TABLESPACE tablespace01 LOCATION '/data/postgresql/9.6/main01';
ALTER DATABASE db_name SET TABLESPACE tablespace01
Do I need to repeat it for all our existing databases?
Since the command is ALTER DATABASE
<your_user_db>, it seems that yes you have to do
it for all of them. A simple bash script should knock
that out quickly.
Should I change our "template*" dbs as well?
If you want
new databases to automatically go to
tablespace01 then alter template1.
Do I need to do something else?
Maybe, depending on the size of your databases, and how
much down time you can afford,
https://www.postgresql.org/docs/9.6/sql-alterdatabase.html
"This command physically moves any tables or indexes in
the database's old default tablespace to the new
tablespace."
For example, our multi-TB databases are so big that
moving it all at once is unreasonably slow. And a
failure might leave the db is a bad spot. Thus, I'd
move one table at a time, a few per outage.
Naturally, YMMV.
Thank you for your advises.
--
Angular momentum makes the world go 'round.
