Hello all,
If I were in your situation, I would analyze if it could move only a part of the 36 databases to the new disk.
* Either, I will move some of the databases to the new disk,
* Either, In the largest databases, I will consider to work in multiple tablespace configuration, using the command ALTER TABLE <<TableName>> SET TABLESPACE <<TablespaceName>>; Link to the documentation: https://www.postgresql.org/docs/9.6/sql-altertable.html . to move some tables to the new disk. You can analyze (depending on your disk and DB configurations, if it's better to move the very large tables or intensively used tables.
I hope I'm clear enough!
Feel free to ask for clarification or add new elements to go further on!
Hope this helps,
Have a nice day,
Thomas
Le mer. 20 févr. 2019 à 21:37, Ron <ronljohnsonjr@xxxxxxxxx> a écrit :
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/mainpostgres=# 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 TABLESPACEtablespace01 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.
