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.
